Views:

Scenario Overview

In this scenario we need to provide a daily report of any failed system jobs (i.e. workflows, actions, internal Microsoft jobs) to the system administrator. We decided to use the North52 Scheduler to execute a Global Action which will send an email which contains a HTML table of any failed system jobs.

Below is a screenshot of what the email to the system administrator would look like:

N52 Formula Manager Solution

The Formula Manager solution works like this:

  • A Schedule is setup to execute on a daily basis
  • This Schedule executes a Global Action
  • The first step of the action is to call a Process Genie formula to generate a HTML table which contains the list of failed system jobs
  • The second step is a Send Email step which sends the email with the generated HTML from the above step

The HTML table can be styled by modifying the default CSS web resource named north52_/css/htmltable

                                                                                        

Formula Steps

The following set of steps outline how to create this Formula

  • Create a new formula, setting the following values in the Formula Guide
    • Source Entity set to Email
    • Set Formula Type to Process Genie
    • Select the Classic editor
  • Copy and paste the formula below into the Formula editor canvas
  • Click Save

Formula

CreateHtmlTable( 
                FindRecordsFD('RetrieveFailedSystemJobsToday'), 
                SetColumnOrder('name', 'startedon', 'regardingobjectid', 'ownerid',
                              'statecode', 'statuscode', 'operationtype'),
                'https://18102014.crm4.dynamics.com/',
                'north52_/css/htmltable'
               )

Formula Detail Steps

The formula above contains a function FindRecordsFD. This function returns a list of records defined in a Fetch-Xml query stored in a Formula Detail record that is associated to the formula. 

 

The following set of steps outline how to create the Formula Detail record:

  • Select the Fetch-Xml tab
  • Click the + icon to create a new Formula Detail record
  • Set the name to RetrieveFailedSystemJobsToday
  • Copy and paste the Fetch-Xml below into the Query field
  • Click Save & Close to return to the Formula

Fetch-XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="asyncoperation">
    <attribute name="asyncoperationid" />
    <attribute name="name" />
    <attribute name="regardingobjectid" />
    <attribute name="operationtype" />
    <attribute name="statuscode" />
    <attribute name="ownerid" />
    <attribute name="startedon" />
    <attribute name="statecode" />
    <order attribute="startedon" descending="true" />
    <filter type="and">
      <condition attribute="statuscode" operator="eq" value="31" />
      <condition attribute="modifiedon" operator="today" />
    </filter>
  </entity>
</fetch>

Action Steps

The following set of steps outline how to create this Action process:

  • Create a new Action named Daily Failed System Jobs new_DailyFailedSystemJobs - see Important Note below **
  • Set it to be a 'Global Action'
  • Add a new step to the Action to call the 'North52 Process Genie'
  • Set the formula shortcode to be the one on the formula you just created, in this case 'LhB'
  • Add a new step to the Action to 'Send an Email'
  • Use the String Output from the Process Genie step to place the Generated HTML into the body of the email
  • Click Activate

**Note : Please note that the 'Process Name' field on the action must contain as the last word the exact same name as the Unique Name that Microsoft generates for you on the Action.

Example: Daily Failed System Jobs becomes Daily Failed System Jobs new_DailyFailedSystemJobs 

This is due to a bug when you execute fetch-xml to lookup at run-time the unique name of the Action. We logged this bug with Microsoft but it has yet to be resolved. 

Action Setup


Process Genie step Configuration
 
Details of the Send Email Step

 
 

Schedule Steps

The following set of steps outline how to create this Formula

  • Create a new North52 Schedule
  • Set the Name to Daily Email of Failed System Jobs
  • Set the Frequency field to Daily
  • Set the Schedule type to Global Action
  • Set the Workflow field to the name of the Action: Daily Failed System Jobs new_DailyFailedSystemJobs
  • Set the Start time to the desired date & time
  • Click Save
  • Finally to start the Schedule click N52 Commands -> Start Schedule
  • To verify that it is up and running check the System Jobs view

Schedule Setup 

Testing 

You will need some failed system Jobs in your system to return results. If you don't have any you could remove the following line from the Fetch-XML just for testing purposes:
<condition attribute="statuscode" operator="eq" value="31" />
Comments (1)
  • It would be helpful to add the required functionality to send the report email only when failed System Jobs are found (count > 0).