SQL REST API

Use the public SQL REST API to extract raw metric data from APM, and integrate this data with custom tools. Like other APM REST APIs, the SQL REST API interface uses token-based authentication. This REST API runs on the following Enterprise Manager (EM) modes:
apmdevops106
Use the public SQL REST API to extract raw metric data from APM, and integrate this data with custom tools. Like other APM REST APIs, the SQL REST API interface uses token-based authentication. This REST API runs on the following Enterprise Manager (EM) modes:
  • Standalone
  • Collector
  • Manager of Managers (MOM)
  • Enterprise Team Center
  • The SQL REST API feature was introduced in the 10.7 Service Pack 1 (SP1).
  • This feature does not provide all SQL capabilities such as JOINs and subselects. Use an APMSQL client for advanced query capabilities.
Connect to the SQL REST API
Follow these steps:
  1. Log into Team Center and click
    Security
    .
  2. Click
    Generate New Token
    .
    A dialog window appears.
  3. Enter the
    Label
    (name) and select
    Public API
    for the
    Type
    .
  4. Set the expiration date or select
    Never Expires
    .
  5. Click
    Generate Token
    .
    The system generates a new token
    For security reasons, you only see a token once. Store the token in a safe place before closing this dialog window. Do not disclose the token to unauthorized parties.
    The token now appears among the other tokens in the Security tab. You can Set Expiration of a token, Invalidate a token, or, Rename a token. The tokens are permanent and never disappear from the list in the Security tab. An invalidated token shows who and when invalidated the token.
  6. Test the connection with a sample query, for example:
    URL http://<EM Host>:8081/apm/appmap/apmData/schema Verb GET Header Accept: application/json Authorization: Bearer <Security Token>
You connected to the APMSQL REST API.
SQL REST API Configuration
Configure the SQL REST API settings in the
IntroscopeEnterpriseManager.properties
file.
Follow these steps:
  1. Go to
    <EM_Home>\config
    directory and open the
    IntroscopeEnterpriseManager.properties
    file.
  2. Edit the following properties as needed:
    • introscope.enterprisemanager.restapi.sql.rateLimit
      Limits the amount of data that is transferred from the SQL REST API. The default value allows for an unlimited transfer. Set the value to a positive number to limit the data transfer.
      Default:
      -1 (KB/s)
    • introscope.enterprisemanager.restapi.sql.connectionLimit
      Limits the number of parallel connections to the SQL REST API. Set the value to a positive number to limit parallel connections in a transfer. Otherwise, the number of parallel connections is unlimited.
      Default:
      5 (connections)
    • introscope.enterprisemanager.restapi.sql.groupCountLimit
      Limits the number of groups in the SQL REST API memory. Set the value to a positive number to limit the number of groups. Otherwise, the number of groups that are stored in the memory is unlimited.
      Default:
      100000 (groups)
    • introscope.enterprisemanager.restapi.token.lockTime
      Defines the period in seconds, after which the IP address is blocked due to too many failed logins.
      Default:
      5 (minutes)
    • introscope.enterprisemanager.restapi.token.maxFailsPerIp
      Defines the number of failed attempts from the IP address before the IP address is blocked.
      Default:
      5 (attempts)
Supportability Metrics
Supportability metrics for the SQL REST API have the
Enterprise Manager|Data Store|SQL API
prefix in their names. The following table lists the available supportability metrics:
Supportability Metric Name
Description
Average Response Time (ms)
Average time to process incoming query
Bytes Sent Per Interval
Number of bytes sent as a result in an interval
Responses Per Interval
Successful queries in an interval
Concurrent Invocations
Number of parallel connections to a query endpoint
Clamped Connections Per Interval
Number of rejected connections due to the clamp limit
Errors Per Interval
Number of failed queries in an interval
APMSQL REST API Resources
The SQL REST API contains the following resources:
  • /apm/appmap/apmData/schema
  • /apm/appmap/apmData/query
    Serves as the actual query interface. You can pass SQL queries using the capabilities that the schema table returned.
