Skip to main content

PG&E PSPS Event: Master SQL Query Repository for Troubleshooting & Monitoring

Srikanth Chinnammagari
Organization
Convey Logo

PG&E PSPS Event: Master SQL Query Repository for Troubleshooting & Monitoring

This article provides a standardized workflow for Tier 2 Support to investigate and verify data related to PG&E PSPS events, including tracking notification statuses and executing SQL queries.

Overview

When a PG&E Public Safety Power Shutoff (PSPS) event is initiated, multi-channel notifications (Voice, SMS, Email) are triggered. Verification of this notification delivery is critical. This guide covers how to identify dataset IDs, track communication logs across queues and result tables, and run reporting queries to ensure systems are processing events as expected.

Prerequisites

Before you begin troubleshooting, ensure you have the following:

  • Access to the MySQL database (specifically pge3, pge3_datasets, pge3_results, and rxbatchresults schemas).
  • Access to the PG&E UI portal (Link) for dataset and UI event verification.
  • Access to the Dialer Dashboard (Link) for real-time voice call tracking.

Workflow & Core SQL Queries

1. System-Level Checks & Event Identification

Before deep-diving into notification logs, verify the system-level settings and locate the correct dataset ID. 

-- Review Recent Opt-Out Events
SELECT * FROM pge3.optout_events ORDER BY id_bint DESC;

-- Verify Automation Flag
SELECT * FROM pge3.keypair WHERE key_vch LIKE '%auto%';

-- Locate the dataset ID (Can also be found via UI)
SELECT * FROM pge3.datasets ORDER BY created_dt DESC;

-- Find dataset details for a specific event (Replace 21770 with current ID)
SELECT * FROM pge3.datasets WHERE dataset_id_bint = 21770;

2. Multi-Channel Communication Tracking

Investigate communication flows through various queue and result tables to verify notifications were successfully pushed. 

-- Check Voice Queue
SELECT * FROM pge3.voice_queue WHERE dataset_id_bint = '21770';

-- Check Active Dialers List
SELECT * FROM pge3.dialers WHERE isActive_int = 1;

-- Check Email Queue
SELECT * FROM pge3.email_queue WHERE dataset_id_bint = '21770';

-- Check SMS Queue
SELECT * FROM pge3.sms_queue WHERE dataset_id_bint = '21770';

3. Reporting & Summary Queries

Verify high-level success rates and final event completion statuses. 

-- Count notifications by delivery method (Replace d_19670)
SELECT count(*), notifyBy_vch FROM pge3_datasets.d_19670 GROUP BY notifyBy_vch;

-- View completion reports for a dataset
SELECT * FROM pge3.completion_reports WHERE datasets_id_bint = 21770 ORDER BY id_bint DESC;

Guidelines & Variable Replacement

When utilizing the comprehensive query bank below, ensure you are replacing the template variables correctly to avoid syntax errors.

Template Query Variables

  • ✓Replace <DATASET_ID> with your target dataset ID (e.g., 22520).
  • ✓Replace <CHANNEL> with your target channel (e.g., 'PHON', 'SMS', 'EML').
  • ✓Replace <DATE> with your target date string (e.g., '%2026-05-15%').
  • ✓Replace <BATCH_ID> with your specific rxbatchresults table ID.
  • ✓Replace <QUEUE_TYPE> with 'voice', 'sms', or 'email'.
  • ✓Replace <RESULT_PREFIX> with 'r' (Voice) or 's' (SMS).

All PSPS Troubleshooting Queries

The following sections contain the complete master list of queries used for deep-dive investigation. Copy and modify the variables as needed.

1. Dataset Configuration & Record Checks

-- Retrieves all metadata and configuration details for a specific dataset.
SELECT * FROM pge3.datasets WHERE dataset_id_bint = <DATASET_ID>;

-- Retrieves the 10 most recently created datasets overall.
SELECT * FROM pge3.datasets ORDER BY dataset_id_bint DESC LIMIT 10;

-- Retrieves the 10 most recent datasets flagged for next-day medical baseline retries.
SELECT * FROM pge3.datasets WHERE nextDayRetry_medicalBaseline_bl = 1 ORDER BY dataset_id_bint DESC LIMIT 10;

-- Retrieves all individual dataset records/contacts.
SELECT * FROM pge3_datasets.d_<DATASET_ID>;

-- Counts records grouped by their current processing status.
SELECT COUNT(*), status_vch FROM pge3_datasets.d_<DATASET_ID> GROUP BY status_vch;

-- Counts records grouped by processing status and notification channel.
SELECT COUNT(*), status_vch, notifyBy_vch FROM pge3_datasets.d_<DATASET_ID> GROUP BY status_vch, notifyBy_vch;

-- Counts records sent to queue missing valid contact info (value is 0), grouped by channel.
SELECT COUNT(*), notifyBy_vch FROM pge3_datasets.d_<DATASET_ID> WHERE status_vch = 'sent to queue' AND notifyByValue_vch = 0 GROUP BY notifyBy_vch;

-- Retrieves active/processed records with valid contact values.
SELECT * FROM pge3_datasets.d_<DATASET_ID> WHERE notifyByValue_vch <> '0' AND status_int = 1;

