Home Advanced Request Offering

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

Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

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.


,s.[UserText2_DDCE6D39_0E80_E3CE_3219_0F163D86BAFD] AS 'User'
,CAST(s.[UserDate1_8210C35D_BC14_9150_E70D_BCA5EAF37E2C] AS DATE) AS 'Expected Date'
,s.[Created] AS Created
,CONVERT(DATETIME, DATEADD(HOUR, -5, [Created]), 100) AS 'Created_minus 5 hours'
,[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 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! :)


  • Options
    Geoff_RossGeoff_Ross Cireson Consultant O.G.

    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?


  • Options
    Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    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!

  • Options
    Geoff_RossGeoff_Ross Cireson Consultant O.G.

    Hi Ryan,

    In that case you will need to join to a relationship table, like in your SQL above. What is not working there?
    That looks to be pulling a bunch of properties of all SRs that have a Runbook Activity with a certain runbook. Are you saying that in some cased the RBA is under a PA and therefore those SRs don't get selected.
    You need to do another join to relationship table to dive a level deeper.


Sign In or Register to comment.