Failing records query

A failing records query is a query that represents the set of records you want to see when analyzing an incident. If you enter a failing records query, when an incident is generated, you can run the query on the Failing Records tab of the incident. File-based datasources (AWS S3, Azure blob storage) don't support failing records queries.

Null % and most Conformity metrics have system-generated default failing records queries. Conformity metrics where the Condition is Is increasing or Is decreasing don't have default failing records queries). You don't need to add failing records queries to metrics that have default failing records queries unless you want something different from the default.

Add a failing records query to a metric

You add a failing records query on the Configure Metric tab of a metric. You can review the metric's query history to see the SQL the metric uses, which might make a good starting point (you might need to run a preview first). Make sure your query meets the SQL requirements.

  1. On Step 2 of a metric's configuration, under Failing records query select Add +. The button text changes to Validate Query and a box appears for you to enter the SQL.

  2. Enter the SQL for the failing records query in the box. When you are done, click Validate Query.

  3. After you validate your query, save the metric.

SQL requirements

Two requirements apply for both incremental and full table metrics:

  • You cannot add an ORDER BY clause to a failing records query.
  • If you want to create a sliced metric, you must add a GROUP BY clause on the intended slice column. You can then choose this column as the slice column after you validate your SQL in step 3 (the next numbered item, below). Note that for incremental metrics, the slice column can't be the same as the column with aggregated metric values in your SELECT clause.

Additional SQL requirements for incremental metrics

  • Your WHERE clause must specify a date range using the time-based column and two tags: one producing the start of the range and another for the end of the range. These tags reflect the kind of time-based column you have: (1) use {start_ts} and {end_ts} for a timestamp column, (2) use {start_date} and {end_date} for a date column, and (3) use {start_datetime} and {end_datetime} for a datetime column.
  • The timestamp in your SELECT must match the aggregation interval that you choose in the drop down selection after you click Validate Query. For example, if your metric does a SELECT of a date, you cannot set Aggregation Interval to hourly.
  • Your SQL must GROUP BY the timestamp column.