View stalled processes

[Guest Paul Alexander]

Hello

Is there a way of finding out or listing which BPM's have stalled? At the moment I can only take a look at these when one of the team tells me that something has broken and their ticket has a big red error message on it, but it'd be nice if I could pro-actively see if there are any problems and get them fixed before anyone notices! 

thanks

 

 

[samwoo]

+1 for a location to see any processes that has stalled / errored - in the Admin Tool via the Service Manager area would be the best place to hold this.

But I doubt there is any way of knowing unless someone attempts to restart the process, the person defined in the email in settings will be notified.

Thanks,

[Victor]

@Paul Alexander @samwoo until a better tool exists in admin, you can theoretically run a report or a query periodically which will highlight requests with BP errors... the SQL query for such report will be something like this:

SELECT h_pk_reference FROM h_itsm_requests
JOIN h_bpm_instance ON h_bpm_instance.h_id = h_itsm_requests.h_bpm_id
WHERE h_bpm_instance.h_state LIKE '%<error>%'

You can add further filters to this like request status, various dates, etc. I can't say 100% that it will return all requests with a broken BP but it should return almost all of them  (if not all...). 

[Guest Paul Alexander]

Thanks @Victor

The SQL certainly helps.....an app of some sort would be nice though because it's a bit long winded finding and  updating the calls (and I'm a bit lazy)

thanks

[samwoo]

On 6/6/2018 at 7:46 AM, Paul Alexander said:

Thanks @Victor

The SQL certainly helps.....an app of some sort would be nice though because it's a bit long winded finding and  updating the calls (and I'm a bit lazy)

thanks

+1

[samwoo]

If anyone is interested, i've had good luck with this SQL:

SELECT 
    h_bpm_id BPM_ID, 
    h_bpm_instance.h_name BPM_NAME,
    h_pk_reference REQUEST_REF, 
    h_datelogged LOGGED_DATE,
    h_fk_servicename SERVICE,
    h_catalog CATALOG,
    h_status STATUS,
    CONCAT('https://admin.hornbill.com/######/app/com.hornbill.servicemanager/workflow/bpm/!',h_bpm_id)  BPM_URL
FROM 
    h_itsm_requests
JOIN 
    h_bpm_instance ON h_bpm_instance.h_id = h_itsm_requests.h_bpm_id
WHERE 
    h_bpm_instance.h_state LIKE '%"status":"failed"%'
    or
    h_bpm_instance.h_state LIKE '%<Error>%'
order by 
    h_datelogged desc

 

[Victor]

@samwoo .. hmm... this: 

 h_bpm_instance.h_state LIKE '%"status":"failed"%'

BP definition syntax will have this text even if the BP has not failed... In the definition syntax this serves as a placeholder to store a failure in case one happens...

P.S. I have removed the name of your instance from the query posted by you  

[samwoo]

6 minutes ago, Victor said:

h_bpm_instance.h_state LIKE '%"status":"failed"%'

Interesting, because all the requests that are being pulled out... are the ones that have errors on them... and we've been resolving them :O (And they are being removed from the list once they are fixed)

6 minutes ago, Victor said:

P.S. I have removed the name of your instance from the query posted by you  

Thanks for that, slipped my mind. I couldn't see your changed so just in case I put in ####### instead.

Thanks,

Samuel

[Victor]

18 minutes ago, samwoo said:

Interesting, because all the requests that are being pulled out... are the ones that have errors on them...

@samwoo just a coincidence  ...  I did run a similar query when a created the query originally and I did have results returned whereby the process was not failed... just saying that you might get such results  

[samwoo]

7 minutes ago, Victor said:

@samwoo just a coincidence  ...  I did run a similar query when a created the query originally and I did have results returned whereby the process was not failed... just saying that you might get such results  

Very interesting indeed!

Is it odd that I am not finding any requests since March 2018, when using the term '%<Error>%' in the query ?

Is there another sure-fire way of detecting any BPM errors?

Thanks,

Samuel

[Victor]

51 minutes ago, samwoo said:

Is there another sure-fire way of detecting any BPM errors?

My suggestion was never "sure"  ... I did say that it should catch all but I did not fully tested as it required "digging"... and this requires time... which I find lacking lately  

So, there should be but I don't have any at the moment...  

[samwoo]

41 minutes ago, Victor said:

My suggestion was never "sure"  ... I did say that it should catch all but I did not fully tested as it required "digging"... and this requires time... which I find lacking lately  

So, there should be but I don't have any at the moment...  

I know exactly how you feel there.

I've tried to look at it, and I was hoping there would be some sort of flowcode beautifier tool... but unfortunately there was nothing there that could help me read the BPM in an easy way so it took me quite a few squints and line breaking in Notepad++ to find what I thought would be a suitable way to search for errors.]

it has worked so far and I am now returning nothing in the list when using the query above along with h_status not in ('status.closed','status.cancelled')

It hasn't returned any "working" BPM's at all, so I wonder if there is a small chance that our setup is slightly different?

Thanks,

Samuel

EDIT:

Refined SQL

SELECT 
    h_bpm_id BPM_ID, 
    h_bpm_instance.h_name BPM_NAME,
    h_pk_reference REQUEST_REF, 
    h_datelogged LOGGED_DATE,
    h_fk_servicename SERVICE,
    h_catalog CATALOG,
    h_status STATUS,
    CONCAT('https://admin.hornbill.com/<INSTANCE>/app/com.hornbill.servicemanager/workflow/bpm/!',h_bpm_id)  BPM_URL
FROM 
    h_itsm_requests
JOIN 
    h_bpm_instance ON h_bpm_instance.h_id = h_itsm_requests.h_bpm_id
WHERE 
    (
        h_bpm_instance.h_state LIKE '%"status":"failed"%'
    or
        h_bpm_instance.h_state LIKE '%<error>%'
    )
AND
    h_status not in ('status.closed','status.cancelled')
order by 
    h_datelogged desc