How to use SQL REST API - examples
Article ID: 182979
Updated On:
Products
CA Application Performance Management Agent (APM / Wily / Introscope)
CA Application Performance Management (APM / Wily / Introscope)
INTROSCOPE
DX Application Performance Management
Issue/Introduction
The following is a list of examples, illustrating how to use the SQLREST API:
1-Gather metrics names for specific agent
2-Gather data points for the PAST hour for specific MQ Agent and specific metrics
3-Gather data points for the past 15 days
4-Gather data points for the past 24 hours
5-Gather data points for specific metric at a specific time and greater than
1-Gather metrics names for specific agent
2-Gather data points for the PAST hour for specific MQ Agent and specific metrics
3-Gather data points for the past 15 days
4-Gather data points for the past 24 hours
5-Gather data points for specific metric at a specific time and greater than
Environment
APM 10.7.x
Resolution
IMPORTANT NOTES:
1. As per documentation: "Use the public SQL REST API to extract raw metric data from APM, and integrate this data with custom tools."
2. To retrieve “data points” use the “metric_data” table, to retrieve “metrics names” use the “metrics” table
For more details on the schema refer to:
https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/application-performance-management/10-7/api-reference/apm-rest-api/sql-rest-api.html#concept.dita_5545b3ca3d7040d88d82df240b68bc0d30ef1fac_APMSQLRESTAPIResources
Step #1 : Generate a Token:
Login to ATC, Security, generate a new token
Select Public API, Never expires
TEST#1 - Gather metrics names for specific agent
In Webview
Command:
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metrics where agent_process LIKE '%MQ%'\" }"
Output:
{"columns":[{"name":"source_name","type":"string"},{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"domain_name","type":"string"},{"name":"metric_path","type":"string"},{"name":"metric_attribute","type":"string"},{"name":"attribute_type","type":"long"},{"name":"first_seen","type":"timestamp"},{"name":"last_seen","type":"timestamp"}]
,"rows":[["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Launch Time","Launch Time",<launch_time>]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","GC Heap:Bytes In Use","Bytes In Use",258,1576366275000,1582879680000]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","GC Heap:Bytes Total","Bytes Total",258,1576366275000,1582879680000]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Host:Wall Clock Time","Wall Clock Time",<clock_time>]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers:Aggregated Agent-MQ Connection Status","Aggregated Agent-MQ Connection Status",258,1576366335000,1582879680000]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>:Aggregated Agent-MQ Connection Status","Aggregated Agent-MQ Connection Status",258,1576366305000,1582879680000]
,["<host>@<prot>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","IBM Integration Node |<host>|example:Component Runstate Value","Component Runstate Value",258,1576366275000,1582879680000]
…
TEST#2 - Gather data points for the PAST hour for specific MQ Agent and specific metrics
Commands:
ONE_HOUR_AGO=`echo $(date "+%s")*1000 " - 60*60*1000" | bc `
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts >= ${ONE_HOUR_AGO}\" }"
Output:
{"columns":[{"name":"source_name","type":"string"},{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"domain_name","type":"string"},{"name":"metric_path","type":"string"},{"name":"metric_attribute","type":"string"},{"name":"attribute_type","type":"long"},{"name":"frequency","type":"long"},{"name":"ts","type":"timestamp"},{"name":"min_value","type":"long"},{"name":"max_value","type":"long"},{"name":"value_count","type":"long"},{"name":"agg_value","type":"long"}]
,"rows":[["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846290000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846305000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846320000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846335000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message …
…
NOTE :
In the above example, the query returns all columns names at the beginning. Below an example of the columns names and corresponding values for “metric_attribute” table:
{"columns":[
{"name":"source_name","type":"string"},
{"name":"agent_host","type":"string"},
{"name":"agent_process","type":"string"},
{"name":"agent_name","type":"string"},
{"name":"domain_name","type":"string"},
{"name":"metric_path","type":"string"},
{"name":"metric_attribute","type":"string"},
{"name":"attribute_type","type":"long"},
{"name":"frequency","type":"long"},
{"name":"ts","type":"timestamp"},
{"name":"min_value","type":"long"},
{"name":"max_value","type":"long"},
{"name":"value_count","type":"long"},
{"name":"agg_value","type":"long"}]
-----------
,[
"<host>@<port>",
"<host>",
"WebSphere MQ and Message Broker",
"WebSphere MQ and Message Broker Agent",
"SuperDomain",
"Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value"
,"Agent-MQ Connection Status Value"
,258,
15000,
1582849290000,
1,
1,
0,
1]
For more details of the tables and columns see schema details:
https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/application-performance-management/10-7/api-reference/apm-rest-api/sql-rest-api.html#concept.dita_5545b3ca3d7040d88d82df240b68bc0d30ef1fac_APMSQLRESTAPIResources
TEST#3: Gather data points for the past 15 days
Commands:
D15_AGO=`echo $(date "+%s")*1000 " - 15*24*60*60*1000" | bc `
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts >= ${D15_AGO}\" }"
TEST#4: Gather data points for the past 24 hours
Commands:
T24_HOUR_AGO=`echo $(date "+%s")*1000 " - 24*60*60*1000" | bc `
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts >= ${T24_HOUR_AGO}\" }"
TEST#5 - Gather data points for specific metric at a specific time and greater than
Use a timestamp conversion tool to obtain the milliseconds
https://www.epochconverter.com/
Commands:
curl -Lk -H "Authorization: Bearer <token>" -H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query -d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts>=1582878247000\" }"
Output:
{"columns":[{"name":"source_name","type":"string"},{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"domain_name","type":"string"},{"name":"metric_path","type":"string"},{"name":"metric_attribute","type":"string"},{"name":"attribute_type","type":"long"},{"name":"frequency","type":"long"},{"name":"ts","type":"timestamp"},{"name":"min_value","type":"long"},{"name":"max_value","type":"long"},{"name":"value_count","type":"long"},{"name":"agg_value","type":"long"}]
,"rows":[["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878255000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878270000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878285000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ
...
1. As per documentation: "Use the public SQL REST API to extract raw metric data from APM, and integrate this data with custom tools."
2. To retrieve “data points” use the “metric_data” table, to retrieve “metrics names” use the “metrics” table
For more details on the schema refer to:
https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/application-performance-management/10-7/api-reference/apm-rest-api/sql-rest-api.html#concept.dita_5545b3ca3d7040d88d82df240b68bc0d30ef1fac_APMSQLRESTAPIResources
Step #1 : Generate a Token:
Login to ATC, Security, generate a new token
Select Public API, Never expires
TEST#1 - Gather metrics names for specific agent
In Webview
Command:
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metrics where agent_process LIKE '%MQ%'\" }"
Output:
{"columns":[{"name":"source_name","type":"string"},{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"domain_name","type":"string"},{"name":"metric_path","type":"string"},{"name":"metric_attribute","type":"string"},{"name":"attribute_type","type":"long"},{"name":"first_seen","type":"timestamp"},{"name":"last_seen","type":"timestamp"}]
,"rows":[["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Launch Time","Launch Time",<launch_time>]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","GC Heap:Bytes In Use","Bytes In Use",258,1576366275000,1582879680000]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","GC Heap:Bytes Total","Bytes Total",258,1576366275000,1582879680000]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Host:Wall Clock Time","Wall Clock Time",<clock_time>]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers:Aggregated Agent-MQ Connection Status","Aggregated Agent-MQ Connection Status",258,1576366335000,1582879680000]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>:Aggregated Agent-MQ Connection Status","Aggregated Agent-MQ Connection Status",258,1576366305000,1582879680000]
,["<host>@<prot>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","IBM Integration Node |<host>|example:Component Runstate Value","Component Runstate Value",258,1576366275000,1582879680000]
…
TEST#2 - Gather data points for the PAST hour for specific MQ Agent and specific metrics
Commands:
ONE_HOUR_AGO=`echo $(date "+%s")*1000 " - 60*60*1000" | bc `
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts >= ${ONE_HOUR_AGO}\" }"
Output:
{"columns":[{"name":"source_name","type":"string"},{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"domain_name","type":"string"},{"name":"metric_path","type":"string"},{"name":"metric_attribute","type":"string"},{"name":"attribute_type","type":"long"},{"name":"frequency","type":"long"},{"name":"ts","type":"timestamp"},{"name":"min_value","type":"long"},{"name":"max_value","type":"long"},{"name":"value_count","type":"long"},{"name":"agg_value","type":"long"}]
,"rows":[["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846290000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846305000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846320000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846335000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message …
…
NOTE :
In the above example, the query returns all columns names at the beginning. Below an example of the columns names and corresponding values for “metric_attribute” table:
{"columns":[
{"name":"source_name","type":"string"},
{"name":"agent_host","type":"string"},
{"name":"agent_process","type":"string"},
{"name":"agent_name","type":"string"},
{"name":"domain_name","type":"string"},
{"name":"metric_path","type":"string"},
{"name":"metric_attribute","type":"string"},
{"name":"attribute_type","type":"long"},
{"name":"frequency","type":"long"},
{"name":"ts","type":"timestamp"},
{"name":"min_value","type":"long"},
{"name":"max_value","type":"long"},
{"name":"value_count","type":"long"},
{"name":"agg_value","type":"long"}]
-----------
,[
"<host>@<port>",
"<host>",
"WebSphere MQ and Message Broker",
"WebSphere MQ and Message Broker Agent",
"SuperDomain",
"Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value"
,"Agent-MQ Connection Status Value"
,258,
15000,
1582849290000,
1,
1,
0,
1]
For more details of the tables and columns see schema details:
https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/application-performance-management/10-7/api-reference/apm-rest-api/sql-rest-api.html#concept.dita_5545b3ca3d7040d88d82df240b68bc0d30ef1fac_APMSQLRESTAPIResources
TEST#3: Gather data points for the past 15 days
Commands:
D15_AGO=`echo $(date "+%s")*1000 " - 15*24*60*60*1000" | bc `
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts >= ${D15_AGO}\" }"
TEST#4: Gather data points for the past 24 hours
Commands:
T24_HOUR_AGO=`echo $(date "+%s")*1000 " - 24*60*60*1000" | bc `
curl -Lk -H "Authorization: Bearer <token>" \
-H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query \
-d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts >= ${T24_HOUR_AGO}\" }"
TEST#5 - Gather data points for specific metric at a specific time and greater than
Use a timestamp conversion tool to obtain the milliseconds
https://www.epochconverter.com/
Commands:
curl -Lk -H "Authorization: Bearer <token>" -H "Accept: application/json" -H "Content-Type: application/json" http://<host>:<port>/apm/appmap/apmData/query -d "{ \"query\" : \"select * from metric_data where agent_name LIKE '%WebSphere MQ and Message Broker Agent%' and metric_attribute LIKE 'Agent-MQ Connection Status Value' and ts>=1582878247000\" }"
Output:
{"columns":[{"name":"source_name","type":"string"},{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"domain_name","type":"string"},{"name":"metric_path","type":"string"},{"name":"metric_attribute","type":"string"},{"name":"attribute_type","type":"long"},{"name":"frequency","type":"long"},{"name":"ts","type":"timestamp"},{"name":"min_value","type":"long"},{"name":"max_value","type":"long"},{"name":"value_count","type":"long"},{"name":"agg_value","type":"long"}]
,"rows":[["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878255000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878270000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878285000,1,1,0,1]
,["<host>@<port>","<host>","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|<host>|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ
...
Feedback
Yes
No
Powered by
