Workflow Execution status reports can be very useful for WFA users, Admins and Architects even operators. I wanted to receive recurring email reports on how many workflows were executed in the last 7 days, and out of those how many passed or how many in Pending state. It helps to track jobs and workfflow executions. Like I wanted to know how many have been failing and how many times, which are stuck in Waiting for approval and need attention. Which workflows are being executed maximum no of times. How many workflow completed sucessfully etc.
A weekly/daily execution status report email to a list of users is a very useful feaure. But its not available as yet with WFA.
Making such a solution had some big challenges. BIG!!
No rest API to get the list of all Workflow Execution jobs
No mechanism to connect to DB and get job execution information
A job details can't be obtained unless you know the job ID and the workflow UUID. And I can’t get the job ID due to point 1.
However, considering that there is always a possibility of innovation and genius , let's build one.
The following logic is used for building the solution.
Get this list of all workflows.
For every workflow name, get its UUID.
Use API SearchResource with context EXECUTION_STATUS to return the list of jobs for this particular workflow name. Search is based on name pattern matching, so it is possible that jobs of other workflows which had common or similar words in their names will also get retuned. So returned list will be:
All jobs of our concerned workflow + (possibly) jobs of workflows with common patterns in their names.
We need to remove the latter.
For every job in the list of jobs returned in step-3, call API to get Execution details using the workflow UUID . If this job didn't belong to our workflow, it will fail.
Now we have been able to filter out which are our relevant jobs for this workflow. 70% of task done.
Assume you wanted weekly status report i.e. Execution status of the last 7 days. So get current date, get the date old date by subtracting 7 days from current date.
If our current job has execution start date falls within this time duration, get its status, and put in in our report. Prepare as beautiful reports as you want.
Do it for every job
Do the same for every workflow.
Now I've designed a solution that generates the report and sends them by email. But you can use the above logic to generate your own reports the way you want them like in CSV or XL or PDF or just plain text.
Now, Lets look at the 3 commands.
Workflow Execution Status Reporter: It gets the execution status details within the time duration provided by the user, prepares the report in beautiful HTML formats. But this is also going to be in a workflow, so its been designed to ignore its own job when preparing the job execution reports. You'll see as you go on.
Send Email with Workflow Execution Status Reports : Send the Workflow execution status report as email to a list of recipients.
Workflow Scheduler: The command to schedule this workflow for recurring executions so that the execution status reports can be generated and sent automatically every day or every week or as once in 2 weeks or per the user decides.
A sample workflow using the above 3 commands which sends Weekly Execution Status Report is attached. Just import it and execute it after getting the prerequisites done. You'll just love it.
Minimum WFA version 3.0: I've used the new feature Password as a User-Input type in Send email command which is only availabe from WFA3.0 on wards. WFA 2.2 Users will need to slightly modify this command.
You need PowerShell 3.0 on your WFA server. WFA is fully supported to work on Posh3.0. Its available by default in Win2012, Win2008 can be upgraded, Win2003 users can't use it.
Add credentials of a WFA Admin/Architect in you WFA itself with Name/IP: localhost
I get a collection of results in the $results variable but all the job IDs are the same. I see the correct number of entries, for example if the workflow was run 3 times I see 3 items in the collection, but all 3 are idential.
Bug? Something I did wrong? Thinking no on that because if I just use a browser and do essentially the same thing with the correct URL I get the same result.
I ahve imported the workflow and still hitting the issue
11:44:24.293 INFO [Workflow Execution Status Reporter] Credentials successfully provided for 'localhost' 11:44:43.059 ERROR [Workflow Execution Status Reporter] The remote server returned an error: (500) Internal Server Error. 11:44:43.121 ERROR [Workflow Execution Status Reporter] Failed executing command. Exception: The remote server returned an error: (500) Internal Server Error.