Database Query Monitor FAQ

Database Query Monitor FAQ

How to fix an "Unable to find the Primary Key for tablename" error displayed after adding a query monitor?


After adding a Database Query Monitor, users must select and update a Primary Key for each table. The primary key can be a single column or a combination of multiple columns, but it must uniquely identify each row in the result set.

Keep in mind:
  1. Primary keys must be unique for every row.
  2. Columns created using aggregate functions such as AVG, SUM, MIN, MAX, or COUNT cannot be used as primary keys, as these values are computed and not row-specific.
  3. Count-based columns (results of COUNT operations) are also ineligible since they are not unique per row.
This ensures accurate row identification and consistent monitoring results.

How to fix automatically removed thresholds or actions?

If you notice that the thresholds or actions you configured are getting removed automatically—especially for new rows in a Database Query Monitor or Script Monitor—it’s likely because those new rows didn’t inherit the existing thresholds.

To fix this:
  1. Use the Configure Alarm Template option available at the top of each table. This ensures that any threshold or action you configure is applied as a template.
  2. Go to Settings → Performance Polling → Optimize Data Collection → choose Script/Database Query monitor as the monitor type, and select Enable Configure Alarm Template for Database Query Monitor and Script Monitor then save.
  3. Once enabled, thresholds and actions set through the alarm template will automatically apply to any newly discovered rows during polling, preventing manual reconfiguration and ensuring consistent alerting.

How to add stored procedure in query monitor?

  1.  To monitor stored procedures (like queries that begin with EXEC, SHOW, etc.) in database query monitor enable performance polling option.
    1. 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.

      

How do I form SQL Queries using Aggregate functions?

When using aggregate functions like COUNT, MIN, MAX, etc., in your Database Query Monitor, it's important to ensure each row has a unique identifier. Since aggregate functions return summarized data (e.g., totals), they lack a natural primary key, which is required for Applications Manager to track and display results effectively.

Recommended Approach: Use a Dummy Column as Primary Key
Let’s say you want to monitor the number of pending tasks in your application. These tasks are stored in a table named app_pending_tasks.
A typical query might look like this:
Quote
SELECT COUNT(*) FROM app_pending_tasks
However, this lacks a unique identifier and will not work correctly in Applications Manager. Instead, modify the query to include a static/dummy column:
  1. For MySQL, Oracle, and Sybase:
Quote
SELECT 'Number of Pending Tasks', COUNT(*) AS totalcount FROM app_pending_tasks
  1. For Kingbase, MS SQL, PostgreSQL, and Db2:
Quote
SELECT 'Number of Pending Tasks' AS NumberOfPendingTasks, COUNT(*) AS totalcount FROM app_pending_tasks
This method ensures that the result row has a unique and recognizable identifier for Applications Manager to track.
Notes
Common Errors to Avoid
  1. Multiline Queries:
    Each query should be written in a single line. Writing across multiple lines causes each line to be treated as a separate query.
  2. Semicolons:
    Avoid ending queries with a semicolon (;), especially when working with Oracle databases.
  3. Quoted Values in MS SQL:
    When including quoted values (e.g., 'APM.API.0101') in your query, you must provide an alias (For example: SELECT TOP 1 CreateTime, UserId, 'APM.API.0101' AS ColumnName FROM TempTable).

                  New to ADSelfService Plus?

                    • Related Articles

                    • Self monitor Applications Manager using Real User Monitoring

                      We can monitor the Applications Manager using Real User Monitoring with a Java Script injection and this can be used to measure the Applications Manager's performance continuously. All you need is to install and setup the Real User Monitoring (RUM) ...
                    • Script/Custom Monitors - Alarms configured for Table rows were missing

                      In the Applications manager , users have the ability to manage the table rows of a script or custom monitor type according to their specific requirements. This can be done by enabling the "Enable Script Row Deletion" option. For example, let's ...
                    • DNS Monitor - Troubleshooting

                      Common DNS Monitor Errors and Troubleshooting Guide 1. Host Not Found Description: The DNS server was unable to locate the requested lookup address. Possible Causes: This may happen if the hostname is incorrect, the domain does not exist, or there is ...
                    • How to get ISP and Region data in Real User Monitor?

                      The default setting of the Real User Monitor does not display ISP and region data for the end user. To obtain the data, you have the option to use a third-party Geolocation API. Currently, RUM exclusively supports APIs that provide responses in JSON ...
                    • Azure SQL Database - FAQ

                      1. Why have we removed the Diagnostic Settings dependency in Azure SQL Database monitor? Till APM v15240: Azure SQL Database was using Diagnostic Settings to perform data collection. Prerequisites had to be fulfilled to turn on Diagnostic Settings ...