[How To] - Quickly View Existing Workflow History of an Incident

Conner_Wood

While the SCSM Console has a top down approach designed by Satan to look for the workflow history of an Incident or any other ticket.

1. [SCSM Console] -> [Administration] -> [Workflows] -> [Status]
2. Attempt to find the workflow you think failed the ticket or didn't get triggered
3. Check both Need attention and All instances Tabs
4. Realize you're in Hell because you have to click on each "View Related Object" to find out what ticket the workflow is about around the time from the ticket history logs

But now, Mr. Wood has a far less tormentful way of going about this.

Wow, really Mr. Wood?

Wow Really Indeed, my tormented scsm'r, indeed I do!

By painstakingly brute forcing combining tables and columns, I finally created a query that could check on the existing workflow history of a ticket. Now you can very quickly check if a workflow was created for a ticket or not.

The below example uses the Incident Table but you could set it up for SRs or CRs as well if needed.

/****** Quickly View Existing Workflow History on an Incident  ******/
USE ServiceManager;
SELECT TOP 1000 LT.[LTValue] AS 'Workflow Name'
	  ,Wflw.[RuleName] AS 'Workflow InternalName'
	  ,JobDetail.[ErrorCode]
	  ,JobDetail.[ErrorMessage]
	  ,JobDetail.[LastModified]
	  ,JobDetail.[Output] AS 'Workflow Actions Log (XML)'
	  ,JobDetail.[TimeStarted]
	  ,JobDetail.[TimeScheduled]
	  ,JobDetail.[TimeFinished]
  FROM [dbo].[WindowsWorkflowTaskJobStatus] WinWorkflow (nolock)


  JOIN [dbo].[Rules] Wflw (nolock)
  ON Wflw.RuleId = WinWorkflow.[RuleId]


  JOIN [dbo].[LocalizedText] LT (nolock)
  ON Wflw.[RuleId] = LT.[MPElementId] AND
  LT.LTStringType=1 AND LT.LanguageCode = 'ENU'


  JOIN [dbo].[JobStatus] JobDetail (nolock)
  ON JobDetail.[BatchId] = WinWorkflow.[BatchId]


  JOIN [dbo].[MT_System$WorkItem$Incident] IR (nolock)
  ON WinWorkflow.[BaseManagedEntityId] = IR.[BaseManagedEntityId]


  --WHERE WinWorkflow.[BaseManagedEntityId] LIKE 'F2647B28-71D7-486F-FBC0-80B498A8F9D0'
  WHERE IR.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] LIKE 'IR708644'


While this may appear simple, it was a living hell figuring it out without documentation.

Conner_Wood

And for those who want Local Times

USE ServiceManager;
SELECT TOP 1000 LT.[LTValue] AS 'Workflow Name'
	  ,Wflw.[RuleName] AS 'Workflow InternalName'
	  ,JobDetail.[ErrorCode]
	  ,JobDetail.[ErrorMessage]
	  ,JobDetail.[LastModified]
	  ,JobDetail.[Output] AS 'Workflow Actions Log (XML)'
	  ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), JobDetail.[TimeStarted]) AS 'TimeStarted (Local Time)'
	  ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), JobDetail.[TimeScheduled]) AS 'TimeScheduled (Local Time)'
	  ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), JobDetail.[TimeFinished]) AS 'TimeFinished (Local Time)'
  FROM [dbo].[WindowsWorkflowTaskJobStatus] WinWorkflow (nolock)

  JOIN [dbo].[Rules] Wflw (nolock)
  ON Wflw.RuleId = WinWorkflow.[RuleId]

  JOIN [dbo].[LocalizedText] LT (nolock)
  ON Wflw.[RuleId] = LT.[MPElementId] AND
  LT.LTStringType=1 AND LT.LanguageCode = 'ENU'

  JOIN [dbo].[JobStatus] JobDetail (nolock)
  ON JobDetail.[BatchId] = WinWorkflow.[BatchId]

  JOIN [dbo].[MT_System$WorkItem$Incident] IR (nolock)
  ON WinWorkflow.[BaseManagedEntityId] = IR.[BaseManagedEntityId]

  --WHERE WinWorkflow.[BaseManagedEntityId] LIKE 'F2647B28-71D7-486F-FBC0-80B498A8F9D0'
  WHERE IR.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] LIKE 'IR729643'

  ORDER BY [TimeStarted (Local Time)]

Adam_Dzyacky

Nice work @Conner_Wood!

Your query coupled with a recent integration of mine means only one thing now. Placing Workflow History directly alongside Work Item History in the portal. Same general process applies per said integration.

1. Make a copy of Incident.js for CustomSpace and insert a div somewhere into the form definition - { DataType: "Display", PropertyName: "SCSMwfHistory", PropertyDisplayName: "<div id=scsmWFHistoryGrid> </div>" }
2. Create a new query for the Dashboard Settings page using your query with a token parameter like {{workitemid}} or something.
3. Create a new js like ViewWorkItemWorkflowHistory.js that is almost identical to the ViewSCOMPerformance.js from above. Update the query guid, update to match the div tags, update the grid to the properties your query returns.

Conner_Wood

@Adam_Dzyacky that's a very good idea! I'm surprised Cireson hasn't make it an official part of their product.