what will be the query for check completion of workflow?

Question

I have to cheack the status of workflow weather that workflow completed within scheduled time or not in sql query format. And also send an email of workflow status like 'completed within time ' or not 'completed within time'. So, please help me out

Answer 1

You can do it either using option1 or option 2.

1. You need access to repository meta database.

• Create a post session shell script. You can pass workflow name and benchmark value to the shell script.
• Get workflow run time from repository metadata base. SQL you can use -

SELECT WORKFLOW_NAME,(END_TIME-START_TIME)*24*60*60 diff_seconds
FROM 
REP_WFLOW_RUN
WHERE WORKFLOW_NAME='myWorkflow'

• You can then compare above value with benchmark value. Shell script can send a mail depending on outcome.
• you need to create another workflow to check this workflow.

2. If you do not have access to Metadata, please follow above steps except metadata SQL. Use pmcmd GetWorkflowDetails to check status, start and end time for a workflow.

pmcmd GetWorkflowDetails -sv service -d  domain -f folder myWorkflow

You can then grep start and end time from there, compare them with benchmark values. The problem is the format etc. You need little bit scripting here.