PON Event Troubleshooting Guide

Overview

This guide covers how to investigate Planned Outage Notification (PON) issues using MongoDB Compass, BigQuery, and the reach-a database.

Reference Case: Ticket: PON Notifications - The Line 2611… – Convey Desk – Zendesk

---

Step 1 — Find the event & get request process key (MongoDB Compass)

Connect to MongoDB Compass using the prod connection string and navigate to eventmanagementprod2 → events.

mongodb://support-team-mongodb-prod-reader:[e-mail]:27017,c3t-reach-prod-shard-00-01.szetb.mongodb.net:27017,c3t-reach-prod-shard-00-02.szetb.mongodb.net:27017/eventmanagementprod2?ssl=true&replicaSet=atlas-95rhd0-shard-0&authSource=admin&retryWrites=true&w=majority

Note: Ask password from your manager and replace with XXXXXXXXXXXX

Search by work order number using this filter:

{ "workOrderNumber": { "$regex": "19209688" } }

Or exact match:

{ "workOrderNumber": "19209688 LINE-2611 CUT 1" }

Copy the transformerRequestKey value from the result (e.g. 1774896873577ZflLe).

---

Step 2 — Look up transformer numbers (BigQuery)

Go to: https://console.cloud.google.com/bigquery?hl=en&project=c3t-tango-prd

SELECT *
FROM `c3t-tango-prd.reach_a.R_TEMP_TRANSFORMER_ADHOC_REQ`
WHERE CREATE_DATE BETWEEN "2026-03-30 00:37:30 UTC" AND "2026-03-30 23:37:30 UTC"
  AND REQUEST_PROCESS_KEY = '1774896873577ZflLe'
ORDER BY CREATE_DATE DESC
LIMIT 1000;

Adjust the CREATE_DATE window to match the event's sendMessageDateTime from MongoDB. Replace the REQUEST_PROCESS_KEY with the value from Step 1.

Output: TRANSFORMER_NUMBER values

---

Step 3 — Validate transformer numbers & get IDs (BigQuery)

SELECT *
FROM R_TRANSFORMER_MT
WHERE TRANSFORMER_NUMBER IN ('142D2A3D', '142C2H3E', '142C2H4G');

Replace transformer numbers with those found in Step 2. Any number not returned does not exist in the system. Copy the ID values from the results for Step 4.

Output: Transformer ID values (e.g. 7831, 27721). Note any transformer numbers not found.

---

Step 4 — Count notification preferences by channel (BigQuery / reach-a DB)

WITH VALID_ACCOUNTS AS (
    SELECT tsp.ACCOUNT_ID
    FROM R_TRANSFORMER_SERVICE_POINT_ML tsp
    JOIN R_SERVICE_POINT_MT sp
        ON tsp.SERVICE_POINT_ID = sp.ID
    WHERE tsp.TRANSFORMER_ID IN (7831, 27721)
    AND sp.IS_ACTIVE = 1
    GROUP BY tsp.ACCOUNT_ID
    HAVING COUNT(DISTINCT tsp.SERVICE_POINT_ID) = 1
)
SELECT 'EMAIL' AS channel, COUNT(*) AS count
FROM R_PREF_EMAIL_ML
WHERE PROGRAM_ID = 54
AND ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM VALID_ACCOUNTS)
UNION ALL
SELECT 'SMS', COUNT(*) FROM R_PREF_SMS_ML
WHERE PROGRAM_ID = 54 AND ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM VALID_ACCOUNTS)
UNION ALL
SELECT 'PUSH', COUNT(*) FROM R_PREF_PUSH_ML
WHERE PROGRAM_ID = 54 AND ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM VALID_ACCOUNTS)
UNION ALL
SELECT 'VOICE', COUNT(*) FROM R_PREF_VOICE_ML
WHERE PROGRAM_ID = 54 AND ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM VALID_ACCOUNTS);

Replace transformer IDs (7831, 27721) with IDs from Step 3. PROGRAM_ID = 54 is the PON program.

Output: Notification count per channel — confirms expected notification volume.

---

Key Fields Reference

Field	Location	Description
workOrderNumber	MongoDB events	Full string e.g. "19209688 LINE-2611 CUT 1"
transformerRequestKey	MongoDB events	Links event to BQ transformer table
REQUEST_PROCESS_KEY	R_TEMP_TRANSFORMER_ADHOC_REQ	Matches transformerRequestKey
TRANSFORMER_NUMBER	R_TEMP_TRANSFORMER_ADHOC_REQ	e.g. 142C2H3F
ID	R_TRANSFORMER_MT	Numeric ID used in service point joins
PROGRAM_ID = 54	R_PREF_*_ML tables	PON program identifier