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.
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."
[<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