CMP Abnormal Usage Alerts Monthly Report

This article explains how to generate a monthly report for CMP Abnormal Usage Alerts using the combined_summary_results table. The report summarizes alerts sent to customers by month, alert type, communication channel, and alert volume.

Overview

This report provides a summary of alerts sent to customers categorized by:

Month
Alert Type
Communication Channel (SMS, Email, etc.)
Total Number of Alerts Sent

Results are filtered using a specified date range and CPPID for the utility company.

Important Note

The report uses timestamps stored in UTC and converts them to US/Eastern time before calculating reporting months.

Purpose

This query retrieves the monthly count of alerts sent for Central Maine Power (CMP) across different alert categories and communication channels.

Database Server	10.10.4.10
Database Name	avangrid

Prerequisites

Access to the Avangrid reporting database.
Permission to query combined_summary_results.
Valid reporting period.
Correct CPPID for the utility company.

Parameters

Parameter	Description	Example
`@reportFrom`	Start date of report period	2024-12-01 00:00
`@reportTo`	End date of report period	2025-02-28 23:59
`@cppid`	Company identifier	2
`@companyTitle`	Company name displayed in report	CMP

SQL Query


SET @reportFrom = '2024-12-01 00:00';
SET @reportTo = '2025-02-28 23:59';
SET @cppid = 2;
SET @companyTitle = 'CMP';

select
m.title as Company,
concat(MONTHNAME(convert_tz(m.sentAt_utc, 'utc', 'US/Eastern')), ', ', m.year) as AlertMonth,
m.AlertType AS AlertType,
m.channel,
COUNT(m.id) AS AlertsCount
FROM (
select @companyTitle as title,
id,
sentAt_utc,
month(convert_tz(sentAt_utc, 'utc', 'US/Eastern')) as month,
year(convert_tz(sentAt_utc, 'utc', 'US/Eastern')) as year,
channel_vch as channel,
case
when type_vch = 'Outage' then 'Outage'
when type_vch = 'Usage' and subType_vch like 'Amount-Exceeded%' then 'Usage Amount Exceeded'
when type_vch = 'Usage' and subType_vch like 'Change%' then 'Usage Change'
when type_vch = 'Usage' and subType_vch like 'Weekly%' then 'Usage Weekly'
ELSE CONCAT(type_vch, ' ', subType_vch)
END AS AlertType
from avangrid.combined_summary_results
where sentAt_utc BETWEEN convert_tz(@reportFrom, 'US/Eastern', 'utc')
AND convert_tz(@reportTo, 'US/Eastern', 'utc')
and cppId_bi = @cppid
) m
group by m.title, m.year, m.month, m.AlertType, m.channel;

How the Query Works

1. Timezone Conversion

Alert timestamps are stored in UTC. The query converts them to US/Eastern to ensure reporting aligns with customer local time.


convert_tz(sentAt_utc, 'utc', 'US/Eastern')

2. Alert Type Classification

Database Value	Report Alert Type
Outage	Outage
Usage + Amount-Exceeded	Usage Amount Exceeded
Usage + Change	Usage Change
Usage + Weekly	Usage Weekly

Any unmatched values are displayed as:


type_vch + subType_vch

3. Aggregation

The report groups data by:

Company
Year
Month
Alert Type
Channel

The query calculates alert volume using:


COUNT(m.id) AS AlertsCount

Sample Output

Company	AlertMonth	AlertType	Channel	AlertsCount
CMP	December, 2024	Outage	SMS	1200
CMP	December, 2024	Usage Amount Exceeded	Email	450

Troubleshooting & Common Issues

Issue	Possible Cause	Resolution
Incorrect Alert Counts	Incorrect CPPID or date range	Validate CPPID and reporting period.
Unexpected Month Values	Timezone conversion issue	Verify US/Eastern timezone conversion.
No Records Returned	No data within specified period	Review date range and alert activity.

Use Cases

Customer reporting requests
Abnormal usage alert analysis
Monthly alert volume tracking
Channel usage reporting

Best Practices

Always validate CPPID before running the query.
Use US/Eastern reporting dates.
Review output for unexpected alert classifications.
Export results for audit and customer reporting.

Important Notes

Ensure the correct CPPID is used for the utility company.
Confirm the reporting period is based on US/Eastern timezone.
The combined_summary_results table contains aggregated alert data.

[Avangrid] Generating CMP Abnormal Usage Alerts Report