-- Retrieves active/processed records with invalid or empty contact info.
SELECT * FROM pge3_datasets.d_<DATASET_ID> WHERE notifyByValue_vch = '0' AND status_int = 1;

-- Counts the total number of records destined for a specific channel (e.g., 'PHON').
SELECT COUNT(*) FROM pge3_datasets.d_<DATASET_ID> WHERE notifyBy_vch = '<CHANNEL>';

-- Counts records for a specific channel, grouped by integer status code.
SELECT COUNT(*), status_int FROM pge3_datasets.d_<DATASET_ID> WHERE notifyBy_vch = '<CHANNEL>' GROUP BY status_int;

2. Queue Checks (Universal)

-- Retrieves all queued records associated with the dataset for a specific channel.
SELECT * FROM pge3.<QUEUE_TYPE>_queue WHERE dataset_id_bint = <DATASET_ID>;

-- Counts successfully processed/completed queue records for a specific channel.
SELECT COUNT(*) FROM pge3.<QUEUE_TYPE>_queue WHERE dataset_id_bint = <DATASET_ID> AND status_int = 1;

-- Counts pending or failed (non-completed) queue records for a specific channel.
SELECT COUNT(*) FROM pge3.<QUEUE_TYPE>_queue WHERE dataset_id_bint = <DATASET_ID> AND status_int <> 1;

3. Results & Outcomes

-- [VOICE & SMS RESULTS]
SELECT * FROM pge3_results.<RESULT_PREFIX>_<DATASET_ID>;

SELECT COUNT(*) FROM pge3_results.<RESULT_PREFIX>_<DATASET_ID>;

-- Finds SMS results that do not have a matching primary key in the Voice results table.
SELECT * FROM pge3_results.s_<DATASET_ID> WHERE pkid_int NOT IN (SELECT pkid_bint FROM pge3_results.r_<DATASET_ID>);

-- Retrieves Voice outcome percentage statistics.
SELECT * FROM pge3.voiceresultpercentages WHERE datasets_id_bint = <DATASET_ID>;

-- [EMAIL RESULTS]
SELECT * FROM pge3.email_events WHERE dataset_id_bint = <DATASET_ID> AND sg_event_vch = 'processed';

SELECT COUNT(*) FROM pge3.email_events WHERE dataset_id_bint = <DATASET_ID> AND sg_event_vch = 'processed';

-- [GENERAL OUTCOMES]
SELECT COUNT(1), notifyby_vch, result_vch FROM pge3_datasets.d_<DATASET_ID> GROUP BY notifyby_vch, result_vch;

-- Finds pending records that have a channel assigned but no final result yet.
SELECT * FROM pge3_datasets.d_<DATASET_ID> WHERE notifyby_vch IS NOT NULL AND result_vch IS NULL;

-- Fetches the 200 most recent raw RxCallDetails for a specific date and dataset within a specific batch.
SELECT * FROM rxbatchresults.rxcalldetails_<BATCH_ID> WHERE Created_dt LIKE '<DATE>' AND DTS_UUID LIKE '%<DATASET_ID>%' ORDER BY Created_dt DESC LIMIT 200;

4. Retries

-- Retrieves pending retry records that have a mapped result but haven't been processed yet.
SELECT * FROM pge3_retries.retries_<DATASET_ID> WHERE result_id_bint IS NOT NULL AND sentOut_dt IS NULL;

-- Counts pending retry records, grouped by notification channel and outcome.
SELECT COUNT(*), notifyBy_vch, result_vch FROM pge3_retries.retries_<DATASET_ID> WHERE result_id_bint IS NOT NULL AND sentOut_dt IS NULL GROUP BY notifyBy_vch, result_vch;

5. Reports, Transfers & Infrastructure

-- Reports & Transfers
SELECT * FROM pge3.completion_reports WHERE datasets_id_bint = <DATASET_ID> ORDER BY id_bint DESC;

SELECT * FROM pge3.completion_reports ORDER BY id_bint DESC LIMIT 100;

SELECT * FROM pge3.completion_transfers ORDER BY id_bint DESC LIMIT 100;

SELECT * FROM pge3.inflight_transfers ORDER BY id_bint DESC LIMIT 100;

SELECT * FROM pge3.inflight_transfers WHERE message LIKE '<DATE>' ORDER BY id_bint;

-- Infrastructure
SELECT * FROM pge3.keypair WHERE key_vch LIKE '%<SEARCH_TERM>%';

SELECT COUNT(*) FROM pge3.dialers WHERE isActive_int = 1;

SELECT * FROM pge3.dialers;

-- Detailed completion report transfers for list of datasets
SELECT cr.datasets_id_bint AS dataset_id, cr.transfers_id_bint, ct.success_dt, ct.failure_dt, ct.message, ct.host, ct.folder, ct.reports_id_bint
FROM pge3.completion_reports cr
INNER JOIN pge3.completion_transfers ct ON cr.transfers_id_bint = ct.id_bint
WHERE cr.datasets_id_bint IN ('<DATASET_ID_1>', '<DATASET_ID_2>', '<DATASET_ID_3>');

