Use Data Query to report on Workflow
You can use Data Query to report on workflow definitions and runs. Typical use cases include:
- Reporting on workflow updates to meet internal audit or compliance requirements.
- Monitoring and notifying of changes to workflow definitions.
- Triaging or troubleshooting workflow runs.
- Initiating additional actions based on results of workflow runs.
To learn how to use Data Query, see Data Query. See below for sample SQL queries.
If you need further assistance, you can join the Data Query forum in Zuora Community.
- You cannot access data that has been removed per the cleanup policy of a workflow definition.
- If your entity has more than one instance of Workflow, you cannot access Workflow data via Data Query.
Workflow-related tables in Data Query
The following Data Query tables contain information about workflow definitions and runs:
workflow
- Each row represents a workflow definition or a workflow run. See Example 1, below.workflow_task
- Each row represents a task that is part of a workflow definition or a workflow run. See Example 2, below.workflow_api_call
- Each row represents an API call made during a workflow run. See Example 3, below.workflow_linkage
- Each row represents a connection between two tasks in a workflow definition. See Example 4, below.workflow_task_usage
- Each row represents your entity's usage of Workflow over a single day. See Example 5, below.
You can use SHOW COLUMNS
to list the columns that are available in each Data Query table. For more information, see Constructing SQL Queries in Data Query.
Sample SQL queries
Example 1: Report on workflow definitions and runs
SELECT w.originalworkflowid, w.id, w.name AS WorkflowName, w.taskscount, w.status FROM workflow AS w ORDER BY w.updateddate DESC
Notes:
- For a workflow run,
originalworkflowid
is the ID of the corresponding workflow definition. For a workflow definition,originalworkflowid
is null.
Query results:
originalworkflowid | id | WorkflowName | taskscount | status |
---|---|---|---|---|
298 | 731 | WF-298-00000013 | 0 | Finished |
298 | 699 | WF-298-00000012 | 0 | Finished |
298 | 684 | WF-298-00000011 | 0 | Finished |
NULL | 298 | Suspend lapsed subscriptions | 6 | NULL |
265 | 649 | WF-265-00000022 | 0 | Finished |
Example 2: Report on recently-updated tasks in workflow definitions
SELECT w.name AS WorkflowName, t.name AS TaskName, t.actiontype, t.updateddate FROM workflow_task AS t INNER JOIN workflow AS w ON w.id = t.workflowid WHERE t.originalworkflowid IS NULL AND t.updateddate >= ( current_timestamp - interval '1' day )
Notes:
- If a task is part of a workflow run,
originalworkflowid
is the ID of the corresponding workflow definition. Otherwise,originalworkflowid
is null. Therefore,t.originalworkflowid IS NULL
ensures that the report excludes tasks that are part of workflow runs.
Query results:
WorkflowName | TaskName | actiontype | updateddate |
---|---|---|---|
Suspend lapsed subscriptions | Suspend subscription | Suspend | 2021-02-08T11:08:03.875Z |
Suspend lapsed subscriptions | Email customer | 2021-02-08T11:08:11.672Z | |
Suspend lapsed subscriptions | Logout all active sessions | Callout | 2021-02-08T11:08:27.631Z |
Refresh products | List products | Query | 2021-02-03T16:34:22.254Z |
Refresh products | Change name | Update | 2021-02-03T16:34:53.497Z |
Example 3: Report on failed API calls for a particular workflow
SELECT t.originaltaskid, t.workflowid AS WorkflowRunId, a.call, a.calltype, a.errortype FROM workflow_api_call AS a INNER JOIN workflow_task AS t ON t.id = a.taskid WHERE a.errortype != '' AND a.originalworkflowid = 298
Notes:
a.originalworkflowid = 298
specifies the ID of a workflow definition.a.taskid
is the ID of a task that is part of a workflow run, so taskt
is part of a workflow run.- Since
t
is part of a workflow run,t.workflowid
is the ID of the workflow run andt.originaltaskid
is the ID of the task in the corresponding workflow definition.
Query results:
originaltaskid | WorkflowRunId | call | calltype | errortype |
---|---|---|---|---|
397 | 731 | Suspend | REST | ZuoraAPI::Exceptions::ZuoraAPIError |
397 | 699 | Suspend | REST | ZuoraAPI::Exceptions::ZuoraAPIError |
Example 4: Report on the downstream tasks of a particular task
SELECT t.name AS TaskName, l.linkagetype FROM workflow_linkage AS l INNER JOIN workflow_task as t ON t.id = l.targettaskid WHERE l.sourcetaskid = 397
Notes:
l.sourcetaskid = 397
specifies the ID of a task that is part of a workflow definition.
Query results:
TaskName | linkagetype |
---|---|
Logout all active sessions | Success |
Email customer | Success |
Email internal ops | Failure |
Example 5: Report on the number of Email task runs per day
SELECT u.emailcount, u.createddate, u.updateddate FROM workflow_task_usage AS u
Notes:
emailcount
is the number of times the Email task was used (per day) across all workflow runs.
Query results:
emailcount | createddate | updateddate |
---|---|---|
25 | 2021-02-09T00:00:00Z | 2021-02-09T10:01:20.18Z |
10 | 2021-02-08T00:00:00Z | 2021-02-08T06:01:33.73Z |
8 | 2021-02-05T00:00:00Z | 2021-02-05T02:01:35.074Z |