Alerts over time

This tile establishes a baseline for on-call load by showing how many alerts are generated each day over time.

We'll use SQL Analytics to build a complete daily time series of alerts, classifying each one as either in hours or out of hours.

Every day in the selected timeframe is represented, returning a count of zero where no alerts occurred, so trends and changes in on-call disruption are easy to see over time.

Rather than focusing on individual alerts, this approach highlights trends in alert volume, making it easy to spot sustained increases, sudden spikes, or periods of relative calm.

Configuring the tile

Configure the following in the tile editor:

1. Data source: Select Azure.
2. Data stream: Select Alerts.
3. Objects: Select the monitored objects you want to track alerts for.
4. Parameters:
   1. Monitor condition: Select Fired.
   2. Severity: Leave blank to capture every severity level.

5. Timeframe: Select the timeframe you want to track. Note that after adding a monitor or configuring a KPI the Use dashboard timeframe option is disabled.

6. SQL Analytics: Enable the toggle then configure the following:
   1. SQL > Query: Enter the following query then click Execute.
      
      Copy

      -- Step 1: Normalize raw alert timestamps
      WITH alerts AS (
        SELECT CAST("properties.essentials.startDateTime" AS TIMESTAMP) AS alert_ts
        FROM dataset1
      ),
      -- Step 2: Build a complete list of calendar days
      calendar_days AS (
        SELECT strftime(day_ts, '%d/%m/%Y') AS day
        FROM (
          SELECT UNNEST(
            generate_series(
              DATE_TRUNC('day', (SELECT MIN(alert_ts) FROM alerts)), -- first alert day
              DATE_TRUNC('day', (SELECT MAX(alert_ts) FROM alerts)), -- last alert day
              INTERVAL 1 DAY                                         -- step 1 day at a time
            )
          ) AS day_ts
        )
      ),
      
      -- Step 3: Count alerts per day and classify as in-hours or out-of-hours
      daily_alert_counts AS (
        SELECT
          strftime(DATE_TRUNC('day', alert_ts), '%d/%m/%Y') AS day,
          -- Categorize alerts by working hours definition:
          CASE
            WHEN dayofweek(alert_ts) BETWEEN 1 AND 5
             AND alert_ts >= DATE_TRUNC('day', alert_ts) + INTERVAL 9 HOUR
             AND alert_ts <  DATE_TRUNC('day', alert_ts) + INTERVAL 18 HOUR
            THEN 'In hours'
            ELSE 'Out of hours'
          END AS hours_bucket,
          -- Count alerts per day per category
          COUNT(*) AS alert_count
      
        FROM alerts
        GROUP BY day, hours_bucket
      ),
      -- Step 4: Define the two possible hour categories explicitly
      hour_categories AS (
        SELECT *
        FROM (VALUES ('In hours'), ('Out of hours')) AS v(hours_bucket)
      )
      -- Step 5: Combine everything
      SELECT
        calendar_days.day,
        hour_categories.hours_bucket,
        COALESCE(daily_alert_counts.alert_count, 0) AS alert_count -- replace NULL with 0
      FROM calendar_days
      CROSS JOIN hour_categories
      LEFT JOIN daily_alert_counts
        ON daily_alert_counts.day = calendar_days.day
       AND daily_alert_counts.hours_bucket = hour_categories.hours_bucket
      ORDER BY
        strptime(calendar_days.day, '%d/%m/%Y'),
        hour_categories.hours_bucket;

7. Visualization:
   1. Mapping:
      1. Type: Select Line.
      2. X-Axis: Select Day.
      3. Y-Axis: Select Alert Count.
   2. X-Axis > Label: Enter Date.
   3. Y-Axis > Label: Enter Alert count.
   4. Options: Enable the following toggles.
      • Data points
      • Shading
      • Grid lines
8. Click Save.

Adding a monitor

This tile is an ideal candidate for monitoring because changes in overall alert volume are often gradual and easy to miss in day-to-day operations.

With a baseline of two alerts per day, we trigger a warning when daily alert volume increases by roughly 50-70%, and an error when it doubles. This keeps the monitor sensitive to meaningful change without reacting to normal fluctuation.

Configuration

Configure the following in the tile editor for your Alerts per day tile:

1. Monitoring: Enable the Monitoring toggle.
2. Type: Select Threshold.
3. Value: Select top.
4. Column: Select Alert Count.
5. Evaluate by: Select Day.
6. Conditions:
   1. Error: Enable the toggle, then configure as greater than and then supply an appropriate value. For our example we'll enter 4.
   2. Warning: Enable the toggle, then configure as greater than and then supply an appropriate value. For our example we'll enter 3.

Was this article helpful?