Deeper Alert Event Analysis

Question: 

We have KPI Alerts going out but I need to do a deeper analysis of the alerts. How do I do this?

Solution:

In the event you need to do a deeper analysis of alert events generated in Metric Insights (MI), you can query the MI database to tease out the information you need. The primary table holding alert event information is alert_event in the dashboard database. You can either create a dataset querying against the default 'Dashboard DB' data source, or query directly in MySQL if you have access to the db server.

For example, let's say you have a metric with a large number of alerts and you want to know how many alert events satisfied some condition. The dashboard.alert_event table contains all the fields needed to run this analysis. Here are the two primary fields you'll need to structure your queries:

1) element_id  This represents the Metric in question. You can also grab this from the URL when viewing the metric or from the ID column in the Elements List page as shown below:

2) alert_rule_id — This represents the Alert Rule that was created for the metric. You can also grab the ID from the URL by going to the Content menu > Alerts > KPI Alerts > clicking on the Alert Rule > grab the numeric value from the URL (../id/some_number):

After you've identified the Metric and Alert Rule you want to analyze by element_id and alert_rule_id, you can use the following fields to further structure your query:

  • is_first_time_only_ind — ‘Y’ (yes) means the Alert Event is generated for the first time only when some defined condition is met per an Alert Rule. Subsequent data points that meet the condition do not generate an Alert Event until the value drops out of the condition.
  • is_frequently_ind — ‘Y’ (yes) means the Alert Event is generated when some defined condition is met for a range of subsequent values (N out of M data points)
  • is_frequently_first_time_only_ind — ‘Y’ (yes) means if the parameter is_frequently_ind is met for the first data point in the range, then an Alert Event is generated for only the first data point in that range. 
  • alert_mask — This is an indicator that combines the three previous parameters and is the result of a binary sum as shown below. Note, the parameter alert_every_time is always equal to 1 if enabled for the alert rule:

Row (condition) name in the table alert_event

TRUE

FALSE

+

alert_every_time

1

-

+

alert_first_time_only

2

0

+

alert_frequently

4

0

+

alert_frequently_first_time_only

8

0

=

alert_mask

Sum()


Example: The query below essentially states "find all alert events which have been generated for a metric with ID '123' and the alert_first_time_only condition is met":

SELECT COUNT(*)
FROM alert_event
WHERE element_id=123 AND alert_mask = 3;


0 Comments

Add your comment

E-Mail me when someone replies to this comment