Use Data Query to report on Workflow

Last updated
Save as PDF

You can use Data Query to report on workflow versions 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 can only use Data Query to retrieve Worfkflow Run data generated in a certain number of past days. Data generated before this time range is non-retrievable. The time range can be set in the Settings tab of a Workflow definition. See Cleanup policy for more information. We provide an alternative way to retrieve usage data that is outside the cleanup period. See Example 6 below for more information.
If your entity has more than one instance of Workflow, you cannot access Workflow data via Data Query.
Data Query for Workflow reporting is not available in the Enhanced Production Copy Environment (ePCE) or Production Copy Environment (PCE) of Zuora.

Workflow-related tables in Data Query

The following Data Query tables contain information about workflow definitions and runs:

workflow - Each row represents a workflow version or a workflow run. See Example 1, below.
workflow_task - Each row represents a task that is part of a workflow version 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 version. See Example 4, below.
workflow_task_usage - Each row represents your entity's usage of Workflow over a single day. See Example 5, below.
workflow_definition - Each row represents a workflow definition that has at least one workflow version.

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.

Audit Trail reports for Workflow

You can also use Data Query to generate the Audit Trail reports containing changes of the following Workflow objects.

WorkflowDefinition
TaskDefinition
Linkage

The audit trails of these objects are stored in the auditobjectchangeevent table. See Sample Queries of Audit Trail on how to retrieve Workflow object changes.

Audit trail data is available based on audit licensing. See Zuora Editions for more information.

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	Email	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 task t is part of a workflow run.
Since t is part of a workflow run, t.workflowid is the ID of the workflow run and t.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

Example 6: Report on workflow task usage beyond the automatic cleanup period

SELECT
  DATE_TRUNC('month', createddate) AS created_date_month,
  SUM(approvalcount + attachmentcount + billingbillruncount + billingcurrencyconversioncount + billingcustombillingdocumentcount + billingreverseinvoicecount) AS total_tasks,
  SUM(workflowcount) AS total_workflows
FROM
  workflow_task_usage
GROUP BY 1
ORDER BY created_date_month

Workflow and workflow task usage that are beyond the automatic cleanup period cannot be retrieved using the normal SQL queries. But you can use the above example to retrieve the usage data beyond the cleanup period by month. The DATE_TRUNC('month', createddate) function groups data by month. Alternatively, you can use DATE_TRUNC('year', createddate) to retrieve data by year.

You can modify the fields in SUM (...) AS total_tasks to retrieve different information.