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'
Comments
0 comments
Please sign in to leave a comment.