Ryan Vice's Blog

Below is a query that will return tracking data when using the SQL

Simply substitue the GUID for the WF instance that you want to find out tracking details about and then fire the query to see the tracking data.

declare  @WorkflowInstanceInternalId   bigint

select   @WorkflowInstanceInternalId = WorkflowInstanceInternalId
from dbo.WorkflowInstance
where
         WorkflowInstanceId = 'E3573C8D-96D7-47C9-AE64-CB3E197A18CE'

select   wie.EventDateTime,
         'Workflow ' + twe.Description [Status],
         wie.EventOrder,
         '' [QualifiedName],
         '' [TypeName],
         '' [UserDataKey],
         '' [UserData_str]
from dbo.WorkflowInstanceEvent wie
   inner join dbo.TrackingWorkflowEvent twe
      on (wie.TrackingWorkflowEventId = twe.TrackingWorkflowEventId)
where wie.WorkflowInstanceInternalId = @WorkflowInstanceInternalId
union
select   se.EventDateTime,
         aes.Description [Status],
         se.EventOrder,
         ai.QualifiedName,
         case when charindex('.',reverse(t.TypeFullName)) > 1
                   then right(t.TypeFullName,charindex('.',reverse(t.TypeFullName))-1)
                   else ''
            end [Type],
         '' [UserDataKey],
         '' [UserData_str]
from dbo.ActivityExecutionStatusEvent se
   inner join dbo.ActivityExecutionStatus aes
      on (se.ExecutionStatusId = aes.ExecutionStatusId)
   inner join dbo.ActivityInstance ai
      on (se.ActivityInstanceId = ai.ActivityInstanceId)
   inner join dbo.WorkflowInstance wi
      on (ai.WorkflowInstanceInternalId = wi.WorkflowInstanceInternalId)
   left join dbo.Activity a
      on (a.WorkflowTypeId = wi.WorkflowTypeId
      and ai.QualifiedName = a.QualifiedName)
   inner join dbo.Type t
      on (a.ActivityTypeId = t.TypeId)
where se.WorkflowInstanceInternalId = @WorkflowInstanceInternalId
union
select   ue.EventDateTime,
         'UserData' [Status],
         ue.EventOrder,
         ai.QualifiedName,
         '' [TypeName],
         ISNULL(ue.UserDataKey, '') [UserDataKey],
         ue.UserData_str
from dbo.vw_UserEvent ue
   inner join dbo.ActivityInstance ai
      on (ue.ActivityInstanceId = ai.ActivityInstanceId)
where ue.WorkflowInstanceInternalId = @WorkflowInstanceInternalId
order by EventOrder,
         EventDateTime

Tags: SQL, Tracking, WF 3.5

This entry was posted on Monday, November 23rd, 2009 at 8:12 am and is filed under Tracking, WF 3.5. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.