Articles in this section

See more

Reporting API

  • Updated
Follow

About reporting API

This API returns the aggregated per-Flow metrics available in the Flows executions dashboard in the Statistics window.

mceclip0.png

Authentication

Before making a call to the built-in API, the user must receive a JWT token from the authentication API.

Step 1. Use any user with the Administrator role to call Etlworks authentication endpoint and receive an access token.

Step 2. Use the access token, received in Step 1, to call Etlworks API endpoints. The access token must be submitted as a header parameter, as in: Authorization:Bearer access-token.

Access tokens in Etlworks are short-lived and self-expiring. An access token gives you access to the APIs for approximately 10 minutes. It is recommended that you refresh the access token before each call to the API.

The API endpoint parameters

  • PATH: /rest/v1/metrics/executions?date=YYYY-MM-DD.

EXAMPLE: https://app.etlworks.com/rest/v1/metrics/executions?offset=0

  • METHOD: GET.
  • HEADER: Authorization:Bearer access-token.
  • PARAMETERS:
    • date=string - date in ISO-8601 format (YYYY-MM-DD) for which records should be returned. Note that records date comparison is done based on configured timezone. For executions at Super Admin level timezone is taken from Settings -> Timezone -> Administration Timezone, for tenant level executions timezone is taken from Users -> Tenants -> {Specific tenant} -> Timezone.
    • offset=number - an offset is a number of days since today to return aggregated per-Flow metrics. 0 means today, 1 means yesterday, etc. If not provided, defaults to 0. date and offset are mutually exclusive. We recommend using date.
    • includeMetrics=boolean - (default: false) if enabled, each execution object will include metrics, file metrics and exception message, if any.
    • tenantId=number - if specified results will be returned for that tenant (can only be used by Super Admin role).
  • REQUEST BODY: none.
  • REQUEST CONTENT TYPE: none.

Response

The response is a JSON document in the following Format:

[
 {
    "flowId": number,
    "name": "string",
    "icon": "string",
    "executions": [
    {
       "flowId": number,
        "auditId": number,
        "status": "string",
        "user": "string",
        "started": number,
        "ended": number,
        "records": number,
        "exception": "string",
        "metrics": "serialized JSON string",
        "fileMetrics": "serialized JSON string"
     },
     ....
   ]
 }
]

Example:

[
 {
    "flowId": 4921,
    "name": "test flow",
    "icon": "/img/di/snowflake/flows/service_snowflake.png",
    "executions": [
    {
      "flowId": 4921,
      "auditId": 173291,
      "status": "success",
      "user": "scheduler",
      "started": 1584896338701,
      "ended": 1584896342166,
      "records": 15
    },
    {
      "flowId": 4921,
      "auditId": 173283,
      "status": "success",
      "user": "joe.doe",
      "started": 1584891258887,
      "ended": 1584891262155,
      "records": 15
    },
    ]
 },
 {
    "flowId": 1234,
    "name": "sql flow",
    "icon": "/img/di/snowflake/flows/sql.png",
    "executions": [
    {
       "flowId": 1234,
       "auditId": 1732923,
       "status": "error",
       "user": "scheduler",
       "started": 1584896338701,
       "ended": 1584896342166,
       "records": 0
    }
    ] 
 }
]

 

Response Codes

  • 200 for success
  • 401 and 403 for not authorized
  • 500 for an internal error

Example of the report 

This report retrieves the aggregated metrics for all Flows executed today and loads them into the Excel spreadsheet.

Step 1. Create an Etlworks API Connection for the reporting API endpoint.

  • Endpoint path: /rest/v1/metrics/executions?offset=0.
  • Method: GET.
  • User:  the username or email of any user with the Administrator role.
  • Password: the password for the user above.

Step 2. Create a JSON Format with all default settings.

Step 3. Create a destination Connection, which for files can be anything from this list:

Step 4. Create Excel XSLX Format with all default settings.

Step 5. Start creating a new Flow by selecting Web Service to file from the gallery. 

mceclip0.png

Step 6. Add new source-to-destination transformation:

  • FROM Connection: HTTP Connection created in step 1.
  • FROM Format: JSON Format created in step 2.
  • FROM: executions.
  • TO Connection: file-based Connection created in step 3.
  • TO Format: Excel Format created in step 4.
  • TO: Excel file name|worksheet, for example, report.xlsx|metrics.

Step 7. Click MAPPING and configure the following mapping:

mceclip1.png

Fields started and ended will need to be converted from milliseconds since 01/01/1970 to MM/dd/yyyy so use the following function (for started, replace on ended for ended):

var val = dataSet.getFieldValue(currentRow, 'started');

value = val != null ? Utils.date2Str(
new java.util.Date(new java.lang.Long(val)) , 'MM/dd/yyyy HH:mm:ss') : null;

Field statistics is a new calculated field that includes a direct link to the Flow dashboard:

'https://your_insance_base_url/#/app/flows/' + {flowid} + '/statistics'

 

Tenant aggregated version of the endpoint

This endpoint returns similar data, but for multiple tenants at the same time 

  • PATH: /rest/v1/metrics/executions/aggregated?offset=number.

EXAMPLE: https://app.etlworks.com/rest/v1/metrics/executions/aggregated?offset=0

  • METHOD: GET.
  • HEADER: Authorization:Bearer access-token.
  • PARAMETERS:
    • offset=number - an offset is a number of days since today to return aggregated per-Flow metrics. 0 means today, 1 means yesterday, etc. If not provided, defaults to 0.
    • date=string - date in ISO-8601 format (YYYY-MM-DD) for which records should be returned. Note that records date comparison is done based on configured timezone. For executions at Super Admin level timezone is taken from Settings -> Timezone -> Administration Timezone, for tenant level executions timezone is taken from Users -> Tenants -> {Specific tenant} -> Timezone.
    • includeMetrics=boolean - (default: false) if enabled, each execution object will include metrics, file metrics and exception message, if any.
    • tenants=string - if not specified, or set to all, then results will be returned for all tenants and Super Admin level. If set to comma separate list of tenant IDs, then results will include data only for specified tenants.
  • REQUEST BODY: none.
  • REQUEST CONTENT TYPE: none.

Response

The response is a JSON document in the following Format:

[
  {
    "flowId": number,
    "tenantId": number,
    "name": "string",
    "icon": "string",
    "executions": [
      {
        "flowId": number,
        "auditId": number,
        "status": "string",
        "user": "string",
        "started": number,
        "ended": number,
        "records": number,
        "exception": "string",
        "metrics": "serialized JSON string",
        "fileMetrics": "serialized JSON string"
      }
    ]
  }
]

Related articles

Comments

0 comments

Please sign in to leave a comment.