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 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
andoffset
are mutually exclusive. We recommend usingdate
. -
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:
- 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'
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 toall
, 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"
}
]
}
]
Comments
0 comments
Please sign in to leave a comment.