DB location - BPMs being used

[Kelvin]

I know this was previously asked but cant find the post.

Is there a location in the database which tells me what BPMs are being used. I have a couple which I am fairly sure are not used anymore but before I disable them I want to be sure.

[Lyonel]

SELECT * FROM h_itsm_service_catalog WHERE h_bpm = 'XYZ'

@Kelvin

[Victor]

@Lyonel hmmm... how is that query tell you what BPs are used throughout Hornbill?

The query you posted returns a list of services where the ‘XYZ’ process is used as opposed to a list of (all) processes that are being in use...

Getting this info is not quite straightforward but I’ll have a look on Mon and post something back

[Victor]

... thinking more of this I can swear I did this a while back ... anyway... until l am back in front of a computer (enjoying some sun in south west England right now) the general idea is you need to SELECT from BP configuration table WHERE the BP is not associated to a service, is not associated to a catalog and is not associated to an application setting... as you can see I do not remember the exact table names so an exact query will have to wait until Mon

[Victor]

@Kelvin @Lyonel so, this query gives you all the processes that are in use. For some reason I can't get a query running to retrieve processes that are not in use (not sure why, I'll keep looking).

SELECT DISTINCT(h_title) FROM h_bpm_processes
WHERE h_name IN (SELECT h_value FROM h_sys_app_settings WHERE h_key IN ('app.requests.defaultBPMProcess.incident','app.requests.defaultBPMProcess.service','app.requests.defaultBPMProcess.change','app.requests.defaultBPMProcess.problem','app.requests.defaultBPMProcess.knownerror','app.requests.defaultBPMProcess.release'))
OR h_name IN (SELECT h_incident_bpm_name FROM h_itsm_services)
OR h_name IN (SELECT h_service_bpm_name FROM h_itsm_services)
OR h_name IN (SELECT h_problem_bpm_name FROM h_itsm_services)
OR h_name IN (SELECT h_change_bpm_name FROM h_itsm_services)
OR h_name IN (SELECT h_knownerror_bpm_name FROM h_itsm_services)
OR h_name IN (SELECT h_release_bpm_name FROM h_itsm_services)
OR h_name IN (SELECT h_bpm FROM h_itsm_service_catalog)
ORDER BY h_title

This being said, you can simplify the above if, for example, you are certain you only have and use processes on catalog items, then the query becomes like:

SELECT DISTINCT(h_title) FROM h_bpm_processes
WHERE h_name IN (SELECT h_bpm FROM h_itsm_service_catalog)
ORDER BY h_title

In any case, the first query returns all processes configured in all possible/potential places. With this list, you can then have a look at all processes in your instance and remove the ones that are not on the list if you like.

The query can also be ported into a report with the use of JOINs...