You can define queries using the count, minimum, maximum and average functions. The following aggregate function is only supported on agg_value column in the metric_data table: apm_average. The aggregate function results in the sum or weighted average based on the metrics queried.
Query Examples
Use the following examples to query the API:
  • Example 1:
    Get count of metrics grouped by agent host
    This example uses a POST request on the
    /apm/appmap/apmData/query
    resource.
    URL http://<EM Host>:8081/apm/appmap/apmData/query Verb POST Header Accept: application/json Content-Type: application/json Authorization: Bearer <Security Token>Data { "query" : "select agent_host, agent_process, agent_name, count(metric_path) from metrics where agent_name Like '' group by agent_host, agent_process, agent_name"}
  • Example 2: Get schema
    This example uses a GET request on the
    /apm/appmap/apmData/schema
    resource.
    { "tables": [ { "name": "metric_data", "columns": [ { "name": "source_name", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "agent_host", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "agent_process", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "agent_name", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "domain_name", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "metric_path", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "metric_attribute", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "attribute_type", "type": "long", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "frequency", "type": "long", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "ts", "type": "timestamp", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "min_value", "type": "long", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "max_value", "type": "long", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "value_count", "type": "long", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "agg_value", "type": "long", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] } ] }, { "name": "metrics", "columns": [ { "name": "source_name", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "agent_host", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "agent_process", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "agent_name", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "domain_name", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "metric_path", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "metric_attribute", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "attribute_type", "type": "long", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "first_seen", "type": "timestamp", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] }, { "name": "last_seen", "type": "timestamp", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN" ] } ] }, { "name": "sources", "columns": [ { "name": "source_name", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "status", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] }, { "name": "type", "type": "string", "whereCapabilities": [ "=", "<", ">", "<=", ">=", "<>", "!=", "BETWEEN", "LIKE", "LIKE_REGEX" ] } ] } ]}
  • ample 3: Get metric data with a where clause
    This example uses a POST request on the
    /apm/appmap/apmData/query
    resource.
    {
    "query" : "select <Columns> from metric_data <Where Clause>"
    }
    Result:
    { "columns" : [ { "name" : "metric", "type" : "string" }, { "name" : "AVG(value)", "type" : "double" } ], "rows" : [ [ "host|process|agent|Average Response Time(ms)", 1025.69 ], [ "host|process|agent|CPU:Utilization %(process)", 12.25 ] ]}
cURL Query Examples
Use the following examples to query the API in cURL:
  • Example 1: Get schema
    curl -Lk -H "Authorization: Bearer $TOKEN" -H "Accept: application/json" -H "Content-Type: application/json" \
    http://$EM_HOST:8081/apm/appmap/apmData/schema
  • Example 2: Get a human readable schema without where capabilities
    curl -Lk -H "Authorization: Bearer $TOKEN" -H "Accept: application/json" -H "Content-Type: application/json" \
    http://$EM_HOST:8081/apm/appmap/apmData/schema | sed 's/,"whereCapabilities":[[][^]] *[]] //g' | python -mjson.tool
  • Example 3: Get all sources
    curl -Lk -H "Authorization: Bearer $TOKEN" -H "Accept: application/json" -H "Content-Type: application/json" \
    http://$EM_HOST:8081/apm/appmap/apmData/query -d '{ "query" : "select * from sources;" }'
    Result:
    {"columns":[{"name":"source_name","type":"string"},{"name":"status","type":"string"},{"name":"type","type":"string"}]
    ,"rows":[["<>:8081","connected","agc"]
    ,["EM Host [email protected]","connected","collector"]
    ,["EM Host 2:8081","connected","standalone"]
    ,["EM Host 3:8081","connected","mom"]
    ,["EM Host 4:8081","connected","standalone"]
    ,["EM Host [email protected]","connected","collector"]
    ,["EM Host [email protected]","connected","collector"]
    ]}
  • Example 4: Get all metrics for agents with specific characters in the agent_process column
    curl -Lk -H "Authorization: Bearer $TOKEN" -H "Accept: application/json" -H "Content-Type: application/json" \
    http://$EM_HOST:8081/apm/appmap/apmData/query -d "{ \"query\" : \"select * from metrics where agent_process LIKE '%Nowhere%'\" }"
    Result:
    {"columns":[{"name":"source_name","type":"string"},{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"domain_name","typ
    e":"string"},{"name":"metric_path","type":"string"},{"name":"metric_attribute","type":"string"},{"name":"attribute_type","type":"long"},{"name":"first_seen","type":"timestamp"},{"name":"last_see
    n","type":"timestamp"}]
    ,"rows":[["Collector Host [email protected]","Agent Host 1","Nowhere Bank","Engine","SuperDomain","Launch Time","Launch Time",2066,1521511260000,1521565710000]
    ,["Collector Host 2:8081","Agent Host 2","Nowhere Bank","Mediator","SuperDomain","Launch Time","Launch Time",2066,1521511515000,1521565710000]
    ,["Collector Host [email protected]","Agent Host 3","Nowhere Bank","Mediator","SuperDomain","Launch Time","Launch Time",2066,1521511560000,1521565710000]
    ,["Collector Host [email protected]","Agent Host 4","Nowhere Bank","Engine","SuperDomain","CPU:Processor Count","Processor Count",17,1521511260000,1521565710000]
    ,["Collector Host 5:8081","Agent Host 5","Nowhere Bank","Mediator","SuperDomain","CPU:Processor Count","Processor Count",17,1521511515000,1521565710000]
    ,["Collector Host [email protected]","Agent Host 6","Nowhere Bank","Portal","SuperDomain","Launch Time","Launch Time",2066,1521511500000,1521565710000]
    ,["Collector Host [email protected]","Agent Host 7","Nowhere Bank","Engine","SuperDomain","CPU:Utilization % (process)","Utilization % (process)",4097,1521511260000,1521565710000]
    ,….
  • Example 5: Get the count of metrics grouped by agents
    curl -Lk -H "Authorization: Bearer $TOKEN" -H "Accept: application/json" -H "Content-Type: application/json" \
    http://$EM_HOST:8081/apm/appmap/apmData/query \
    -d "{ \"query\" : \"select agent_host, agent_process, agent_name, count(metric_path) from metrics group by agent_host, agent_process, agent_name \" }"
    Result:
    {"columns":[{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"agent_name","type":"string"},{"name":"count(metric_path)","type":"long"}]
    ,"rows":[["usilca31","Cross-Enterprise APM Process","Cross-Enterprise APM Agent HEY",4768]
    ,["EM Host 1","CTG Client 2","CICSTestDriver",119]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Metric Agent (Virtual) (Custom Host [email protected])",1008]
    ,["EM Host 2","Nowhere Bank","Portal",302]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Metric Agent (Virtual)",7234]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Metric Agent (Virtual) (Custom Host [email protected])",1131]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Business Application Agent (Virtual) (Custom Host [email protected])",76]
    ,["EM Host 3","Infrastructure","Agent",231]
    ,["EM Host 4","Collector","Agent",80]
    ,["EM Host 5","Tomcat","Tomcat Agent",360]
    ,["EM Host 6","Collector","Agent",344]
    ,["EM Host 7","Nowhere Bank","Mediator",269]
    ,["EM Host 8","Nowhere Bank","Portal",302]
    ,["EM Host 9","Agent","UnnamedAgent",12]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Metric Agent (Virtual) (Custom Host [email protected])",1186]
    ,["EM Host 10","Nowhere Bank","Engine",283]
    ,["EM Host 11","WebSphere","WebSphere Agent",601]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Business Application Agent (Virtual)",873]
    ,["EM Host 12","Nowhere Bank","Engine",283]
    ,["EM Host 13","Tomcat","Tomcat Agent",806]
    ,["EM Host 14","CTG Client 1","CICSTestDriver",119]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Business Application Agent (Virtual) (Custom Host [email protected])",242]
    ,["EM Host 15","Tomcat-MathApp-BA-PO","Tomcat-MathApp-BA-PO",1489]
    ,["EM Host 16","DxC Agent","Logstash-APM-Plugin",306]
    ,["EM Host 17","Nowhere Bank","Engine",283]
    ,["EM Host 18","Nowhere Bank","Mediator",261]
    ,["EM Host 19","CEM","Default Application",107]
    ,["tradeservice-app","Tomcat","CA APM Demo Agent - Tomcat",763]
    ,["EM Host 20","Nowhere Bank","Portal",302]
    ,["EM Host 21","Nowhere Bank","Mediator",269]
    ,["Custom Metric Host (Virtual)","Custom Metric Process (Virtual)","Custom Business Application Agent (Virtual) (Custom Host [email protected])",76]
    ]}
  • Example 6: Get all metric data in the last hour
    This query returns an very large JSON file. Run with caution.
    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://<EM Host>:8081/apm/appmap/apmData/query \
    -d "{ \"query\" : \"select * from metric_data where ts >= ${ONE_HOUR_AGO}\" }"
  • Example 7: Get the maximum value grouped by metric path from metric data for all Average metrics (ms)
    curl -Lk -H "Authorization: Bearer $TOKEN" -H "Accept: application/json" -H "Content-Type: application/json" \
    http://$EM_HOST:8081/apm/appmap/apmData/query \
    -d "{ \"query\" : \"select metric_path, max(agg_value) from metric_data where metric_attribute LIKE 'Average%(ms)' group by metric_path \" }"
    Result:
    {"columns":[{"name":"metric_path","type":"string"},{"name":"max(agg_value)","type":"long"}]
    ,"rows":[["Business Segment|tas-cz-n148/9091|/brtmtestapp/spa/|#/green|Resources|AJAX Call|Async|tas-cz-n148/9091|/brtmtestapp/sample.txt:Average Callback Execution Time (ms)",4]
    ,["Backends|WebService at http_//localhost_8080:Average Response Time (ms)",4856]
    ,["By Frontend|CICSTestDriver_RunUOW|Backend Calls|System localhost on port 2006:Average Response Time (ms)",0]
    ,["Frontends|Apps|TradeService|URLs|/TradeService/PlaceOrder|Called Backends|System localhost on port 3456:Average Response Time (ms)",105]
    ,["Backends|WebService at PipeOrganWebService_2:Average Response Time (ms)",128]
    ,["Frontends|Apps|ReportingService|URLs|Default|Called Backends|WebServices:Average Response Time (ms)",177]
    ,["Enterprise Manager|Internal|Messaging|PostOffices|Server.WatchedAgentPO|Messages|com.wily.isengard.messageprimitives.service.MessageServiceCallMessage:Average Process Time (ms)",73]
    ,["Enterprise Manager|Internal|Messaging|PostOffices|Server.main|Messages|com.wily.isengard.messageprimitives.service.MessageServiceCallMessage|com.wily.introscope.spec.server.beans.transactiontrace.ITransactionTraceService:Average Process Time (ms)",40]
    ,["By Business Service|tas-cz-n148/9091|/brtmtestapp/HTTP304.html_AJAXCalls|Browser:Average Response Time (ms)",18]
  • Example 8: Get Average CPU Utilization for Agents
    curl -Lk -H "Authorization: Bearer $TOKEN" -H "Accept: application/json" -H "Content-Type: application/json" http://$EM_HOST:8081/apm/appmap/apmData/query \
    -d "{ \"query\" : \"select agent_host, agent_process, avg(agg_value) from metric_data where metric_path like '%CPU:Utilization%' group by agent_host, agent_process\" }"
    Result:
    {"columns":[{"name":"agent_host","type":"string"},{"name":"agent_process","type":"string"},{"name":"avg(agg_value)","type":"long"}]
    ,"rows":[["Collector Host 1","CTG Client 2",4]
    ,["Collector Host 2","WebSphere",8]
    ,["Collector Host 3","Nowhere Bank",3]
    ,["Collector Host 4","Tomcat",3]
    ,["Collector Host 5","Nowhere Bank",2]
    ,["Collector Host 6","Nowhere Bank",1]
    ,["Collector Host 7","CTG Client 1",7]
    ,["Collector Host 8","Tomcat",24]
    ,["Collector Host 9","Tomcat-MathApp-BA-PO",0]
    ]}
  • Example 9: Get M
    aximum Metric Count for Collectors
    curl -Lk -H "Authorization: Bearer $TOKEN " -H "Accept: application/json" -H "Content-Type: application/json" http://$EM_HOST:8081/apm/appmap/apmData/query \
    -d "{ \"query\" : \"select agent_name, max(agg_value) from metric_data where metric_path like '%Connections:Number of Metrics' group by agent_name\" }"
    Result:
    {"columns":[{"name":"agent_name","type":"string"},{"name":"max(agg_value)","type":"long"}]
    ,"rows":[["Custom Metric Agent (Virtual)",8756]
    ,["Custom Metric Agent (Virtual) (Collector Host [email protected])",1990]
    ,["Custom Metric Agent (Virtual) (Collector Host [email protected])",2117]
    ,["Custom Metric Agent (Virtual) (Collector Host [email protected])",5141]
    ]}