Use the asynchronous custom reports API

Edge Analytics provides a rich set of interactive dashboards, custom report generators, and related capabilities. However, these features are intended to be interactive: you submit either an API or UI request and the request is blocked until the analytics server provides a response.

However, analytics requests can time out if they take too long to complete. If a query request needs to process a large amount of data (for example, 100's of GB), then it might fail because of a time out.

Asynchronous query processing lets you query for very large data sets and retrieve the results at a later time. You might consider using an offline query when you find that your interactive queries time out. Some situations when asynchronous query processing might be a good alternative include:

  • Analyzing and creating reports that span large time intervals.
  • Analyzing data with a variety of grouping dimensions and other constraints that add complexity to the query.
  • Managing queries when you find that data volumes have increased significantly for some users or organizations.

This document describes how to intiate an asynchronous queries by using the API. You can also use the UI, as described in Running a custom report.

Comparing the reports API to the UI

Create and manage custom reports descrbes how to use the Edge UI to create and run custom reports. You can run those reports synchronously or asynchronously.

Most of the concepts for generating custom reports with the UI apply to using the API. That is, when creating custom reports with the API you specify metrics, dimensions, and filters built into Edge, and any custom metrics you created by using the StatisticsCollector policy.

The major differences between the reports generated in the UI and in the API are that reports generated with the API are written to CSV or JSON files instead of to a visual report displayed in the UI.

How to make an asynchronous analytics query

You make asynchronous analytics queries in three steps:

  1. Submit the query.

  2. Get the query status.

  3. Retrieve the query results.

Step 1. Submit the query

You must send a POST request to the /queries management API. This API tells Edge to process your request in the background. If the submission of the query succeeds, the API returns a 201 status and an ID that you will use to refer to the query in later steps.

For example:

curl -X POST -H "Content-Type:application/json"
https://api.enterprise.apigee.com/v1/organizations/myorg/environments/myenv/queries -d @json-query-file
-u orgAdminEmail:password

The body of the request is a JSON description of the query. In the JSON body, specify the metrics, dimensions, and filters that define the report.

Shown below is an example json-query-file file:

{ 
   "metrics":  [
     {
         "name": "message_count",
         "function": "sum",
         "alias": "sum_txn"
    }
        ],
    "dimensions": ["apiproxy"],
    "timeRange": "last24hours",
    "limit": 14400,
    "filter":"(message_count ge 0)"         
}

See About the request body below for a complete description of the request body syntax.

Sample response:

Note that the query ID 9cfc0d85-0f30-46d6-ae6f-318d0cb961bd is included in the response. In addition to the HTTP status 201, the state of enqueued means that the request succeeded.

HTTP/1.1 201 Created

{  
  "self":"/organizations/myorg/environments/myenv/queries/9cfc0d85-0f30-46d6-ae6f-318d0cb961bd",
  "created":"2018-05-10T07:11:10Z",
  "state":"enqueued",
  "error":"false",
}

Step 2. Get the query status

Make a GET call to request the status of the query. You provide the query ID that was returned from the POST call. For example:

curl -X GET -H "Content-Type:application/json"
https://api.enterprise.apigee.com/v1/organizations/myorg/environments/myenv/queries/9cfc0d85-0f30-46d6-ae6f-318d0cb961bd
-u email:password

Sample responses:

If the query is still in progress, you'll get a response like this, where the state is running:

{
    "self": "/organizations/myorg/environments/myenv/queries/1577884c-4f48-4735-9728-5da4b05876ab",
    "state": "running",
    "created": "2018-02-23T14:07:27Z",
    "updated": "2018-02-23T14:07:54Z"
}

After the query has completed successfully, you will see a response like this, where state is set to completed:

{
      "self": "/organizations/myorg/environments/myenv/queries/9cfc0d85-0f30-46d6-ae6f-318d0cb961bd",
      "state": "completed",
      "result": {
        "self": "/organizations/myorg/environments/myenv/queries/9cfc0d85-0f30-46d6-ae6f-318d0cb961bd/result",
        "expires": "2017-05-22T14:56:31Z"
      },
      "resultRows": 1,
      "resultFileSize": "922KB",
      "executionTime": "11 sec",
      "created": "2018-05-10T07:11:10Z",
      "updated": "2018-05-10T07:13:22Z"
}

Step 3. Retrieve the query results

After the query status is completed, you can use the get results API to retrieve the results, where the query ID is once again 9cfc0d85-0f30-46d6-ae6f-318d0cb961bd.

curl -X GET -H "Content-Type:application/json" -O -J https://api.enterprise.apigee.com/v1/organizations/myorg/environments/myenv/queries/9cfc0d85-0f30-46d6-ae6f-318d0cb961bd/result
-u email:password

To retrieve the downloaded file, you need to configure the tool that you use so that it will save a downloaded file to your system. For example:

  • If you use cURL, you can use the -O -J options, as shown above.

  • If you use Postman, you need to select the Save and Download button. In this case, a zip file called response is downloaded.

  • If you use the Chrome browser, the download is accepted automatically.

If the request succeeds, and there is a non-zero result set, the result is downloaded to the client as a zipped JSON file. The name of the downloaded file will be:

OfflineQueryResult-<query-id>.zip

For example:

OfflineQueryResult-9cfc0d85-0f30-46d6-ae6f-318d0cb961bd.zip

The zip file contains a .gz archive file of the JSON results. To access the JSON file, unzip the download file, then use the gzip command to extract the JSON file:

unzip OfflineQueryResult-9cfc0d85-0f30-46d6-ae6f-318d0cb961bd.zip
gzip -d QueryResult-9cfc0d85-0f30-46d6-ae6f-318d0cb961bd-000000000000.json.gz

About the request body

This section describes each of the parameters that you can use in the JSON request body for a query. For details on metrics and dimensions that you can use in your query, see Analytics reference.

{  
   "metrics":[  
      {  
        "name":"metric_name",
        "function":"aggregation_function",
        "alias":"metric_dispaly_name_in_results",
        "operator":"post_processing_operator",
        "value":"post_processing_operand"
      },
   ...
   ],
   "dimensions":[  
      "dimension_name",
      ...
   ],
   "timeRange":"time_range",
   "limit":results_limit,
   "filter":"filter",
   "groupByTimeUnit": "grouping",
   "outputFormat": "format",
   "csvDelimiter": "delimiter"
}
Property Description Required?
metrics

Array of metrics. You can specify one or more metrics for a query where each metric includes. Only the metric name is required:

  • name: (Required) The name of the metric as defined by the table at metrics.
  • function: (Optional) The aggregation function as avg, min, max, or sum.

    Not all metrics support all aggregation functions. The documentation on metrics contains a table that specifies the metric name and the function (avg, min, max,sum) supported by the metric.

  • alias: (Optional) The name of the proroperty containing the metric data in the output. If omitted, it defaults to the metric name combined with the name of the aggregation function.
  • operator: (Optional) An operation to perform on the metric after its value has been calculated. Works with the value property. Supported operations include: + - / % *.
  • value: (Optional) The value applied to the calculated metric by the specified operator.

The operator and value properties define a post-processing operation performed on the metric. For example, if you specify the metric response_processing_latency, the metric returns the average response processing latency with a unit of milliseconds. To convert the units to seconds, set the operator to "/" and the value to ”1000.0“:

"metrics":[  
  {  
    "name":"response_processing_latency",
    "function":"avg",
    "alias":"average_response_time_in_seconds",
    "operator":"/",
    "value":"1000"
  }
]

For more information, see Analytics metrics, dimensions, and filters reference.

No
dimensions Array of dimensions to group the metrics. For more information, see the list of supported dimension. You can specify multiple dimensions. No
timeRange Time range for the query.

You can use the following predefined strings to specify the time range:

  • last60minutes
  • last24hours
  • last7days

Or, you can specify the timeRange as a structure describing start and end timestamps in the ISO format: yyyy-mm-ddThh:mm:ssZ. For example:

"timeRange": {
    "start": "2018-07-29T00:13:00Z",
    "end": "2018-08-01T00:18:00Z"
}
Yes
limit Maximum number of rows that can be returned in the result. No
filter Boolean expression that can be used to filter data. Filter expressions can be combined using AND/OR terms and should be fully parenthesized to avoid ambiguity. See Analytics metrics, dimensions, and filters reference for more information on the fields available to filter on. For more information on the tokens that you use to build filter expressions, see Filter expression syntax. No
groupByTimeUnit Time unit used to group the result set. Valid values include: second, minute, hour, day, week, or month.

If a query includes groupByTimeUnit, then the result is an aggregation based on the specified time unit and the resultant timestamp does not include milliseconds precision. If a query omits groupByTimeUnit, then the resultant timestamp includes milliseconds precision.

No
outputFormat Output format. Valid values include: csv or json. Defaults to json.

Note: Configure the delimiter for CSV output using the csvDelimiter property.

No
csvDelimiter Delimiter used in the CSV file, if outputFormat is set to csv. Defaults to the , (comma) character. Supported delimiter characters include comma (,), pipe (|), and tab (\t). No

Filter expression syntax

This reference section describes the tokens that you can use to build filter expressions in the request body. For example, the following expression uses the "ge" token (greater than or equal to):

"filter":"(message_count ge 0)"
Token Description Examples
in Include in list
(apiproxy in 'ethorapi','weather-api')

(apiproxy in 'ethorapi')

(apiproxy in 'Search','ViewItem')

(response_status_code in 400,401,500,501)

Note: Strings must be in quotes.

not in Exclude from list
(response_status_code not in 400,401,500,501)
eq Equal to (==)
(response_status_code eq 504)

(apiproxy eq 'non-prod')
ne Not equal to (!=)
(response_status_code ne 500)

(apiproxy ne 'non-prod')
gt Greater than (>)
(response_status_code gt 500)
lt Less than (<)
(response_status_code lt 500)
ge Greater than or equal to (>=)
(target_response_code ge 400)
le Less than or equal to (<=)
(target_response_code le 300)
like Returns true if the string pattern matches the supplied pattern.

The example to the right matches as follows:

- any value that has the word 'buy'

- any value ending in 'item'

- any value that starts with 'Prod'

- any value that starts with 4, note response_status_code is numeric

(apiproxy like '%buy%')

(apiproxy like '%item')

(apiproxy like 'Prod%')
not like Returns false if the string pattern matches the supplied pattern.
(apiproxy not like '%buy%')

(apiproxy not like '%item')

(apiproxy not like 'Prod%')
and Lets you use 'and' logic to include more than one filter expression. The filter includes data that meets all the conditions.
(target_response_code gt 399) and (response_status_code ge 400)
or Lets you use 'or' logic to evaluate different possible filter expressions. The filter includes data that meets at least one of the conditions.
(response_size ge 1000) or (response_status_code eq 500)

Constraints and defaults

Following are a list of constraints and defaults for the asynchronous query processing feature.

Constraint Default Description
Active query limit 10 You can have up to 10 active queries for an organization/environment.
Query execution time threshold 6 hours Queries that take longer than 6 hours will be terminated.
Query Time Range See description The maximum allowed time range for a query is 365 days.
Dimensions and metrics limit 25 The maximum number of dimensions and metrics you can specify in the query payload.

About the query results

The following is an example result in JSON format. The output consists of JSON rows separated by a new line delimiter:

{"message_count":"10209","apiproxy":"guest-auth-v3","hour":"2018-08-07 19:26:00 UTC"}
{"message_count":"2462","apiproxy":"carts-v2","hour":"2018-08-06 13:16:00 UTC"}    
…

You can fetch the results from the URL until the expiry of the data in the repository. See Constraints and defaults.

Examples

Example 1: Sum of message counts

Query for the sum of message counts over the last 60 minutes.

Query

curl -X POST -H "Content-Type: application/json" -H "Accept: application/json"
https://api.enterprise.apigee.com/v1/organizations/myorg/environments/test/queries"
-d @last60minutes.json
-u orgAdminEmail:password

Request Body from last60minutes.json

{  
   "metrics":[  
      {  
         "name":"message_count",
         "function":"sum"
      }
   ],
   "dimensions":[  
      "apiproxy"
   ],
   "groupByTimeUnit":"minute",
   "limit":1000,
   "timeRange":"last60minutes"
}

Example 2: Custom time range

Query using a custom time range.

Query

curl -X POST -H "Content-Type: application/json" -H "Accept: application/json"
https://api.enterprise.apigee.com/v1 /organizations/myorg/environments/test/queries"
-d @last60minutes.json
-u orgAdminEmail:password

Request body from last60minutes.json

{  
   "metrics":[  
      {  
         "name":"message_count",
         "function":"sum"
      },
      {  
         "name":"total_response_time",
         "function":"avg",
         "alias":"average_response_time"
      }
   ],
   "dimensions":[  
      "apiproxy"
   ],
   "groupByTimeUnit":"minute",
   "limit":1000,
   "timeRange":{  
      "start":"2018-11-01T11:00:00Z",
      "end":"2018-11-30T11:00:00Z"
   }
}

Example 3: Transactions per minute

Query on the metric for transactions per minute (tpm).

Query

curl -X POST -H "Content-Type: application/json" -H "Accept: application/json"
https://api.enterprise.apigee.com/v1/organizations/myorg/environments/test/queries"
-d @tpm.json
-u orgAdminEmail:password

Request body from tpm.json

{  
   "metrics":[  
      {  
         "name":"tpm"
      }
   ],
   "dimensions":[  
      "apiproxy"
   ],
   "groupByTimeUnit":"minute",
   "limit":1000,
   "timeRange":{  
      "start":"2018-07-01T11:00:00Z",
      "end":"2018-07-30T11:00:00Z"
   }
}

Sample result

Excerpt from the results file:

{"tpm":149995.0,"apiproxy":"proxy_1","minute":"2018-07-06 12:16:00 UTC"}
{"tpm":149998.0,"apiproxy":"proxy_1","minute":"2018-07-09 15:12:00 UTC"}
{"tpm":3.0,"apiproxy":"proxy_2","minute":"2018-07-11 16:18:00 UTC"}
{"tpm":148916.0,"apiproxy":"proxy_1","minute":"2018-07-15 17:14:00 UTC"}
{"tpm":150002.0,"apiproxy":"proxy_1","minute":"2018-07-18 18:11:00 UTC"}
...

Example 4: Using a filter expression

Query with a filter expression that uses a boolean operator.

Query

curl -X POST -H "Content-Type:application/json"
https://api.enterprise.apigee.com/v1/organizations/myorg/environments/test/queries"
-d @filterCombo.json
-u orgAdminEmail:password

Request body from filterCombo.json

{  
   "metrics":[  
      {  
         "name":"message_count",
         "function":"sum"
      },
      {  
         "name":"total_response_time",
         "function":"avg",
         "alias":"average_response_time"
      }
   ],
   "filter":"(apiproxy ne \u0027proxy_1\u0027) and (apiproxy ne \u0027proxy_2\u0027)",
   "dimensions":[  
      "apiproxy"
   ],
   "groupByTimeUnit":"minute",
   "limit":1000,
   "timeRange":{  
      "start":"2018-11-01T11:00:00Z",
      "end":"2018-11-30T11:00:00Z"
   }
}

Example 5: Passing expression in the metrics parameter

Query with an expression that is passed in as part of the metrics parameter. You can use only simple one-operator expressions.

Query

curl -X POST -H "Content-Type:application/json"
https://api.enterprise.apigee.com/v1/organizations/myorg/environments/test/queries" 
-d @metricsExpression.json
-u orgAdminEmail:password

Request body from metricsExpression.json

{  
   "metrics":[  
      {  
         "name":"message_count",
         "function":"sum",
         "operator":"/",
         "value":"7"
      }
   ],
   "dimensions":[  
      "apiproxy"
   ],
   "groupByTimeUnit":"minute",
   "limit":10,
   "timeRange":"last60minutes"
}

How to make an asynchronous monetization report query

You can capture all successful monetization transactions within a given time range for a specific set of criteria using the steps described in this section.

As with asynchronous analytics queries, you make asynchronous monetization report queries in three steps: (1) submit the query, (2) get the query status, and (3) retrieve the query results.

Step 1, submitting the query, is described below.

Steps 2 and 3 are exactly the same as for asynchronous analytics queries. For more information, see How to make an asynchronous analytics query.

To submit a query for an asynchronous monetization report, issue a POST request to /mint/organizations/org_id/async-reports.

Optionally, you can specify the environment by passing the environment query parameter. If not specified, the query parameter defaults to prod. For example:

/mint/organizations/org_id/async-reports?environment=prod

In the request body, specify the following search criteria.

Name Description Default Required?
appCriteria ID and organization for a specific application to be included in the report. If this property is not specified, all applications are included in the report. N/A No
billingMonth Billing month for the report, such as JULY. N/A Yes
billingYear Billing year for the report, such as 2015. N/A Yes
currencyOption Currency for the report. Valid values include:
  • LOCAL - Each line of the report is displayed using the applicable rate plan. This means that there may be multiple currencies in one report if the developers have plans that use different currencies.
  • EUR - Local currency transactions are converted and displayed in Euros.
  • GPB - Local currency transactions are converted and displayed in United Kingdom pounds.
  • USD - Local currency transactions are converted and displayed in United States dollars.

If you select EUR, GBP or USD, the report displays all transactions using that single currency, based on the exchange rate in effect on the date of the transaction.

N/A No
devCriteria

Developer ID or email address, and organization name for a specific developer to be included in the report. If this property is not specified, all developers are included in the report.

For example:

"devCriteria":[{
    "id":"RtHAeZ6LtkSbEH56",
    "orgId":"my_org"}
]
N/A No
fromDate Starting date of the report in UTC. N/A Yes
monetizationPakageIds ID of one or more API packages to include in the report. If this property is not specified, all API packages are included in the report. N/A No
productIds ID of one or more API products to include in the report. If this property is not specified, all API products are included in the report. N/A No
ratePlanLevels

Type of rate plan to be included in the report. Valid values include:

  • DEVELOPER - Developer rate plan.
  • STANDARD - Standard rate plan.

If this property is not specified, both developer-specific and standard rate plans are included in the report.

N/A No
toDate Ending date of the report in UTC. N/A Yes

For example, the following request generates an asynchronous monetization report for the month of June 2017 for the specified API product and developer ID. Report fromDate and toDate dates and times are in UTC/GMT and can include times.

curl -H "Content-Type:application/json" -X POST -d \
'{
      "fromDate":"2017-06-01 00:00:00",
      "toDate":"2017-06-30 00:00:00",    
     "productIds": [
        "a_product"
    ],
    "devCriteria": [{
        "id": "AbstTzpnZZMEDwjc",
        "orgId": "myorg"
    }]

 }' \
"https://api.enterprise.apigee.com/v1/mint/organizations/myorg/async-reports?environment=prod" \
-u orgAdminEmail:password