Use Data Query to report on Workflow

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.
• workflow_definition - Each row represents a workflow definition.

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.

Sample SQL queries

SELECT 
  w.originalworkflowid, 
  w.id, 
  w.name AS WorkflowName, 
  w.taskscount, 
  w.status 
FROM 
  workflow AS w 
ORDER BY 
  w.updateddate DESC

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
  )

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

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

SELECT 
  u.emailcount, 
  u.createddate, 
  u.updateddate 
FROM 
  workflow_task_usage AS u