Story for the example: – You own a media web site, where people upload and share media content. You want to keep an eye on the quantity of uploaded content and the number of views per media type (audio, photo, video).
Here we use this type of the SQL Query Result monitor to retrieve at each monitoring check the content quantities and views by media type.
1.Setting up the Monitor
Your SQL query will look something like:
SELECT media_type, Count(*) AS media_by_type, SUM(`views`) AS total_views FROM `media` GROUP BY media_type`;
As you can see the query extracts the counts and the number of views per media type.
In the SQL Query monitor setup wizard select Monitor fields as metrics for multiple objects and enter the query in the box. Click Continue to Mappings. The items that you have selected in the query will be listed.
For each of the items selected in the query you need to specify the type (integer, float or string). We need this to know which of the metrics are expected to be numeric, so that we plot them on the chart.
You can also provide the UOM (Unit of Measurement) for each of the items, so that we show it appropriately.
If you change the SQL query at any time, click Refresh to refresh the item list.
Now you need to select the primary field.
If you have selected more than one metric in your query, you will see there the Object specifying field combo appear on the screen.
The item that you select in the Object specifying field box will be used as the primary field in your monitor data. You will be able to plot in the chart view of the monitor the values of any other numeric item that you have selected against any of the values in the Object specifying field (objects).
Just click Add to add the monitor in your Monitis dashboard. Wait for the first data to be loaded to the monitor (approximately 5 minutes).
See the monitoring results explained below under Viewing Monitor Data.
2.Viewing Monitor Data
In the table view, you can see every time the check was ran and the query was executed, the output of the query is 3 rows, corresponding to the number of unique values in the media_type field, which is the object containing field (we call it also “the primary field” throughout this document) that we have selected:
If we switch to the chart view, we can see that:
– Unique values (objects) of the primary metric media_type – “video”, “photo”, and “audio” – are used to show for any or all of them (you can toggle them OFF/ON on the chart) the over time change of any other numeric item you have selected from your DB in the SQL query: in our example, these are media_by_type and total_views. You can select one of these items from the drop down box in the top right corner of the monitor module. In the below shown screen, total_views is selected to display its data for the objects.
You can configure thresholds (see Thresholds) to define for your monitor conditions of entering Critical or Warning state. You can then add alert rules (see below Setting Up an Alert Rule) to receive alerts when your monitored metrics cross your predefined thresholds.
Click the icon in the top right corner of the monitor to open the context menu, and select Thresholds. The threshold includes two conditions connected by a logical OR. If any of the conditions matches, the monitor will enter your specified state.
- Check fails (response timed out, connection failure, etc)
- Any or all of your selected metrics meet the failure conditions you specify for them
See below explanations on how to configure thresholds, based on our example.
Select the severity level (Critical or Warning) and the number of consecutive checks that the threshold condition must hold true for the monitor to enter that state. Here we have selected “Critical”, and the number of consecutive checks to be 1.
As here we are monitoring metrics for multiple objects, first of all we need to select the object(s). As you can see it in the above screen, you can select any or all of them from the list before you proceed to specifying the metrics conditions. Here we have selected 2 of our objects (unique values in the primary field, which is media_type in our example): photo and video.
Chose whether the threshold condition applies to Any or All of the selected objects. Here we have selected All. It means that metrics condition that you specify next must be met for all the selected objects in order for the monitor to enter Critical state.
Now let’s select the metrics and define failure conditions for them.
Notice that the metrics list contain all your metrics selected in the SQL query except for the primary one (media_type in our example).
Here we have selected media_by_type to be greater than 1,300, and total_views greater than 72,000.
Select whether All or Any of the specified metric conditions must be met for the monitor to enter the Critical state. Here we have selected All, so the 2 metric conditions are tied by logical AND.
Click OK to save the threshold.
4.Setting up an Alert Rule
You can now set up an alert rule so that you receive alerts whenever your monitor crosses your predefined threshold.
Click the icon in the top rightmost corner of the monitor to open the context menu, and select Alert Rules. You will be taken to the Alert Rules tab of the monitor settings. Click Add Alert Rule. The New Alert Rule window will open.
Choose the contact (or contact group) to send the alerts to.
Select the threshold type (Critical in our example).
Click Add Rule to add the alert rule.
You will start receiving alerts whenever a Critical threshold is violated in your monitor.
See Alert Rules in Alerts 2.0 for more info about alert rules.