Workflow Failure Queries

This post has been republished via RSS; it originally appeared at: Core Infrastructure and Security Blog articles.

I regularly see customers getting a lot of alerts indicating that workflows have failed to run a script of WMI query…the alert name is usually one of the following:

• Workflow Runtime: Failed to run a process or script
  
• Workflow Runtime: Failed to run a WMI query
  
• Workflow Runtime: Failed to run a WMI query for WMI events
  
• Workflow Initialization: Failed to start a workflow that runs a process or script
  
• Workflow Initialization: Failed to start a process or script

The descriptions on these alerts will give details about the workflow that failed and the script or WMI error.  It’s important to review these alerts to determine if there is:

• A specific workflow that is consistently failing
  
• Specific Agents where workflows are consistently failing
  
• Specific Operating Systems where workflows are consistently failing

The following queries can be run against the OpsMgr Data Warehouse database to get more information about these alerts:

--Get all workflow failure alerts within the last 30 days
select
a.RaisedDatetime
,me.DisplayName as Agent
,a.AlertName
,a.AlertDescription
,RepeatCount
from Alert.vAlert a with (NOLOCK)
join vManagedEntity me on me.ManagedEntityRowId=a.ManagedEntityRowId
where (AlertName like '%workflow runtime%' or AlertName like '%workflow initialization%')
and a.RaisedDateTime > DateAdd (dd,-30,GetUTCDate())
order by Displayname,RaisedDateTime

--Get server name, Alert name, and count of workflow failure alerts within the last 30 days
select
me.DisplayName as Agent
,a.AlertName
,Count(*) as Count
,Sum(RepeatCount) as TotalRepeatCount
from Alert.vAlert a with (NOLOCK)
join vManagedEntity me on me.ManagedEntityRowId=a.ManagedEntityRowId
where (AlertName like '%workflow runtime%' or AlertName like '%workflow initialization%')
and a.RaisedDateTime > DateAdd (dd,-30,GetUTCDate())
group by me.DisplayName,a.AlertName
order by Count(*) desc,Displayname,AlertName

--Get Operating System name, Alert name, and count of workflow failure alerts within the last 30 days
select
me2.DisplayName as "Operating System"
,a.AlertName
,Count(*) as Count
,Sum(RepeatCount) as TotalRepeatCount
from Alert.vAlert a with (NOLOCK)
join vManagedEntity me on me.ManagedEntityRowId=a.ManagedEntityRowId
join vManagedEntity me2 on (me2.TopLevelHostManagedEntityRowId=me.TopLevelHostManagedEntityRowId and me2.ManagedEntityTypeRowId in (select ManagedEntityTypeRowId from vManagedEntityType where ManagedEntityTypeSystemName = 'Microsoft.Windows.OperatingSystem'))
where (AlertName like '%workflow runtime%' or AlertName like '%workflow initialization%')
and a.RaisedDateTime > DateAdd (dd,-30,GetUTCDate())
group by me2.DisplayName,a.AlertName
order by Count(*) desc,me2.Displayname,AlertName