Good day -
I’m familar with the query to extract the total FNI count from a Camunda 7 database for a period, but I’d like to get more granularity - breakdown by BPMN definition ideally. However, I’m struggling to put that together from the database. The numbers per node are displayed in the Enterprise Cockpit so they must be available - could someone assit with extracting them?
This would assist me in identifying the most complex and expensive processes.
SELECT 'FNI' AS Metric,
ISNULL(SUM(VALUE_), 0) AS [Count]
WHERE NAME_ = 'activity-instance-start' AND TIMESTAMP_ BETWEEN '01 Jan 2022' AND '31 Dec 2022'
After some digging in the database I have developed the following query which shows FNI per process definition. The sum of the FNI values from this query aren’t a perfect match for the official FNI count query above, but the difference is small enough (1%) for my purposes. I haven’t been able to identify what’s causing the discrepency, perhaps timing differences.
Note that on a large/busy database these tables can get very large and running this query could impact Camunda’s performance.
SELECT act.PROC_DEF_KEY_ AS ProcessDefinition,
FROM ACT_HI_ACTINST act WITH (NOLOCK)
INNER JOIN ACT_HI_PROCINST procinst WITH (NOLOCK) ON procinst.PROC_INST_ID_ = act.PROC_INST_ID_
WHERE act.START_TIME_ BETWEEN '01 Mar 2023' AND '31 Mar 2023'
AND procinst.STATE_ NOT IN ('ACTIVE')
GROUP BY act.PROC_DEF_KEY_
ORDER BY act.PROC_DEF_KEY_