Alert Definition Data Query Structures

The data query used in an alert definition always specifies a filter condition to be evaluated. Different kinds of data query conditions can be specified: raw, KPI, single group by, or multigroup by queries. Simple filters and aggregate filters are used to establish these query conditions.

This section describes and provides examples for the filter structure and the structures for all query types.

This is an experimental feature.

• Simple Filters
• Aggregate Filters
• Raw Data Query Conditions
• KPI Data Query Conditions
• Single Group By Queries
• Multigroup By Queries

Simple Filters

Because alert conditions are checked periodically, filter conditions must be set properly, especially if the queried data is limited to a dynamic timeframe.

Filters are specified using the following structure. The example specifies a dynamic filter that looks for sales records with sale dates that occurred in the last month.

"filters": [
   {
      "operation": "BETWEEN",
      "path": {
         "name": "sale_date"
      },
      "value": [
         "$start_of_month_-1_month",
         "$end_of_month_-1_month"
      ]
   }
],

The following parameters are included in the filter structure.

Aggregate Filters

Aggregate filters define thresholds using the aggregateFilters object in the following structure. Usually a single threshold is defined, but more complex aggregate filters can be specified. The following aggregate filter example searches for groups (for example, product categories) with 1 to 1000 sales records in which the sum of the sales price fell between $2 and $700,000.

"aggregateFilters": [
   {
      "metric": {
         "type": "COUNT"
       },
      "operation": "BETWEEN",
      "value": [
         1,
         1000
      ]
   },
   {
      "metric": {
         "type": "FIELD",
         "field": {
            "name": "price"
         },
         "function": "SUM"
      },
      "operation": "BETWEEN",
      "value": [
         2,
         700000
      ]
   }
],

The following parameters are included in the aggregateFilters query structure.

Raw Data Query Conditions

A raw data query condition supports only simple filters. The following sample raw query condition searches for sales records with sale dates that occurred in the last month and with prices that equal or exceed $1,000,000.00. When records are found meeting these conditions, and alert notification is sent.

{
   ...
   "condition": {
      "sourceId": "<rts-source-id>",
      "dataQuery": {
         "queryType": "RAW",
         "filters": [
            {
               "operation": "BETWEEN",
               "path": {
                  "name": "sale_date"
               },
               "value": [
                  "$start_of_month_-1_month",
                  "$end_of_month_-1_month"
               ]
            },
            {
               "path": {
                  "name": "price"
               },
               "operation": "GE",
               "value": 1000000
            }
         ]
      },
      "activateAlertWhenData": "EXISTS"
   }
}

The following parameters are included in the raw data query structure.

KPI Data Query Conditions

A KPI query condition is a single-dimension query, without aggregations. Filters can be used to reduce the data to be evaluated. The threshold is defined by aggregate filters.

The following sample KPI query condition searches for sales records from the state of Alabama with sale dates that occurred in the last month and with total planned sales between $1 and $3,035.00. If no records can be found that meet these conditions, an alert notification is sent.

{
   ...
   "condition": {
      "sourceId": "<rts-source-id>",
      "dataQuery": {
         "queryType": "AGGREGATE",
         "filters": [
            {
               "operation": "BETWEEN",
               "path": {
                  "name": "sale_date"
               },
               "value": [
                  "$start_of_month_-1_month",
                  "$end_of_month_-1_month"
               ]
            },
            {
               "path": {
                  "name": "state"
               },
               "operation": "EQ",
               "value": "Alabama"
            }
         ],
         "dimensions": [
            {
               "aggregations": []
            }
         ],
         "aggregateFilters": [
            {
               "metric": {
                  "type": "FIELD",
                  "field": {
                     "name": "plannedsales"
                  },
                  "function": "SUM"
               },
               "operation": "BETWEEN",
               "value": [
                  1.00,
                  3035.00
               ]
            }
         ]
      },
      "activateAlertWhenData": "NOT_EXISTS"
   }
}

The following parameters are included in the KPI data query structure.

Single Group By Queries

Single group by queries are similar to KPI data queries, but add one aggregation. Filters can be used to reduce the data to be evaluated. The threshold is defined by aggregate filters. The window attribute may be defined for the dimension section. But it does not affect the query execution logic.

The following sample single group by query condition searches for sales records from the state of Alabama, aggregated by product group, with sale dates that occurred in the last month and with total planned sales between $1 and $3,035.00. If no records can be found that meet these conditions, an alert notification is sent.

{
   ...
   "condition": {
      "sourceId": "<rts-source-id>",
      "dataQuery": {
         "queryType": "AGGREGATE",
         "filters": [
            {
               "operation": "BETWEEN",
               "path": {
                  "name": "sale_date"
               },
               "value": [
                  "$start_of_month_-1_month",
                  "$end_of_month_-1_month"
               ]
            },
            {
               "path": {
                  "name": "state"
               },
               "operation": "EQ",
               "value": "Alabama"
            }
         ],
         "dimensions": [
            {
               "aggregations": [
                  {
                  "type": "TERMS",
                  "field": {
                     "name": "product_group"
                  }
               }
            ]
         }
      ],
      "aggregateFilters": [
         {
            "metric": {
               "type": "FIELD",
               "field": {
                  "name": "planned_sales"
               },
               "function": "SUM"
            },
            "operation": "BETWEEN",
            "value": [
               1.00,
               3035.00
            ]
         }
      ]
   },
   "activateAlertWhenData": "NOT_EXISTS"
   }
}

The following parameters are included in the single group data query structure.

Multigroup By Queries

Multigroup by queries are the same as Single Group By queries, except that they allow for more than one aggregation.

Top of the Top sorting is not supported; only simple sorting is supported. We recommend that no sorting be specified at all.

The following sample multigroup by query condition searches for sales records from the state of Alabama, aggregated by sales day and city, with sale dates that occurred in the last month and with total planned sales between $1 and $1.050.00. If records can be found that meet these conditions, an alert notification is sent.

{
   ...
   "condition": {
   "sourceId": "<rts-source-id>",
   "dataQuery": {
      "queryType": "AGGREGATE",
      "filters": [
         {
            "operation": "BETWEEN",
            "path": {
               "name": "sale_date"
            },
            "value": [
               "$start_of_month_-1_month",
               "$end_of_month_-1_month"
            ]
         },
         {
            "path": {
               "name": "state"
            },
            "operation": "EQ",
            "value": "Alabama"
         }
      ],
      "dimensions": [
         {
            "aggregations": [
               {
                  "type": "TERMS",
                  "field": {
                     "name": "user_city"
                  }
               },
               {
                  "type": "TIME",
                  "field": {
                     "name": "saledate"
                  },
                  "granularity": "DAY"
               }
            ]
         }
      ],
      "aggregateFilters": [
         {
            "metric": {
               "type": "FIELD",
               "field": {
                  "name": "planned_sales"
               },
               "function": "SUM"
            },
            "operation": "BETWEEN",
            "value": [
               1000.00,
               1050.00
            ]
         }
      ]
   },
   "activateAlertWhenData": "EXISTS"
   }
}

The following parameters are included in the multigroup by data query structure.