Skip to main content

Use Data Query to report on Workflow

Zuora

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.
  • 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 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 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