Knowledge Base

cancel
Showing results for 
Search instead for 
Did you mean: 

Limitations of Database Monitoring Custom Metrics

Custom metrics allows the user to specify a SQL query to run on a monitored DB every minute, and collect the results of the query.

 

This document specifies restrictions and limitations, as well as some examples and debugging techniques. 

 

Restrictions and Limitations:

  • The query must always return a positive integer number.
  • The query should not return more than one column.
  • The user can define up to 20 custom metrics per monitored DB.
    • This limit is configurable through admin.jsp with the following property; however, it should be increased graciously.
    • Yo.png
  • For Security reasons, we only allow SELECT queries.

 

 

How to debug?  

  • If the Agent encounters an error while running the query, the Agent will send an error event to the Controller.
    • View these error events by clicking on Database -> Events in the Controller UI.
  • For more details on an error, view the database Agent logs located at <db_install_dir>/logs.
    • The errors are self-descriptive and can be searched using the keywords "CustomMetricsCollector," "ERROR," or "WARN."
    • Examples:
    • [<MyTestMysql>-Scheduler-3] 29 May 2017 11:04:59,702 ERROR CustomMetricsCollector - Error collecting data for database 'MyTestMysql'
      java.sql.SQLException: Invalid custom metric: test4 , actual data type is non-numeric and expected data type is numeric (Custom Metric : test4)
    • 09 Jun 2017 13:40:08,889 ERROR [<Database>-Scheduler-1] CustomMetricsCollector:130 - Error collecting data for database 'Database' java.sql.SQLException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=4.16.53 (Custom Metric : TestMetric)

 

 

Query Examples with MySQL:

1.select COUNT(1) "total" from dual; - Worked
Output: 1

2. select 9+ ' ' +':00' num from dual; - Worked.
Output: 9 

3. select name from user where id=3 - Not working as expected
Output: root

 

4. select id as node_id from application_component_node where id NOT in (select distinct(node_id) from metricdata_hour where ts_min > ((select max(ts_min) from metricdata_hour)) - 60*2) - Worked/Not Worked based on output

Output: Fails if returns multiple records

 

Screen Shot 2017-05-29 at 11.01.55 AM (1).pngScreen Shot 2017-05-29 at 11.07.34 AM (1).png

 

 

Related Links:

 

 

 

 

Version history
Revision #:
4 of 4
Last update:
‎02-04-2019 01:53 PM
Updated by:
 
Labels (1)


Found this article helpful? Click the Thumbs Up button.
Have an additional comment? Post it below.
Comments

Are there any restrictions on Joins or Table aliases? I think queries terminating with a semi-colon fail as far as I've observed.

What are the functions allowed/disallowed in SELECT queries? Looks like COALESCE is restricted for some reason? "Query validation failed because it has keyword 'COALESCE' that is trying to manipulate DB."


Found this article helpful? Click the Thumbs Up button.
Have an additional comment? Post it below.