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,

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

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!

Geoff_Ross

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_Kelley

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_Ross

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_Kelley

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;

Ryan_Kelley

bump