Home› Advanced Request Offering

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

Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
in Advanced Request Offering

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! :)

Best Answer

  • Geoff_RossGeoff_Ross Cireson Consultant O.G.
    Answer ✓

    Hi Ryan,

    You need to add in a second join to the relationship table to get the ones under that PA. If you have double nested RBAs, you will need to go again etc. Keep iterating until you have enough.

    Geoff

Answers

  • 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?

    Geoff

  • 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!

  • 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.

    Geoff

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    Hi,

    Sorry for the delay in response. This dropped off my radar.

    There are runbooks under a PA but the query above only finds runbooks outside of a PA.

    Looking to see if there is something I am missing or what relationship table I should be using - thanks!

  • Geoff_RossGeoff_Ross Cireson Consultant O.G.
    Answer ✓

    Hi Ryan,

    You need to add in a second join to the relationship table to get the ones under that PA. If you have double nested RBAs, you will need to go again etc. Keep iterating until you have enough.

    Geoff

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    maybe I am overthinking this but:
    even with this query I am not getting the runbooks under a PA. I only get runbooks outside of the parallel activities. Sorry for the mess copy and pasting.
    WITH RecursiveRunbooks AS (
    -- First level activities (non-PA activities including runbook automation)
    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',
    ra.BaseManagedEntityId AS RunbookID,
    ra.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS RunbookTitle,
    CONVERT(DATETIME, DATEADD(HOUR, -4, ra.[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,

       s.[Status],
    
    s.[Priority],
    
    s.[AssignedUser],
    
    s.[CreatedByUser],
    
    s.[AffectedUser],
    
    s.[Category],
    
    s.[TierId],
    
    s.[Tier],
    
    s.[LastModified],
    
    s.[Created],
    
    CONVERT(DATETIME, DATEADD(HOUR, -5, s.[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],
    
    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,
    
    1 AS Level
    
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 TRY_CAST(ra.BaseManagedEntityId AS UNIQUEIDENTIFIER) = r.TargetEntityId -- Capture direct runbooks
    
WHERE 
    
    s.Status NOT IN ('Cancelled', 'Closed')
    
    AND ra.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 IN (
    
        'RB: DeprovisionADUserAccount',
    
        'RB: ProvisionADAcctAndGroups',
    
        'RB: TransferProvisionADAccess',
    
        'RB: LeaveOfAbsence',
    
        'RB: ReturnFromLOA',
    
        'RB: ReEnableADAccess'
    
    )
    
    AND s.Created BETWEEN DATEADD(day, -400, GETDATE()) AND GETDATE()

    
UNION ALL

    
-- Recursive join for runbooks nested under Parallel Activities (PAs)
    
SELECT
    
    RecursiveRunbooks.[Title],
    
    RecursiveRunbooks.WorkItemId,
    
    RecursiveRunbooks.[User],
    
    RecursiveRunbooks.[Expected Date],
    
    ra.BaseManagedEntityId AS RunbookID,
    
    ra.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS RunbookTitle,
    
    CONVERT(DATETIME, DATEADD(HOUR, -4, ra.[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,
    
    RecursiveRunbooks.[Status],
    
    RecursiveRunbooks.[Priority],
    
    RecursiveRunbooks.[AssignedUser],
    
    RecursiveRunbooks.[CreatedByUser],
    
    RecursiveRunbooks.[AffectedUser],
    
    RecursiveRunbooks.[Category],
    
    RecursiveRunbooks.[TierId],
    
    RecursiveRunbooks.[Tier],
    
    RecursiveRunbooks.[LastModified],
    
    RecursiveRunbooks.[Created],
    
    RecursiveRunbooks.[Created_minus_5_hours],
    
    RecursiveRunbooks.[SLOStatus],
    
    RecursiveRunbooks.[PrimaryOwner],
    
    RecursiveRunbooks.[PrimaryOwnerId],
    
    RecursiveRunbooks.[SourceId],
    
    RecursiveRunbooks.[Source],
    
    RecursiveRunbooks.[ScheduledStartDate],
    
    RecursiveRunbooks.[ScheduledEndDate],
    
    RecursiveRunbooks.[ContactMethod],
    
    RecursiveRunbooks.[Description],
    
    RecursiveRunbooks.[DisplayName],
    
    RecursiveRunbooks.[RequiredByDate],
    
    RecursiveRunbooks.[ResolvedDate],
    
    RecursiveRunbooks.[ClosedDate],
    
    RecursiveRunbooks.[CompletedDate],
    
    RecursiveRunbooks.[ImplementationPlan],
    
    RecursiveRunbooks.[ImplementationResults],
    
    RecursiveRunbooks.[Resolution],
    
    RecursiveRunbooks.[ResolutionCategory],
    
    RecursiveRunbooks.[ResolutionDescription],
    
    DATEDIFF(DAY, RecursiveRunbooks.[Expected Date], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) AS DateDiffuse,
    
    CONVERT(INT, DATEDIFF(DD, RecursiveRunbooks.[Expected Date], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78])) AS datediff_days,
    
    CONVERT(INT, DATEDIFF(HH, RecursiveRunbooks.[Expected Date], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) % 24) AS datediff_hours,
    
    CONVERT(INT, DATEDIFF(MI, RecursiveRunbooks.[Expected Date], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) % 60) AS datediff_minutes,
    
    DATEDIFF(MI, RecursiveRunbooks.[Expected Date], ra.[ActualEndDate_A3C4A137_3FF8_39E2_BE65_2D070EEA2E78]) AS DateDiffminutestotal,
    
    RecursiveRunbooks.Level + 1 AS Level
    
FROM 
    
    RecursiveRunbooks
    
INNER JOIN 
    
    dbo.SM_WI_Relationship r 
    
    ON r.SourceEntityId = RecursiveRunbooks.RunbookID
    
JOIN 
    
    [ServiceManager].[dbo].[MT_Microsoft$SystemCenter$Orchestrator$RunbookAutomationActivity] ra 
    
    ON TRY_CAST(ra.BaseManagedEntityId AS UNIQUEIDENTIFIER) = r.TargetEntityId -- Handle nested relationships within PAs
    
WHERE 
    
    ra.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 IN (
    
        'RB: DeprovisionADUserAccount',
    
        'RB: ProvisionADAcctAndGroups',
    
        'RB: TransferProvisionADAccess',
    
        'RB: LeaveOfAbsence',
    
        'RB: ReturnFromLOA',
    
        'RB: ReEnableADAccess'
    
    )

    )
    SELECT *
    FROM RecursiveRunbooks
    ORDER BY Created DESC, Level ASC;

Sign In or Register to comment.