Use the asynchronous custom reports API

You're viewing Apigee Edge documentation.
Go to the Apigee X documentation. info

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 initiate 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 (newline delimited) files instead of to a visual report displayed in the UI.

Limits in Apigee hybrid

Apigee hybrid enforces a 30 MB size limit on the result data set.

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 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 (newline delimited) 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"
}

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

"timeRange": {
    "start": "2018-07-29T00:13:00Z",
    "end": "2018-08-01T00:18:00Z"
}

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)"

(apiproxy in 'ethorapi','weather-api')

(apiproxy in 'ethorapi')

(apiproxy in 'Search','ViewItem')

(response_status_code in 400,401,500,501)

(response_status_code notin 400,401,500,501)

(response_status_code eq 504)

(apiproxy eq 'non-prod')

(response_status_code ne 500)

(apiproxy ne 'non-prod')

(response_status_code gt 500)

(response_status_code lt 500)

(target_response_code ge 400)

(target_response_code le 300)

(apiproxy like '%buy%')

(apiproxy like '%item')

(apiproxy like 'Prod%')

(apiproxy not like '%buy%')

(apiproxy not like '%item')

(apiproxy not like 'Prod%')

(target_response_code gt 399) and (response_status_code ge 400)

(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.

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.

"devCriteria":[{
    "id":"RtHAeZ6LtkSbEH56",
    "orgId":"my_org"}
]

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