Database Query Monitoring

Overview

Database Query Monitor is used to monitor a single query or a set of queries for any given database. Applications Manager provides easy to use and affordable Database monitoring software of different kinds, where database query monitoring is one among them. This SQL-based query monitor allows user to monitor the status of that particular query.

Creating a new Database Query monitor

Using the REST API to add a new DB Query Monitor: Click here

To create a new Database Query Monitor, follow the below given steps:

1. Click New Monitor and select Add New Monitor. Choose Database Query Monitor.
2. Enter the Display Name of the Monitor.
3. Select the Database for which the query is being executed. The Database Query monitor currently supports queries of the following databases - DB2, Informix, Ingres, MS SQL, MySQL, Oracle, Oracle RAC, Postgres, Sybase, DB2 for i, SAP HANA (on-premise) and SAP Max DB. The DB2 for i database will be shown only for trail users and the Users with AS400 Add-on.
4. Enter the Host Type of the Monitor on which the database is running - New or Existing (You can select the an existing host from a drop-down list).
5. Enter the Hostname or IP Address of the host.
6. Enter the Port number in which the database is running.
7. Select the Enable Kerberos Authentication checkbox if you want to monitor MS SQL database through Kerberos authentication.
8. Enter the Username and Password of the database server.
9. Enter the Database name.
10. If you want to connect using a Named Instance, enable the Connect using Named Instance checkbox and specify the instance name for MS SQL database.
11. Check the SSL is enabled checkbox if SSL authentication is enabled for MySQL, MS SQL, Oracle and PostgreSQL databases.
12. Choose jTDS JDBC Driver or Microsoft JDBC Driver option in Driver for SQL Server Connection field to choose the required driver for connecting to the MS SQL database for data collection.
13. Choose either the MySQL Driver or the MariaDB Driver from the Driver option for connecting to the MySQL or MariaDB Database server (Default value: MariaDB Driver).

    Note: For the MySQL Driver, the MySQL jar file should be located in the AppManager_home/working/mysql/MMMySQLDriver/ path.

14. Enter the Query. Please note that the number of queries is limited to five queries. Also, note that the delimiter for a query is new line.
15. Select whether you would prefer Query Output by choosing the Yes or No radio button.
16. If a table row is unavailable in the next poll, you can configure appropriate table row actions (Retain, Delete or Unmanage) by enabling the Manage Table Row option.
17. Enter the Polling Interval. By default, it is 5 minutes.
18. If you are adding a new monitor from an Admin Server, select a Managed Server.
19. Choose the Monitor Group from the combo box with which you want to associate Database Query monitor.

Enable Configure Alarms Template for Database Query Monitor

You can enable a link to Configure Alarms (link will appear on the right top corner of each table in your Database Query Monitor page). Using the link, the user can set Predefined Thresholds for any attributes in the table.

Here is how you can enable Configure Alarms Template for Database Query Monitor:

1. Go to the Settings tab.
2. Click Performance Polling under Discovery and Data Collection.
3. Under Performance Data Collection, click on the Optimize Data Collection tab.
4. Choose Script / Database Query Monitor from the drop-down menu.
5. Check the Enable Configure Alarm Template for Script Monitor and Database Query Monitor option.
6. The Configure Alarm link will appear on the right top corner of each table in your Database Query Monitor page.

Monitored Parameters

Database Query Monitor is used to monitor a single or a specific set of queries for any given database.

• Availability tab gives the Availability history for the past 24 hours or 30 days.
• Performance tab gives the Health Status and events for the past 24 hours or 30 days.
• List view enables you to perform bulk admin configurations.

Using a single query or a given set of queries, you can monitor the status of any given database using Applications Manager Database Query monitor. Queries are the best way to find out whether your database is up and running 24x7. In business enterprises such as an online store, there are a number of applications and databases used for e-commerce. Any interruption in such an environment could mean only one thing: loss of revenue.

The execution of such a query or a set of queries can be automated by setting the polling interval. By fixing the polling interval, user can automate this process and the results are obtained at the end of the polling. The result includes execution time (time taken by the query to provide results) and also displays any error that may occur during regular polling intervals. These errors help identify any issue that may occur with the database.

