Query to Monitor Avangrid Outage File Processing

Purpose                                                                                                               

This KB provides the SQL query used to check the status of outage files processed in the Avangrid Production Database. It helps verify whether outage files are being received and processed correctly.

Query

Run the following SQL query in the Avangrid Production Database to check the processing status of Outage files:

SQL
SELECT *
FROM avangrid.batch_file_log
WHERE filetype_vch = 'outage'
ORDER BY id DESC;

Output Details

The query returns:

• id: Unique identifier for the log entry.
• filename_vch: Name of the outage file processed.
• filetype_vch: Type of file (should be outage).
• runAt_utc: When the file started processing.
• status_vch: Processing status (COMPLETED or FAILED).
• statusAt_utc: When the status was updated.
• records_int: Number of records processed.
• duration_int: Processing duration.
• error_vch: Error message if the file failed.

Examples

Screenshot 1: Failed Files

This shows examples of outage files that failed to process.
Common error messages include No such file, Lock wait timeout exceeded, Incorrect datetime value, etc.

Screenshot 2: Successfully Processed Files

This shows outage files that were processed successfully with status COMPLETED.
You can also see the number of records processed and duration.

Use Case

• Normal Operations: Verify files are processed every 5 minutes.
• Storm Mode: Confirm files are processed every 15 minutes (as per Avangrid notification).
• Troubleshooting: Identify missing or failed files for escalation.

Next Steps

If:

• Files are missing, delayed or getting failed then, escalate to Avangrid engineering team using the slack channel #client-avangrid.
• Point of Contacts: Abhijeet Deshpande, Vipin Kumar Malik, Nicole Biffle, Eric Tillman