Skip to main content

Use Data Query to report on Workflow

Zuora

Use Data Query to report on Workflow

  1. Last updated
  2. 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.