Let us consider an example. Many enterprise environment run critical applications which need to be up and running 24x7. Let us assume, the status of such applications are maintained in a "APPLICATION_STATUS" table. Using Database Query monitor, the user shall be able to send a select set of queries to that database to find out if they are operational or not.

|-----------------------------------------------------------|

| APPLICATIONS_STATUS                      |

|-----------------------------------------------------------|

| APPLICATIONS_NAME   | Status         |

|-----------------------------------------------------------|

| PURCHASE                    | OK             |

| CRM                                | CRITICAL    |

| PAYROLL                        | OK             |

| LEADS                            | OK             |

|-----------------------------------------------------------|

SELECT * APPLICATIONS_STATUS

By executing the above command using Database Query monitor, the user will then obtain the list of applications that are running along with its status. The user can then identify the applications whose status is 'Critical' and then carry out necessary action by configuring Alarms in Applications Manager. This action could be in the form of creating a ticket, or executing a script to rectify the problem.

Database Query monitor can also be used to identify any bottle necks in the networks which are linked to several databases and help remove them by identifying the correct database which has the issue. This bottle neck issue can arise because there is a problem with the one of applications or with the databases. Using Database Query monitor, user can then execute a given set of queries and analyze the result which provides a clear indication of the error that has caused such an occurance. The result includes the execution time (time taken by the query to generate the result). If the execution time is above a certain pre-assigned threshold, then the issue is with the database or if the result is below the pre-assigned threshold, then the issue is elswhere.

Supported Databases

The Database Query monitor currently supports queries of the following databases:

• DB2
• Informix
• Ingres
• MS SQL
• MySQL
• Oracle
• Oracle RAC
• Postgres
• Sybase
• SAP HANA
• SAP MaxDB
• DB2 for I

Applications Manager also provides the ability to compare various column value in the output by attributes types. Option to Enable or Disable Reports is provided.

Note: Please note that the number of queries is limited to five queries. Total number of rows shown in the output is limited to 50 rows.

Support for Stored Procedures in Database Query Monitor

We have added support for Stored Procedures for all databases that we support in Database Query Monitor. We support Stored Procedures which returns only one Result Set.

To Enable Stored Procedures, go to Settings → Performance Polling → Optimize Data Collection → Script / Database Query Monitor and select Enable Stored Procedure Monitoring for Database Query Monitor.

FAQs and Troubleshooting

1. How can I form an SQL Query?

The query should be in a single line and maximum of 2000 characters. Please note that the number of queries is limited to five queries in one monitor. Also, note that the delimiter for query is new line. Total number of rows shown in the output is limited to 50 rows.

If there is more than one column in the results, we recommend that you set a column which has string values as primary key in the monitor.

The query should have an identifier. For queries involving counts we recommend you have a dummy column and use that as primary key.

Let's take the example below:

Select 'count' as value, count(*) as alert_count from alert;

In the above query, identifier is alert_count and dummy primary column is value. Whenever this query is executed during polling we store the actual count values based on the primary key selection.In this case, the primary key is value.

You can configure an alarm for the alert_count attribute and then define a threshold value.

For reference: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/identifiers-entity-sql

2. How can I form SQL Queries when using Aggregate functions like count, min, max etc?

The query should be in a single line and maximum of 2000 characters. Please note that the number of queries is limited to five queries in one monitor. Also, note that the delimiter for query is new line. Total number of rows shown in the output is limited to 50 rows.

If there are more than one columns in the results, we recommend to have set a column which has string values as primary key in the monitor.

For queries involving counts we recommend to have a dummy column and use that as primary key.

Lets take for example some pending tasks for your application is stored in app_pending_tasks table and you want to monitor the number of pending tasks for the application.

Typically we will form the query as given below:

select count(*) from app_pending_tasks

The problem with the above query is it doesn't have an identifier. Instead you can form the query as given below.

For MySQL, Oracle, Sybase:
---------------------------------
select 'Number of Pending Tasks', count(*) as totalcount from app_pending_tasks

Make the 'Number of Pending Tasks' the primary column.

For MS SQL, PostgreSQL, Db2:
---------------------------------
select 'Number of Pending Tasks' as NumberOfPendingTasks, count(*) as totalcount from app_pending_tasks

Make the 'Number of Pending Tasks' the primary column.

Now you can generate alarms by assigning thresholds and generate reports for 'totalcount'.