What is the best way to list top 50 agents with most SQL metrics?

Document ID : KB000093597
Last Modified Date : 01/05/2018
Show Technical Document Details
Question:
What is the best way to list top 50 agents with most SQL metrics?

I want to create a top 50 hit list of agents giving the most SQL metrics so we can start to address our high level of metrics as its estimated 70-80% of our metrics are due to SQL metrics specifically.

What is the best way to do this one-time and/or on an ongoing basis without creating significant overhead?

 
Answer:
Use APMSqlServer. Overhead will not be significant as long as the query is not hit frequently.
 
You can write your own query on the table we exposed. It is sql-92 standard sql.
 
Here is a suggestion of an optimized query for this purpose:
-----------------------------
SELECT full_agent_name,
count(metric_path) as sql_metric_count
FROM (
SELECT distinct metric_path,
(domain_name||'|'|| agent_host||'|'|| agent_process||'|'|| agent_name) as full_agent_name
FROM numerical_metric_data
WHERE
--agent_name like 'JBoss Agent%' AND
metric_path like 'Backends|%|SQL|%' AND
ts between TIMESTAMPADD(SQL_TSI_SECOND, -30, now()) AND now()
) a
GROUP BY full_agent_name
ORDER BY sql_metric_count desc
LIMIT 50
------------------------------
 
Above query fetches data from all agents and then determine top 50. If you want to know top 50 agents from specific set of agents, you can use agent_name or domain_name in where clause, which I commented in the about SQL query.

The above query example just queries last 30 seconds of data and determine the top 50. That means the query results are true only for the live data at the moment. If you want to query for those agents which are not live available at the time of running query, you can modify the time range accordingly. If you give wider range of time, the query takes longer.

Care should be taken while setting WHERE clause parameters.
 
Additional Information:
https://docops.ca.com/ca-apm/10-7/en/integrating/apmsql-cookbook