System Center Service Manager–SQL Query to get a full List of your Incidents

In the most cases, the Reporting has to be done through the Data warehouse DB for SCSM, but in some cases you have to get all the Data from the ServiceManager DB. For Example if you don’t have a Data warehouse DB for SCSM or you need some more Details.

In this Case you have to query the Service Manager DB, and unfortunately, this DB is not as simple as the Data warehouse DB. So to get a complete list of your Incidents, including all your Status, Classifications, Affected Users, Assigned Users and everything else, it will cost some time to build this Query.

Also to get the correct Values for Status, Classification and so on, you have to make some joins, but see yourself.

Take this Query as a basis and changed it to fit your needs. Below is a Link to Download

Query

select
                INC.BaseManagedEntityId                                               as [PK]
              , INC.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C                           as [IR-Nummer]
              , AffectedUser.DisplayName                                              as [Name des betroffenen Benutzers]
              , AffectedUser.UserName_6AF77E23_669B_123F_B392_323C17097BBD            as [Username des betroffenen Benutzers]
              , AffectedUser.Company_8CD345AC_E9BC_E5FE_88BA_D281FC29637D             as [Copmany]
              , AssignedToUser.DisplayName                                            as [Name des zugewiesenen Benutzers]
              , AssignedToUser.UserName_6AF77E23_669B_123F_B392_323C17097BBD          as [Username des zugewiesenen Benutzers]
              , INC.ContactMethod_28FB7672_2975_24DE_8340_B4333DEC82C8                as [Alternative Kontaktmethode]
              , ParentInc.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C                     as [Der Key vom Übergeordneten Incident]
              , LastModified.LastModified                                             as [Zuletzt geändert Datum]
              , INC.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E                 as [Aufgelöst am Datum]
              , INC.DisplayName                                                       as [Displayname]
              , INC.ClosedDate_C529833E_0926_F082_C185_294CBC8BB9FD                   as [ClosedDate]
              , INC.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688                  as [CreatedDate]
              , sla.TargetEndDate_4F17E5C2_86D5_05E8_35DE_6E012567DAB7                as [Zielenddatum]
              , INC.FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A            as [Datum der ersten Antwort]
              , INC.Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B                  as [Description]
              , INC.FirstAssignedDate_4C9FBB15_6DF5_E42E_FD6D_18F416428495            as [FirstAssignedDate]
              , INC.Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794                     as [Priority]
              , INC.ResolutionDescription_85E8B5FA_3ECB_9B6C_0A02_A8C9EC085A39        as [ResolutionDescription]
              , INC.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E                 as [ResolvedDate]
              , INC.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104                        as [Title]
              , Status.LTValue                                                        as [Status]
              , Impact.LTValue                                                        as [Impact]
              , Urgency.LTValue                                                       as [Urgency]
              , INC.FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A            as [First_Response_Date ]
              , Classification.LTValue                                                as [Classification]
              , Source.LTValue                                                        as [Source]
              , INC.Escalated_525F1F92_CEB3_079D_C0A5_E7A06AC4D6A5                    as [Escalated]
