You're viewing **Apigee Edge** documentation. [Go to the **Apigee X** documentation](http://cloud.google.com/apigee/docs/api-platform/analytics/asynch-reports-api). info [Edge Analytics](https://docs.apigee.com/analytics-services/content/analytics-services-overview) 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](https://docs.apigee.com/api-platform/analytics/create-custom-reports#run). > [!NOTE] > **Note:** Free and trial accounts cannot create asynchronous custom reports. For more information, see [Edge pricing plans](https://apigee.com/api-management/#/pricing). ## Comparing the reports API to the UI [Create and manage custom reports](https://docs.apigee.com/api-platform/analytics/create-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](https://docs.apigee.com/api-platform/analytics/analytics-reference#metrics), [dimensions](https://docs.apigee.com/api-platform/analytics/analytics-reference#dimensions), and [filters](https://docs.apigee.com/api-platform/analytics/analytics-reference#filters) built into Edge, and any custom metrics you created by using the [StatisticsCollector policy](https://docs.apigee.com/api-platform/reference/policies/statistics-collector-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](https://docs.apigee.com/api-platform/analytics/asynch-reports-api#submit-query) the query. 2. [Get](https://docs.apigee.com/api-platform/analytics/asynch-reports-api#get-query-status) the query status. 3. [Retrieve](https://docs.apigee.com/api-platform/analytics/asynch-reports-api#retrieve-query-results) the query results. ### Step 1. Submit the query You must send a POST request to the [/queries](https://apidocs.apigee.com/docs/analytics/1/routes/organizations/%7Borg_name%7D/environments/%7Benv_name%7D/queries/post) 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. > [!NOTE] > **Note:**You must be in the role of organization administrator to call this API. 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](https://docs.apigee.com/api-platform/analytics/analytics-reference#metrics), [dimensions](https://docs.apigee.com/api-platform/analytics/analytics-reference#dimensions), and [filters](https://docs.apigee.com/api-platform/analytics/analytics-reference#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](https://docs.apigee.com/api-platform/analytics/asynch-reports-api#about-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](https://apidocs.apigee.com/docs/analytics/1/routes/organizations/%7Borg_name%7D/environments/%7Benv_name%7D/queries/%7BqueryId%7D/get) 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](https://apidocs.apigee.com/docs/analytics/1/routes/organizations/%7Borg_name%7D/environments/%7Benv_name%7D/queries/%7BqueryId%7D/result/get) 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-.zip` For example: `OfflineQueryResult-9cfc0d85-0f30-46d6-ae6f-318d0cb961bd.zip` > [!NOTE] > **Note:** A zip file is downloaded because for very large files it takes Edge a long time to combine the results. Thus, for performance reasons, Edge returns a zip file consisting of the result parts. 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](https://docs.apigee.com/analytics-services/reference/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](https://docs.apigee.com/api-platform/analytics/analytics-reference#metrics). - `function`: (Optional) The aggregation function as `avg`, `min`, `max`, or `sum`. Not all metrics support all aggregation functions. The documentation on [metrics](https://docs.apigee.com/api-platform/analytics/analytics-reference#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](https://docs.apigee.com/api-platform/analytics/analytics-reference). | No | | `dimensions` | Array of dimensions to group the metrics. For more information, see the list of supported [dimension](https://docs.apigee.com/api-platform/analytics/analytics-reference#dimensions). 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-dd*T*hh:mm:ss*Z`. 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](https://docs.apigee.com/api-platform/analytics/analytics-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](https://docs.apigee.com/api-platform/analytics/asynch-reports-api#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` corresponding to newline delimited 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. | | `notin` | Exclude from list | ``` (response_status_code notin 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) ``` | > [!NOTE] > **Note:** > > Compound filter expressions of the following form are not supported: > > ``` > (response_size ge 1000 and response_status_code eq 500) or (developer_app eq 'api_prod') > ``` > > Instead, when compounding "and" and/or "or" logic, every subclause must be enclosed within parentheses. For example: > > ``` > ((response_size ge 1000) and (response_status_code eq 500)) or (developer_app eq 'api_prod') > ``` ### Constraints and defaults Following are a list of constraints and defaults for the asynchronous query processing feature. | Constraint | Default | Description | |---|---|---| | Query call limit | See description | You can make up to seven calls per hour to the [/queries](https://apidocs.apigee.com/docs/analytics/1/routes/organizations/%7Borg_name%7D/environments/%7Benv_name%7D/queries/get) management API to initiate an asynchronous report. If you exceed the call quota, the API returns an HTTP 429 response. | | 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](https://docs.apigee.com/api-platform/analytics/asynch-reports-api#constraints-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 > [!NOTE] > **Note:**This section only applies to organizations that have monetization enabled. 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](https://docs.apigee.com/api-platform/analytics/asynch-reports-api#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 ```