Examples: SQL queries

Use this reference to learn 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

Note that for a workflow run, originalworkflowid is the ID of the corresponding workflow definition. For a workflow definition, originalworkflowid is null.

Query results:

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
  )

Note that 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:

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:

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:

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:

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.