Incidents over time

Incident volume is easy to feel and hard to evidence. Everyone remembers a bad week, but far fewer teams can say whether last month was genuinely worse than the one before it.

This tile plots the number of incidents opened on each day of the selected timeframe, including the days where nothing happened. That makes the shape of your incident load visible: steady background noise, one bad Tuesday, or a sustained climb that has crept up unnoticed.

The count comes from each incident’s opened timestamp, grouped by day, so an incident is attributed to the day it was raised rather than the day it was closed. Days with no incidents return zero instead of being skipped, which keeps quiet periods visible rather than letting the chart draw a straight line between two distant points.

Read the trend rather than any single bar. A one-day spike usually has an obvious cause, whereas a rising floor across several weeks is the signal worth acting on, because it means the baseline load on the team has changed.

Before you begin

This article is part of the how to build a DORA metrics dashboard tutorial series.

It pairs with two tiles that answer different questions about the same incidents:

• Active major incidents tells you whether something is happening right now.
• MTTR tells you how long recovery takes once it does.

Together they cover the three questions worth asking of incident data: how often, how bad right now, and how quickly you recover.

Configure the data

Configure the following in the tile editor:

1. Data Source: Select ServiceNow.
2. Data Stream: Select Table Query.
3. Parameters:
   1. Table name: Select Incident.
   2. Filter query: Leave blank to count every incident, or narrow the scope. For example, priority=1^ORpriority=2 counts only high-priority incidents.

4. 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.

   1. Filter | Group | Sort: Select SQL, enter the query below, then click Execute.

      The query runs against a table called dataset. Queries run using DuckDB, which is PostgreSQL-compatible. Table and column names containing spaces or mixed case must be enclosed in double quotes.

      1. This query builds a complete daily timeline for the dashboard timeframe, returning a count of zero for any day on which no incidents were raised.
      2. Copy

         WITH days AS (
           SELECT UNNEST(
             generate_series(
               DATE_TRUNC('day', CAST('{{timeframe.start}}' AS TIMESTAMP)),
               DATE_TRUNC('day', CAST('{{timeframe.end}}'   AS TIMESTAMP)),
               INTERVAL 1 DAY
             )
           ) AS day
         ),
         
         incident_counts AS (
           SELECT
             DATE_TRUNC('day', CAST(opened_at AS TIMESTAMP)) AS day,
             COUNT(*) AS incident_count
           FROM dataset
           WHERE CAST(opened_at AS TIMESTAMP) BETWEEN
                 CAST('{{timeframe.start}}' AS TIMESTAMP)
             AND CAST('{{timeframe.end}}'   AS TIMESTAMP)
           GROUP BY 1
         )
         
         SELECT
           days.day,
           COALESCE(incident_counts.incident_count, 0) AS incident_count
         FROM days
         LEFT JOIN incident_counts
           ON days.day = incident_counts.day
         ORDER BY days.day;

Visualize the data

1. Visualization: Select Bar.
   1. Mapping > X-Axis: Select Day.
   2. Y-Axis: Select Incident Count.
   3. X-Axis > Label: Enter Date.
   4. Y-Axis > Label: Enter Incidents raised.
   5. Options: Enable Grid lines.

2. Click Save.

Other visualizations for this metric:

• Line graph: Best for reading the trend across a longer timeframe. Same mapping as above.
• Table: Best for reading exact daily counts. Columns are added automatically.
• Scalar: Best for a single headline figure rather than a trend. Needs its own query, because a Scalar takes one value.

SELECT COUNT(*) AS incident_count
FROM dataset;

Add a monitor

Incident volume is a good monitoring candidate precisely because it moves slowly. A single busy day rarely warrants attention, but a day that breaks well above your normal range usually means something upstream has changed.

With a baseline of three incidents a day, we warn when the count roughly doubles and error when it triples. Set your own values from the range you actually see in the chart rather than these numbers.

Configure the following on the Monitoring tab:

1. Monitoring: Enable the toggle.
2. Type: Select Threshold.

1. Value: Select Top.
2. Column: Select Incident Count.
3. Evaluate by: Select Day.
4. Conditions:
   1. Error: Enable the toggle, then configure as greater than and supply an appropriate value. For our example we’ll enter 9.
   2. Warning: Enable the toggle, then configure as greater than and supply an appropriate value. For our example we’ll enter 6.

5. Click Save.

Was this article helpful?