Report showing the count of each BPM (template) used per month

[Berto2002]

I would like data to show my most used BPMs. After export I would like to be able to select a period and see the number of times each BPM was called-up and sort them and graph them. So the data I need is:

• BPM ID (i.e. an occurence of the BPM at the time a Request was created)
• Date it was created (created is fine for my purposes which will coincide with then end of the IC form in most cases)
• The BPM name it is an instance of (i.e. the name I see it as in process designer like "Change Process" or "Account Creation Process")

I am trying to follow a process of looking for the data in the entities and trying to find the linking elements. Can anyone push me in the right direction please?

I have found that the h_itsm_request_bpm_evts table contains the h_request_id, h_fk_bpmid and h_pk_id but the bit that is alluding me is how to link the BPMID to the core template it is derived from. I thought it would be the h_bpm_processes table but there's no reference to BPMID there and the h_pk_id there does not match-up with the h_pk_id in h_itsm_request_bpm_evts. I even looked at h_bpm_instance but the primary key there is h_id and in the format "AT20210125000001" which I cannot relate to anything

I guess I am just missing the link between BPMID's and BPMs as I would understand them from process designer.

[Met]

Hi @Berto2002

I think this information is contained within h_bpm_instance:

h_name = name of the BPM workflow invoked

h_version = which revision of the workflow invoked

h_created_on = the datetime the BPM was invoked

h_application = com.hornbill.servicemanager

Each row is an invocation of a BPM workflow from what I can see.

Can then link this to h_bpm_processes on h_name and h_version for both tables in order to pull h_title , which is the name you see in the process designer, and other information from h_bpm_process.

I haven't had a chance to actually test this in the reports section, but it makes sense in my head... could be completely wrong.

[Steve Giller]

1 hour ago, Berto2002 said:

I even looked at h_bpm_instance but the primary key there is h_id and in the format "AT20210125000001" which I cannot relate to anything

Do you use Auto Tasks?

[Berto2002]

17 hours ago, Steve Giller said:

Do you use Auto Tasks?

Ah, of course! I was only looking at the preview data in the database direct and the AT refs would be at the top so I would not see the BPM refs. That's what threw me. OK learning. I will now try @Met's concepts in a report.

[Berto2002]

Thanks both. I got the data I was after. Main purpose is to see what workflows are candidates to deprecate and which are high volume for additional testing when altering, etc.

h_bpm_processes (com.hornbill.servicemanager)->Name	BPM instances (com.hornbill.servicemanager)->Reference	BPM instances (com.hornbill.servicemanager)->Id	BPM instances (com.hornbill.servicemanager)->Created On	h_bpm_processes (com.hornbill.servicemanager)->Version	h_bpm_processes (com.hornbill.servicemanager)->Id	BPM instances (com.hornbill.servicemanager)->Workflow Version
-generic-service-request	SR00060536	BPM20221222000038	2022-12-22 10:06:18	137	3562	137
-generic-service-request	SR00060535	BPM20221222000037	2022-12-22 10:02:10	137	3562	137

Pivots to the counts for 2022. I think my filter for a whole year has hit a 25000 row limit but that can be refined.

-generic-service-request	12466
-incident-process	2297
-lob-app-account-creation	1653
-update-subscriber-list	1227
-leaver-notification	1114
-hardware-request	911
schools-service-request	763
-lob-app-account-removal	695
-new-starter-process	681
-service-desk-mailbox	600
-leaver	563
-software-install-request	297
-talentlinkbwo-emails	289
-user-asset-enquiry	270
-contractor-extension	183
-mover-process	166
-pir-mir-activity-process	165
-infrastructure-alert-emails	134
-raise-po-process-live	115
-new-teams-request-live	104
schools-incident-process	100
-problem-process	54
-ict-surgery	24
-name-change	21
-azure-active-directory-group-update-test-dec-21	19
-generic-service-request-test-for-hr-diverts-aug-2022	15
-orbis-support	12
-software-request-test	10
-generic-service-request-test-cllr-collabs-feb-22	8
-site-visitor-terry-test	8
basic-test-flow-for-	7
dms-generic-service-request	6
-asset-receipting-process	6
-leaver-copy-for-hr-demo	5
-known-error	4
dms---add-users-to-early-life-support-group	3
-password-reset	1
-starter-notification	1
test-for-time-recording-may-2022	1
-site-visitor-rob-test	1
-generic-service-request-for-hold-wait-external-authorisation-march-2022	1

Grand Total

25000

 

utility---number-of-instances-of-each-named-bpm-in-defined-period.report.txt