Reporting API

About reporting API

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

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 an Etlworks Integrator authentication endpoint and receive an access token.

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

Access tokens in the Etlworks Integrator 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?offset=number.

EXAMPLE: https://app.etlworks.com/rest/v1/metrics/executions?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.
• 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
     },
     ....
   ]
 }
]

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 HTTP Connection for the reporting API endpoint.

• URL: https://your_instance_base_url/rest/v1/metrics/executions?offset=0.
• Method: GET.
• Authentication: oauth2.
• User or AccessKey:  the username or email of any user with the Administrator role.
• Password or SecretKey: the password for the user above.
• Authentication URL: https://your_instance_base_url/rest/v1/token-auth/issue.
• Authentication Request Payload: {"login":"{user}","password":"{password}"}.

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:

• Amazon S3 
• Google Cloud Storage 
• Microsoft Azure Storage 
• Server Storage 
• FTP 
• FTPS 
• SFTP 
• Box
• Dropbox
• Google Drive
• OneDrive for Business
• SharePoint
• WebDAV 
• Outbound email 

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. 

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:

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'