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, total across all media types (audio, photo, video).
Here we use the Monitor Fields as Metrics type of the SQL Query Result monitor to retrieve at every monitoring check the total quantity of content and total views across all media types.
1. Setting up the Monitor
Your SQL query will look something like:
SELECT COUNT(*) AS all_media, SUM(`views`) AS total_views FROM `media`;
As you can see, the query simply extracts the count of all media and the sum of all views across all media.
In the SQL Query monitor setup wizard select Monitor fields as metrics 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 can 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.
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 monitor 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 just one row, showing the total quantity of media files and the total number of views.
If we switch to the chart view, we will be able to see the change of both metrics (all_media, total_views) over time.
3. Configuring Thresholds
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 specified 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.
Select the metrics and define failure conditions for them. Use the green “+” button to add a metric condition, or the red “-” to remove.
Here we have selected all_media to be greater than 3,200 (see the screen below), and total_views greater than 180,000 (e.g. increase in content quantity and number of views to serve as an indication for you to consider expanding your website hosting capacities: disk space, RAM, etc.).
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 Any, so the 2 metrics conditions are tied by logical OR.
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.