Jump to content

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


Berto2002
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...