How to use SQL REST API - examples

book

Article ID: 182979

calendar_today

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

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
In this example: 1f187630-3906-4038-b470-39e217a58b68



TEST#1 - Gather metrics names for specific agent

In Webview



Command:

curl -Lk -H "Authorization: Bearer 1f187630-3906-4038-b470-39e217a58b68" \
-H "Accept: application/json" -H "Content-Type: application/json" http://muntest000478.bpc.broadcom.net:7081/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":[["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Launch Time","Launch Time",2066,1576366275000,1582879680000]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","GC Heap:Bytes In Use","Bytes In Use",258,1576366275000,1582879680000]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","GC Heap:Bytes Total","Bytes Total",258,1576366275000,1582879680000]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Host:Wall Clock Time","Wall Clock Time",2050,1576366275000,1582879680000]
,["[email protected]","muntest000477","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]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d:Aggregated Agent-MQ Connection Status","Aggregated Agent-MQ Connection Status",258,1576366305000,1582879680000]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","IBM Integration Node |muntest000477|TESTNODE_Administrator: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 1f187630-3906-4038-b470-39e217a58b68" \
-H "Accept: application/json" -H "Content-Type: application/json" http://muntest000478.bpc.broadcom.net:7081/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":[["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846290000,1,1,0,1]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846305000,1,1,0,1]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846320000,1,1,0,1]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582846335000,1,1,0,1]
,["[email protected]","muntest000477","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"}]

-----------

,[
"[email protected]",
"muntest000477",
"WebSphere MQ and Message Broker",
"WebSphere MQ and Message Broker Agent",
"SuperDomain",
"Queue Managers|apmw2k8r2d|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 1f187630-3906-4038-b470-39e217a58b68" \
-H "Accept: application/json" -H "Content-Type: application/json" http://muntest000478.bpc.broadcom.net:7081/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 0dacd07b-23f9-4fe2-8c2c-e3fc950ed9a0" \
-H "Accept: application/json" -H "Content-Type: application/json" http://muntest000478.bpc.broadcom.ne7081/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 1f187630-3906-4038-b470-39e217a58b68" -H "Accept: application/json" -H "Content-Type: application/json" http://muntest000478.bpc.broadcom.net:7081/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":[["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878255000,1,1,0,1]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878270000,1,1,0,1]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ Connection Status Value",258,15000,1582878285000,1,1,0,1]
,["[email protected]","muntest000477","WebSphere MQ and Message Broker","WebSphere MQ and Message Broker Agent","SuperDomain","Queue Managers|apmw2k8r2d|QM1|Last Check:Agent-MQ Connection Status Value","Agent-MQ 
...

Additional Information

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

Attachments