SQL - Reporting - Is there a way to capture items under a PA?

Ryan_Kelley

I am capturing data for our hiring process and we want to know when runbooks run and end ect.

One runbook sits under a PA and I can join tables to see other runbooks but not the one I want.

Example:

SELECT

s.[Title]

,s.WorkItemId

,s.[UserText2_DDCE6D39_0E80_E3CE_3219_0F163D86BAFD] AS 'User'

,CAST(s.[UserDate1_8210C35D_BC14_9150_E70D_BCA5EAF37E2C] AS DATE) AS 'Expected Date'

,s.UserText4_F2285595_280B_DD8D_2A25_4C6EBC4A47C1

,s.[Status]

,s.[Priority]

,s.[AssignedUser]

,s.[CreatedByUser]

,s.[AffectedUser]

,s.[Category]

,s.[TierId]

,s.[Tier]

,s.[LastModified]

,s.[Created] AS Created

,CONVERT(DATETIME, DATEADD(HOUR, -5, [Created]), 100) AS 'Created_minus 5 hours'

,s.[SLOStatus]

,s.[PrimaryOwner]

,s.[PrimaryOwnerId]

,s.[SourceId]

,s.[Source]

,s.[ScheduledStartDate]

,s.[ScheduledEndDate]

,s.[ContactMethod]

,s.[Description]

,s.[DisplayName]

,s.[RequiredByDate]

,s.[ResolvedDate]

,s.[ClosedDate]

,s.[CompletedDate]

,s.[ImplementationPlan]

,s.[ImplementationResults]

,s.[Resolution]

,s.[ResolutionCategory]

,s.[ResolutionDescription]

,[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] AS RunbookID

,CONVERT(DATETIME, DATEADD(HOUR, -4, [ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]), 100) AS 'Actual_End_Runbook_minus 5 hours'

,ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78] AS Actual_End_Runbook

,DATEDIFF(DAY, s.[UserDate1_8210C35D_BC14_9150_E70D_BCA5EAF37E2C], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) AS DateDiffuse

,CONVERT(INT, DATEDIFF(DD, s.[UserDate1_8210C35D_BC14_9150_E70D_BCA5EAF37E2C], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78])) AS datediff_days

,CONVERT(INT, DATEDIFF(HH, s.[UserDate1_8210C35D_BC14_9150_E70D_BCA5EAF37E2C], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) % 24) AS datediff_hours

,CONVERT(INT, DATEDIFF(MI, s.[UserDate1_8210C35D_BC14_9150_E70D_BCA5EAF37E2C], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) % 60) AS datediff_minutes

,DATEDIFF(MI, s.[UserDate1_8210C35D_BC14_9150_E70D_BCA5EAF37E2C], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) AS DateDiffminutestotal

FROM [CiresonAnalytics].[dbo].[SM_WorkItem_ServiceRequest] s

INNER JOIN dbo.SM_WI_Relationship r ON r.SourceEntityId = s.id

JOIN [ServiceManager].[dbo].[MT_Microsoft$SystemCenter$Orchestrator$RunbookAutomationActivity] ra ON ra.BaseManagedEntityId = r.TargetEntityId

WHERE title LIKE '%Provision IT Services for New User%'

AND Created BETWEEN DATEADD(day, -400, GETDATE()) AND GETDATE()

AND ra.RunbookId_21D8C930_42C9_63F5_5770_C67E9FEB1B2E = '9ADB0708-0EFC-43DB-B31B-71DB354216F0'

In this example I can capture a runbook under that guid. But I cannot see the one I need.

Thank you for any help! :)

Geoff_Ross

Hi @Ryan_Kelley,

I think the WorkItem table in the ServiceManagement DB has a column called ParentWI which will be the top level parent, not just the direct parent. Would that help?

Geoff

Ryan_Kelley

That kinda helps but am looking how to get the runbooks under a PA. Would I join that table to itself to try to get all runbooks. Thank you!