from
                MTV_System$WorkItem$Incident as INC
                /*Get Affected User*/
                left outer Join
                                (
                                           select
                                                      rel1.SourceEntityId
                                                    , Users.DisplayName
                                                    , Users.UserName_6AF77E23_669B_123F_B392_323C17097BBD
                                                    , Users.Company_8CD345AC_E9BC_E5FE_88BA_D281FC29637D
                                           from
                                                      MTV_System$Domain$User as Users
                                                      inner join
                                                                 Relationship as rel1
                                                                 on
                                                                            Users.BaseManagedEntityID=Rel1.TargetEntityId
                                           where
                                                      rel1.RelationshipTypeId= 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
                                                      and rel1.IsDeleted     = '0'
                                )
                                as AffectedUser
                                on
                                                affectedUser.SourceEntityId=INC.BaseManagedEntityId
                /*Get Assigned To User*/
                left outer Join
                                (
                                           select
                                                      rel1.SourceEntityId
                                                    , Users.DisplayName
                                                    , Users.UserName_6AF77E23_669B_123F_B392_323C17097BBD
                                           from
                                                      MTV_System$Domain$User as Users
                                                      inner join
                                                                 Relationship as rel1
                                                                 on
                                                                            Users.BaseManagedEntityID=Rel1.TargetEntityId
                                           where
                                                      rel1.RelationshipTypeId= '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
                                                      and rel1.IsDeleted     = '0'
                                )
                                as AssignedToUser
                                on
                                                AssignedToUser.SourceEntityId=INC.BaseManagedEntityId
                /*Get Parent incident*/
                left outer Join
                                (
                                           select
                                                      rel1.SourceEntityId
                                                    , INCS.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C
                                           from
                                                      MTV_System$WorkItem$Incident as INCS
                                                      inner join
                                                                 Relationship as rel1
                                                                 on
                                                                            INCS .BaseManagedEntityID=Rel1.TargetEntityId
                                           where
                                                      rel1.RelationshipTypeId= 'DA3123D1-2B52-A281-6F42-33D0C1F06AB4'
                                                      and rel1.IsDeleted     = '0'
                                )
                                as ParentInc
                                on
                                                ParentInc.SourceEntityId=INC.BaseManagedEntityId
                /* Get Last Modified*/
                left outer join
                                (
                                           SELECT
                                                      t2.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C
                                                    , t1.LastModified
                                           FROM
                                                      dbo.MTV_System$WorkItem$Incident AS t2
                                                      INNER JOIN
                                                                 (
                                                                                 SELECT
                                                                                                 MT_System$WorkItem$Incident_Log.EntityChangeLogId
                                                                                               , inc.BaseManagedEntityId
                                                                                               , ecl.LastModified
                                                                                 FROM
                                                                                                 dbo.MT_System$WorkItem$Incident_Log
                                                                                                 INNER JOIN
                                                                                                                 MT_System$WorkItem$Incident AS INC
                                                                                                                 ON
                                                                                                                                 INC.BaseManagedEntityId = dbo.MT_System$WorkItem$Incident_Log.BaseManagedEntityId
                                                                                                 LEFT OUTER JOIN
                                                                                                                 dbo.EntityChangeLog AS ecl
                                                                                                                 ON
                                                                                                                                 ecl.EntityChangeLogId = MT_System$WorkItem$Incident_Log.EntityChangeLogId
                                                                                 UNION
                                                                                 SELECT
                                                                                                 SLALog.EntityChangeLogId
                                                                                               , inc.BaseManagedEntityId
                                                                                               , EntityChangeLog.LastModified
                                                                                 FROM
                                                                                                 dbo.MTV_System$WorkItem$Incident AS inc
                                                                                                 LEFT OUTER JOIN
                                                                                                                 dbo.Relationship AS rel
                                                                                                                 ON
                                                                                                                                 rel.SourceEntityId = inc.BaseManagedEntityId
                                                                                                 LEFT OUTER JOIN
                                                                                                                 dbo.MT_System$SLA$Instance$TimeInformation_Log AS SLALog
                                                                                                                 ON
                                                                                                                                 SLALog.BaseManagedEntityId = rel.TargetEntityId

                                                                                                 LEFT OUTER JOIN
                                                                                                                 EntityChangeLog
                                                                                                                 ON
                                                                                                                                 SLALog.EntityChangeLogId = EntityChangeLog.EntityChangeLogId
                                                                                 WHERE
                                                                                                 (
                                                                                                                 rel.RelationshipTypeId = 'BA08F9BB-85C7-20D8-05E3-0035938CDDD5'
                                                                                                 )
                                                                 )
                                                                 AS t1
                                                                 ON
                                                                            t1.BaseManagedEntityId = t2.BaseManagedEntityId
                                                                            AND t1.LastModified    =
                                                                            (
                                                                                   SELECT
                                                                                          MAX(LastModified) AS Expr1
                                                                                   FROM
                                                                                          (
                                                                                                          SELECT
                                                                                                                          MT_System$WorkItem$Incident_Log.EntityChangeLogId
                                                                                                                        , inc.BaseManagedEntityId
                                                                                                                        , ecl.LastModified
                                                                                                          FROM
                                                                                                                          dbo.MT_System$WorkItem$Incident_Log
                                                                                                                          INNER JOIN
                                                                                                                                          MT_System$WorkItem$Incident AS INC
                                                                                                                                          ON
                                                                                                                                                          INC.BaseManagedEntityId = dbo.MT_System$WorkItem$Incident_Log.BaseManagedEntityId
                                                                                                                          LEFT OUTER JOIN
                                                                                                                                          dbo.EntityChangeLog AS ecl
                                                                                                                                          ON
                                                                                                                                                          ecl.EntityChangeLogId = MT_System$WorkItem$Incident_Log.EntityChangeLogId
                                                                                                          UNION
                                                                                                          SELECT
                                                                                                                          SLALog.EntityChangeLogId
                                                                                                                        , inc.BaseManagedEntityId
                                                                                                                        , EntityChangeLog.LastModified
                                                                                                          FROM
                                                                                                                          dbo.MTV_System$WorkItem$Incident AS inc
                                                                                                                          LEFT OUTER JOIN
                                                                                                                                          dbo.Relationship AS rel
                                                                                                                                          ON
                                                                                                                                                          rel.SourceEntityId = inc.BaseManagedEntityId
                                                                                                                          LEFT OUTER JOIN
                                                                                                                                          dbo.MT_System$SLA$Instance$TimeInformation_Log AS SLALog
                                                                                                                                          ON
                                                                                                                                                          SLALog.BaseManagedEntityId = rel.TargetEntityId
                                                                                                                          
                                                                                                                          LEFT OUTER JOIN
                                                                                                                                          EntityChangeLog
                                                                                                                                          ON
                                                                                                                                                          SLALog.EntityChangeLogId = EntityChangeLog.EntityChangeLogId
                                                                                                          WHERE
                                                                                                                          (
                                                                                                                                          rel.RelationshipTypeId = 'BA08F9BB-85C7-20D8-05E3-0035938CDDD5'
                                                                                                                          )
                                                                                          )
                                                                                          as temp2
                                                                                   WHERE
                                                                                          (
                                                                                                 BaseManagedEntityId = t2.BaseManagedEntityId
                                                                                          )
                                                                            )
                                )
                                as LastModified
                                on
                                                LastModified.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C=INC.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C
                /* Get SLA */
                left outer Join
                                (
                                           select
                                                      rel1.SourceEntityId
                                                    , SLAs.TargetEndDate_4F17E5C2_86D5_05E8_35DE_6E012567DAB7
                                           from
                                                      MTV_System$SLA$Instance$TimeInformation as SLAs
                                                      inner join
                                                                 Relationship as rel1
                                                                 on
                                                                            SLAs.BaseManagedEntityID=Rel1.TargetEntityId
                                           where
                                                      rel1.RelationshipTypeId                                   = 'BA08F9BB-85C7-20D8-05E3-0035938CDDD5'
                                                      and rel1.IsDeleted                                        = '0'
                                                      and slas.IsCancelled_16DDBB6C_0E00_ACAD_4F04_D134E61AF82B = '0'
                                )
                                as SLA
                                on
                                                SLA.SourceEntityId=INC.BaseManagedEntityId
                /*Status*/
                left outer join
                                (
                                       select *
                                       from
                                              LocalizedText
                                       where
                                              (
                                                     LanguageCode = 'ENU'
                                              )
                                              AND
                                              (
                                                     LTStringType = '1'
                                              )
                                )
                                as Status
                                on
                                                Status.LTStringId = INC.Status_785407A9_729D_3A74_A383_575DB0CD50ED
                /*Impact*/
                left outer join
                                (
                                       select *
                                       from
                                              LocalizedText
                                       where
                                              (
                                                     LanguageCode = 'ENU'
                                              )
                                              AND
                                              (
                                                     LTStringType = '1'
                                              )
                                )
                                as Impact
                                on
                                                Impact.LTStringId = INC.Impact_276C8DBF_2BC3_2374_665E_77FC76513017
                /*Urgency*/
                left outer join
                                (
                                       select *
                                       from
                                              LocalizedText
                                       where
                                              (
                                                     LanguageCode = 'ENU'
                                              )
                                              AND
                                              (
                                                     LTStringType = '1'
                                              )
                                )
                                as Urgency
                                on
                                                Urgency.LTStringId = INC.Urgency_D4A8FF0E_3074_B44F_54AD_CC201ABD9A6A
                /*Classification*/
                left outer join
                                (
                                       select *
                                       from
                                              LocalizedText
                                       where
                                              (
                                                     LanguageCode = 'ENU'
                                              )
                                              AND
                                              (
                                                     LTStringType = '1'
                                              )
                                )
                                as Classification
                                on
                                                Classification.LTStringId = INC.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA
                /*Source*/
                left outer join
                                (
                                       select *
                                       from
                                              LocalizedText
                                       where
                                              (
                                                     LanguageCode = 'ENU'
                                              )
                                              AND
                                              (
                                                     LTStringType = '1'
                                              )
                                )
                                as Source
                                on
                                                Source.LTStringId = INC.Source_96FD9295_16FA_3D7A_5995_F805B7B01F21
                                                /*
                                                select * from RelationshipType where RelationshipTypeName like '%ltvalue%'
                                                select * from MTV_System$SLA$Instance$TimeInformation
                                                */
                                                

Download from TechNet Gallery: https://gallery.technet.microsoft.com/SCSM-SQL-Query-to-get-the-b87ae694

Michael Seidl aka Techguy

4 thoughts on “System Center Service Manager–SQL Query to get a full List of your Incidents”

  1. Hello, thanks a lot for your query. Would it be possible to add the Resolved By field?

  2. Is there also a query for ServiceManager to retrieve Service Requests. This query you shared was amazing.

Leave a Comment

Your email address will not be published. Required fields are marked *

*