Bizagi SQL queries

Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInShare on RedditShare on Tumblr

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

  1. 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.
  2. SELECT
        A2.idWorkflow, A1.wfClsDisplayName, A2.wfActive
    FROM
        WFCLASS A1
    INNER JOIN
        WORKFLOW A2
    ON
        A1.idWfClass = A2.idWFClass
    
  3. 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.
  4. 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
    
  5. 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.
  6. 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
    
    
  7. List of all business rules and expressions.
  8. 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
    
  9. List of all users.
  10. SELECT * FROM [dbo].[WFUSER]
    
  11. Job scheduler details
  12. Environment details