SCSM Live Reporting - Display Child IR List of Specified Parent IR

Conner_Wood

Hi all,

Since it is rare to come across SQL documentation for SCSM Live Reporting and since there will inevitably be something someone wants reporting on in real time, it would be a good idea to post this for others so they'll have a headstart unlike myself .  For this example I will say IR55000 is the Parent IR and want the list of current Children IRs and the Live SCSM Database is called ServiceManager.

Note:  You'll notice the formatting on the columns is so that is can be easily copy pasted into excel, also the Created Date is converted from UTC to local time and given an additional space at the start so it won't lose the formatting in excel.

USE [ServiceManager<span>];
</span>
DECLARE @ParentIncidentID nvarchar(256) = 'IR55000';<br>
SELECT replace(replace(replace(IR_Parent.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C], char(10), ' '), char(13), ' '), char(9), ' ') AS 'Parent Incident ID'
	  ,replace(replace(replace(IR_Child.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C], char(10), ' '), char(13), ' '), char(9), ' ') AS 'Child Incident ID'
	  ,replace(replace(replace(IR_Child.[Title_9691DD10_7211_C835_E3E7_6B38AF8B8104], char(10), ' '), char(13), ' '), char(9), ' ') AS 'Title'
	  ,replace(replace(replace(IR_Status.[Status], char(10), ' '), char(13), ' '), char(9), ' ') AS 'Status'
	  ,replace(replace(replace(AssignedTo.[DisplayName], char(10), ' '), char(13), ' '), char(9), ' ') AS 'Assigned To'
	  ,IIF(IR_Child.[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] IS NULL, '', ' ' + CAST(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), IR_Child.[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688]) AS varchar(50))) AS 'Created Date'
  FROM [dbo].[Relationship] R (nolock)

  JOIN [dbo].[RelationshipType] RT (nolock)
  ON RT.[RelationshipTypeId] = R.[RelationshipTypeId]
  AND RT.[RelationshipTypeName] = 'System.WorkItemHasParentWorkItem'

  -- Join is forcing looking for particular parent incident (target endpoint) which we specify by ID
  JOIN [dbo].[MTV_System$WorkItem$Incident] IR_Parent (nolock)
  ON IR_Parent.[BaseManagedEntityId] = R.[TargetEntityId]
  AND IR_Parent.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] = @ParentIncidentID

  --Join is forcing source endpoint to retrieve children incidents
  JOIN [dbo].[MTV_System$WorkItem$Incident] IR_Child (nolock)
  ON IR_Child.[BaseManagedEntityId] = R.[SourceEntityId]

  --Join for getting localized child incident status enumeration
  LEFT JOIN
  (
		SELECT DISTINCT Enum.EnumTypeId, LT.LTValue AS 'Status' 
		FROM [dbo].[EnumType] Enum (nolock)
		JOIN [dbo].[LocalizedText] LT (nolock)
		ON LT.LanguageCode = 'ENU'
		AND LT.LTStringType = 1 -- 1 is localized DisplayName for the element, 2 is Description
		AND Enum.EnumTypeId = LT.LTStringId
  ) AS IR_Status ON IR_Status.[EnumTypeId] = IR_Child.[Status_785407A9_729D_3A74_A383_575DB0CD50ED]


  --Join for getting child incident Assigned To Display Name if someone is assigned.
  LEFT JOIN 
  (
        SELECT R.SourceEntityId AS 'BaseManagedEntityId', AssignedUser.DisplayName
        FROM [dbo].[Relationship] R (nolock)
        JOIN [dbo].[RelationshipType] rType (nolock)
        ON rType.RelationshipTypeId = R.RelationshipTypeId
        JOIN [dbo].[BaseManagedEntity] AssignedUser (nolock)
        ON AssignedUser.BaseManagedEntityId = R.TargetEntityId
        WHERE rType.RelationshipTypeName = 'System.WorkItemAssignedToUser' AND R.IsDeleted = 0
  ) AS AssignedTo ON AssignedTo.BaseManagedEntityId = IR_Child.BaseManagedEntityId

ORDER BY IR_Child.[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688]

Remember that you'll have to execute this query on your Live SCSM Database in SSMS and name it something other than ServiceManager if you had named it differently when installing your SCSM environment.