Why do we need SQL queries
The answer is quite simple. As a Bizagi BPM Consultant/Developer you must have a way to better understand how things are managed at a database level. This can be achieved by running some custom SQL queries.
I mainly use these scripts to instantly get access to some information that otherwise is quite impossible to get from Bizagi. Also, it provides an amazing way to debug your process, check for performances issues, bottlenecks in your workflows, etc.
Bizagi version
All the queries work on the latest Bizagi version (at the time of writing this post, the latest Bizagi version was 11). From my knowledge, the Bizagi database hasn’t changed during the last 2 major versions so it might work as well on 9 and 10.
To run the below queries please log in to the database. Feel free to customise them as much as you like.
I would not recommend playing with the table’s content and here I am talking about doing a manual insert, update or delete. There is a high chance of messing with Bizagi metadata and that’s the last thing you want, believe me.
Content
- SQL queries to retrieve users data
- Retrieve all vocabularies
- Retrieve all tasks for a specific user
- Retrieve all tasks by case id
- Retrieve all the processes with their available versions
- Retrieve all cases
- Decrypt the Catalog content
SQL queries to retrieve users’ data
-- 1. List all users
-- [enabled] column shows if the user is active (1-true) or inactive (0-false)
SELECT * FROM WFUSER
-----------------------------------
-- 2. List active users and their roles
SELECT
a2.userName, a2.enabled, a3.roleDisplayName, a3.roleDescription
FROM
USERROLE a1
INNER JOIN
WFUSER a2
ON
a1.idUser = a2.idUser
AND a2.enabled = 1 -- bring only the active wfusers
INNER JOIN
ROLE a3
ON
a1.idRole = a3.idRole
ORDER BY
a2.userName, a3.roleName
-----------------------------------
-- 3. List active Stakeholders
SELECT
a2.entDisplayName AS 'Stakeholder', a3.userName AS 'User'
FROM
BASTAKEHOLDERUSER a1
INNER JOIN
ENTITY a2
ON
a1.idEnt = a2.idEnt
AND a2.entType = 4 -- stakeholder
AND a1.disabled = 0 -- bring only the active stakeholders
INNER JOIN
WFUSER a3
ON
a1.idUser = a3.idUser
AND a3.enabled = 1 -- bring only the active wfusers
ORDER BY
a2.entDisplayName, a3.userName
-----------------------------------Get all users and the associated roles separated by a comma (,)
SELECT
A1.idUser,
A1.fullName,
A1.userName,
STUFF(
(
SELECT ', ' + A3.roleName
FROM [dbo].[USERROLE] A2
INNER JOIN [dbo].[ROLE] A3 ON A2.idRole = A3.idRole
WHERE A2.idUser = A1.idUser
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
) AS Roles
FROM
[dbo].[WFUSER] A1
ORDER BY
A1.fullName;
Retrieve all vocabularies
IF object_id('tempdb.dbo.#vocabulary') IS NOT NULL
DROP TABLE #vocabulary
IF object_id('tempdb.dbo.#vocabularyValues') IS NOT NULL
DROP TABLE #vocabularyValues
GO
SELECT IDENTITY(INT,1,1) AS Id, guidObject, objName, [dbo].[fnBA_DB_BlobToClob](objContentResolved) AS objContent
INTO #vocabulary
FROM babizagicatalog WHERE objType = 1025 AND deleted = 0 ORDER BY objName
--SELECT * FROM #vocabulary
DECLARE @id INT, @countRows INT, @sql NVARCHAR(max), @json NVARCHAR(MAX);
CREATE TABLE #vocabularyValues(
Id INT,
Vocabulary VARCHAR(255),
VocabularyDescription VARCHAR(255)
)
SELECT @id = 1, @countRows = max(Id) FROM #vocabulary
WHILE @id <= @countRows
BEGIN
SELECT @json = objContent FROM #vocabulary WHERE Id = @id
INSERT INTO #vocabularyValues
SELECT @id as Id,CAST([key] AS VARCHAR(255)), CAST([value] AS VARCHAR(255)) FROM OPENJSON(@json)
WHERE [key] IN ('displayName','description','constant')
SET @id = @id + 1;
END
--SELECT * FROM #vocabularyValues
SELECT * FROM (
SELECT displayName AS Vocabulary,[description] AS VocabularyDesc,constant AS VocabularyValue FROM
( SELECT Id, Vocabulary, VocabularyDescription FROM #vocabularyValues ) d
PIVOT
(max(VocabularyDescription)
FOR Vocabulary IN (displayName,[description],constant)) piv ) B
WHERE
B.VocabularyValue IS NOT NULL
Retrieve all tasks for a specific user
The script can also be customised to retrieve all the tasks and with their assigned users
DECLARE @username VARCHAR(255) = 'admon'
SELECT
A2.userName, A6.radNumber AS 'Case Number',
A3.tskTpName AS 'Task Type', A5.tskDisplayName AS 'Task Name'
FROM
CURRENTASSIGNEE A1
INNER JOIN
WFUSER A2
ON
A1.idUser = A2.idUser
AND A2.userName = @username
AND A1.Deleted = 0
INNER JOIN
TASKTYPE A3
ON
A1.idTaskType = A3.idTaskType
INNER JOIN
WORKITEM A4
ON
A1.idWorkItem = A4.idWorkItem
INNER JOIN
TASK A5
ON
A4.idTask = A5.idTask
INNER JOIN
WFCASE A6
ON
A4.idCase = A6.idCase
LEFT OUTER JOIN
WFUSER A7
ON
A2.idBossUser = A7.idUser
ORDER BY
A6.radNumber, A3.tskTpNameRetrieve all tasks by case ID
DECLARE @caseId INT = 1803
SELECT
A3.wiName AS 'WorkItem State', A4.tskName AS 'Task Name',
A7.userName, A6.tskTpName AS 'Task Type'
FROM
(SELECT * FROM WORKITEM UNION ALL SELECT * FROM WORKITEMCL) A2
INNER JOIN
WORKITEMSTATE A3
ON
A2.idWorkItemState = A3.idWorkItemState
AND A2.idCase = @caseId
INNER JOIN
TASK A4
ON
A2.idTask = A4.idTask
INNER JOIN
CURRENTASSIGNEE A5
ON
A2.idWorkItem = A5.idWorkItem
INNER JOIN
TASKTYPE A6
ON
A5.idTaskType = A6.idTaskType
INNER JOIN
WFUSER A7
ON
A5.idUser = A7.idUser
ORDER BY
A2.wiEntryDate
Retrieve all the processes with their available versions
SELECT
A1.wfClsDisplayName AS 'Process Name',
A1.wfClsDescription AS 'Process Description',
A2.wfVersion AS 'Process version'
FROM
WFCLASS A1
INNER JOIN
WORKFLOW A2
ON
A1.idWfClass = A2.idWFClass
ORDER BY
A1.wfClsDisplayName, A2.wfVersionRetrieve all cases
To improve performance, Bizagi stores the active cases in WFCASE and the inactive ones in WFCASECL (the CL from the end stands for CLOSED).
The same logic applies to WFWORKITEM. The transition from normal table to CL is performed at night when the portal’s activity is low.
SELECT
idCase AS 'Case Id',casCreationDate AS 'Date Created',
idWorkflow AS 'Workflow Id',idCreatorUser,
idCaseState,radNumber AS 'Case Number'
FROM
WFCASE
UNION ALL
SELECT
idCase,casCreationDate,idWorkflow,
idCreatorUser,idCaseState,radNumber
FROM
WFCASECL
ORDER BY
casCreationDateDecrypt the Catalog content
To decrypt the column called objContentResolved I use the function [dbo].[fnBA_DB_BlobToClob].
SELECT guidObject, objName, [dbo].[fnBA_DB_BlobToClob](objContentResolved) 'Decrypted Content' FROM vwBA_Catalog_BABIZAGICAT_ALL