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
Hello, thanks a lot for your query. Would it be possible to add the Resolved By field?
Will try to add asap
Is there also a query for ServiceManager to retrieve Service Requests. This query you shared was amazing.
i got some, put not ready to publish