Database query to extract FNI usage by BPMN process definition

Camunda 7 Topics Discussion & Questions
1

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] 
FROM ACT_RU_METER_LOG 
WHERE NAME_ = 'activity-instance-start' AND TIMESTAMP_ BETWEEN '01 Jan 2022' AND '31 Dec 2022'

Thanks
Peter

2

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,
       COUNT(*) FNI
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_
3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.