Adding SQL Query Result Monitor

To add an SQL Query Result monitor, go to Monitors -> Custom Monitors -> SQL Query Result.
Monitors_

The Add Server/Device Monitor – Step 1 window will open.
Add_SQL_monitor_Step_1

Select the Monitis Agent from the list and click Next.

Note: If you want to run a monitor from a host that has no Monitis agent on it, then you need to download and install a new agent there. Click the Download Agent button to download a new agent.

See  Install Windows Agent and Install Linux Agent guides for more info.

The Add Server/Device Monitor – Step 2 window will open.Add_SQL_Monitor_step_2

Select a group for this monitor. To add a new group, click the green “+” button.

 

Connect Your DB

Select your RDBMS from the RDBMS combo box: Oracle, MySQL or MS SQL.

Provide the Host and Port to connect to the DB.

Provide the name under SID (Oracle) or Database (MySQL, MS SQL) for the DB to connect to.

Enter the User and Password for the DB you want to monitor to enable Monitis access to it.

Choose Monitor Type

You need now to select between two types of SQL Query monitor. See 2 radio buttons further in the window to choose from:

1.Monitor fields as metrics

2.Monitor fields as metrics for multiple objects

1.Monitor fields as metrics

Select this option if the expected query output must be only one row. The monitor will show query output results over time.

For example, this option could be used to monitor the total quantity of customers’ orders in specific product category. In this case your SQL query will look something like:

SELECT COUNT(*) AS total_orders FROM orders WHERE orders.category =’books’;

Please refer to Monitor Fields as Metrics: an Example for detailed explanations on how to configure this type of the SQL Query monitor.

2.Monitor fields as metrics for multiple objects

Select this option if the expected query output may be more than one row. The monitor will show query output results over time.

For example this option could be used to monitor the total quantity of customers’ orders in different product categories. In this case your SQL query will look something like:

SELECT COUNT(*) AS total_orders, orders.category AS category FROM orders WHERE orders.category =’books’ OR orders.category =’electronics’ GROUP BY orders.category;

In this case one of the items in the SQL query output must contain unique data (objects). In our example above it is orders.category. Such an item can be then selected as a primary field by you, to show in the chart view of the monitor the over time change of any numeric item(s) in the SQL query against any of the objects in the primary field that you choose.

Please refer to Monitor Fields as Metrics for Multiple Objects: an Example for detailed explanations on how to configure this type of the SQL Query monitor.

 

Provide the SQL query

Enter your query in the Query box. Only Select queries are allowed.

Add_SQL_Monitor_Step_2.2

Note: the allowed limit of items that you can select in the SQL query is 10. If you exceed this limit, or use “select *” to try to retrieve all items from the table you will receive an error message.

Error

Rest of configuration

Please refer to Monitor Fields as Metrics: an Example and Monitor Fields as Metrics for Multiple Objects: an Example for detailed explanations on writing the SQL query, receiving data, viewing the results, configuring thresholds and setting alert rules.