Home Advanced Request Offering
Options

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.

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

Answers

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

    Geoff

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

    Geoff

Sign In or Register to comment.