-- Execution timing for inflight reports
SELECT r.id_bint AS dataset_id, r.filename_vch, r.records_bint, r.report_dt, r.update_dt, t.success_dt, t.seconds_int AS transfer_duration_seconds, t.host_vch, t.folder_vch, t.message AS transfer_message
FROM pge3.inflight_reports r
LEFT JOIN pge3.inflight_transfers t ON r.transfers_id_bint = t.id_bint
WHERE r.id_bint IN ('<DATASET_ID_1>', '<DATASET_ID_2>', '<DATASET_ID_3>')
ORDER BY r.report_dt DESC;

6. NOC Dashboard Query (Queue & Result Counts)

USE pge3;

-- 1. FEED YOUR VARIABLES HERE
SET @dataset_id = 22562;  -- Replace with your dataset_id_bint
SET @status_ti = 1;        -- Replace with 1 (true) or 0 (false) for the <cfif> block

-- 2. BUILD THE DYNAMIC QUERY
SET @sql_query = CONCAT(
    'SELECT ''DSQ'' AS status, ''PHON'' AS channel, COUNT(1) AS ct FROM pge3_datasets.d_', @dataset_id, ' WHERE status_vch = ''sent to queue'' AND notifyby_vch = ''phon'' ',
    'UNION ALL ',
    'SELECT ''DSQ'' AS status, ''SMS'' AS channel, COUNT(1) AS ct FROM pge3_datasets.d_', @dataset_id, ' WHERE status_vch = ''sent to queue'' AND notifyby_vch = ''sms'' ',
    'UNION ALL ',
    'SELECT ''DSQ'' AS status, ''EMAIL'' AS channel, COUNT(1) AS ct FROM pge3_datasets.d_', @dataset_id, ' WHERE status_vch = ''sent to queue'' AND notifyby_vch = ''eml'' ',
    'UNION ALL ',
    'SELECT ''DBQ'' AS status, ''PHON'' AS channel, COUNT(1) AS ct FROM voice_queue q WHERE q.dataset_id_bint = ', @dataset_id, ' ',
    'UNION ALL ',
    'SELECT ''DBQ'' AS status, ''SMS'' AS channel, COUNT(1) AS ct FROM sms_queue q WHERE q.dataset_id_bint = ', @dataset_id, ' ',
    'UNION ALL ',
    'SELECT ''DBQ'' AS status, ''EMAIL'' AS channel, COUNT(1) AS ct FROM email_queue q WHERE q.dataset_id_bint = ', @dataset_id, ' ',
    'UNION ALL ',
    'SELECT ''DBQP'' AS status, ''PHON'' AS channel, COUNT(1) AS ct FROM voice_queue q WHERE q.dataset_id_bint = ', @dataset_id, ' AND status_int=1 ',
    'UNION ALL ',
    'SELECT ''DBQP'' AS status, ''SMS'' AS channel, COUNT(1) AS ct FROM sms_queue q WHERE q.dataset_id_bint = ', @dataset_id, ' AND status_int=1 ',
    'UNION ALL ',
    'SELECT ''DBQP'' AS status, ''EMAIL'' AS channel, COUNT(1) AS ct FROM email_queue q WHERE q.dataset_id_bint = ', @dataset_id, ' AND status_int=1 ',
    'UNION ALL ',
    
    -- Handle the <cfif status_ti> logic
    IF(@status_ti = 1,
        CONCAT(
            'SELECT ''RESULT'' as status, ''PHON'' as channel, count(1) AS ct from pge3_results.r_', @dataset_id, ' r WHERE r.RXCallDetailId_int IS NOT NULL ',
            'UNION ALL ',
            'SELECT ''RESULT'' AS status, ''SMS'' as channel, count(1) AS ct FROM pge3_results.s_', @dataset_id, ' s JOIN sms_response r ON s.provider_id_vch = r.SMSMessageID_bint WHERE r.Status_vch IN (''Queued'', ''Dispatched'') '
        ),
        'SELECT ''RESULT'' AS status, ''PHON'' as channel, 0 AS ct UNION ALL SELECT ''RESULT'' AS status, ''SMS'' as channel, 0 AS ct '
    ),
    
    'UNION ALL ',
    'SELECT ''RESULT'' AS status, ''EMAIL'' AS channel, count(1) AS ct FROM email_events WHERE dataset_id_bint = ', @dataset_id, ' AND sg_event_vch = ''processed'' ',
    
    -- Use FIELD() to maintain the 1, 2, 3, 4 sort order visually
    'ORDER BY channel, FIELD(status, ''DSQ'', ''DBQ'', ''DBQP'', ''RESULT'');'
);

-- 3. EXECUTE THE QUERY
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

References & Resources

  • TRAINING Tier 2 Training Session: PG&E PSPS Monitoring Training – Tier 2 Support (Session 1)-20260311_103442-Meeting Recording.mp4
  • SHAREPOINT Knowledge Transfer: PSPS Reverse KT Sessions for Dev Support and Tier 1 Teams - Message Broadcast - PSPS reverse KT - All Documents
  • REPO Query Repository: PGE-PSPS Monitoring (SQL Queries)

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk