Using APMSQL REST API

Document ID : KB000091849
Last Modified Date : 18/04/2018
Show Technical Document Details
Introduction:
Use the Public APMSQL REST API  to extract raw metric data from APM and load this data to the APMSQL Server.  Like other APM REST APIs, the APMSQL 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
Environment:
This new API is public starting from 10.7.1 but can be used starting from 10.5
Instructions:
Follow these steps:
  1. Log into Team Center and click Security.
  2. Click Generate New Token.
    A dialog window appears.
  3. Fill out 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
Important: 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.
  1. 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.
 

APMSQL REST API Resources

The APMSQL REST API contains the following resources:
  • /apm/appmap/apmData/schema
    Describes all known virtual tables that the interface can return.
  • /apm/appmap/apmData/query
    Serves as the actual query interface. You can pass SQL queries using the capabilities that the schema table returned.
 

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 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;" }'

·        Example 4: Get all metrics

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" }'

·        Example 5: 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%'\" }"

·        Example 6: 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 \" }"

·        Example 7: Get all metric data

Note: This query returns an very large JSON file. Run with caution.

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 \" }"

·        Example 8: Get all metric data in the last hour

Note: 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 9: 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 \" }"

·        Example 10: 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\" }"

·        Example 11: Get Maximum 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\" }"
Additional Information:

APMSQL REST API Configuration

Configure the APMSQL 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 APSQL 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 APSQL 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 APMSQL 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 APMSQL REST API have the Enterprise Manager|Data Store|SQL API prefix in their names. The following table lists the available supportability metrics:
Supportability Metric NameDescription
Average Response Time (ms)Average time to process incoming query
Bytes Sent Per IntervalNumber of bytes sent as a result in an interval
Responses Per IntervalSuccessful queries in an interval 
Concurrent InvocationsNumber of parallel connections to a query endpoint
Clamped Connections Per IntervalNumber of rejected connections due to the clamp limit
Errors Per Interval Number of failed queries in an interval 

Note: The APMSQL REST API is simplified and does not provide all SQL capabilities such as JOINs and subselects. Use an APMSQL client for advanced query capabilities.