Bizagi SQL queries post is recommended to every BPM Consultant or Developer. These queries can be used as a debugging method or as a way to understand better how things are managed at a database level.
Every time I work with a new BPM tool I always try to figure out where does it stores the information in the database. For example where does Bizagi stores the instances of each process.
On this post I will share my list of most powerful and useful queries that I developed during my working experience with Bizagi.
Bizagi SQL queries
- First let’s see all the processes (also known as WORKFLOW) from our project. This query will retrieve the workflow id, workflow name and the status: Active(1) or Inactive(0). This query was also used in this post: Customize Case Number using Sequence.
- List of all cases in a descending order. A case represents a process instance. I selected only the attributes that I thought are useful for our next queries. An advice is to select all the attributes to see exactly what the table contains.
- Get the details of each case (process instance). In this example I will retrieve the details of idCase = 1. Use the case id from the previous query.
- List of all business rules and expressions.
- List of all users.
- Job scheduler details
- Environment details
SELECT A2.idWorkflow, A1.wfClsDisplayName, A2.wfActive FROM WFCLASS A1 INNER JOIN WORKFLOW A2 ON A1.idWfClass = A2.idWFClass
SELECT idCase,casCreationDate,idWorkflow,idCreatorUser,idCaseState, idParentCase,radNumber AS 'Case Number',idParentWorkItem FROM WFCASE UNION SELECT idCase,casCreationDate,idWorkflow,idCreatorUser,idCaseState, idParentCase,radNumber,idParentWorkItem FROM WFCASECL ORDER BY idCase DESC
DECLARE @idCase INT SET @idCase = 1 SELECT A1.idWorkItem, A2.tskDisplayName AS 'Task Name', A3.wiName AS 'Work Item State', A1.wiEntryDate AS 'Work Item Start Date' FROM [dbo].[WORKITEM] A1 INNER JOIN [dbo].[TASK] A2 ON A1.[idCase] = @idCase AND A1.[idTask] = A2.[idTask] INNER JOIN [dbo].[WORKITEMSTATE] A3 ON A1.idWorkItemState = A3.idWorkItemState
SELECT A1.[ruleName], A1.[ruleDisplayName], A1.[ruleDescription], A2.[ruleTypeName], A1.[ruleFormula] FROM [dbo].[BIZRULE] A1 LEFT OUTER JOIN [dbo].[RULETYPE] A2 ON A1.idRuleType = A2.idRuleType
SELECT * FROM [dbo].[WFUSER]