Get Incidents Containing a Specific Keyword from SCSM

SQL query in this post will get all incidents containing “print” keyword, along with analyst comments from Service Manager operational database. Comments are not available in data warehouse by default.

This can be helpful in doing an ad hoc analysis of a particular type of issues independent of Service Manager interface. Especially when the incidents are not already categorised in SM. Looking at number of incidents and “Resolved by Analyst” resolution category alone, one can know there is a problem. But what if you want to create a report on number of incidents under different type of problems and resolutions categories. Like driver issue, paper jam, hardware issue, spooler reset, vendor fixed hardware, refill, etc. You assign such categories to incidents in Excel and then create a report in PowerBI.

Refer this blog post if you are new to writing queries for Service Manager.

/****** Script to get list of incidents along with analyst comments including "print" keyword. ******/
SELECT I.[BaseManagedEntityId] [Entity ID]
,I.[DisplayName] [Display Name]
,I.[Title_9691DD10_7211_C835_E3E7_6B38AF8B8104] Title
,I.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] ID
,I.[Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B] [Description]
,I.[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] [Created On]
,I.[Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794] [Priority]
--,[ResolutionCategory_EF7FA7DA_30A1_8B5E_58D5_236E93BB765F]
,I.[ResolutionDescription_85E8B5FA_3ECB_9B6C_0A02_A8C9EC085A39] [Resolution Description]
,I.[ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E] [Resolved On]
,R.RelationshipId
--,r.RelationshipTypeId
,rt.RelationshipTypeName
,AC.Comment_AECE7B96_331B_498C_2F77_AEA4376EFFF1 [Comment]
,AC.EnteredDate_4E8F8D74_6B59_27B4_325B_60E901FFA4F6 [Entered On]
,AC.EnteredBy_A14C9A28_2F82_D1F2_BCCD_A4E3745179B5 [Entered By]
FROM [sm].[dbo].[MTV_System$WorkItem$Incident] AS I
Left Join [sm].dbo.Relationship AS R ON I.BaseManagedEntityId = r.SourceEntityId AND R.RelationshipTypeId = '835A64CD-7D41-10EB-E5E4-365EA2EFC2EA'
Left join sm.dbo.RelationshipType AS RT ON r.RelationshipTypeId = rt.RelationshipTypeId
Left Join sm.dbo.MTV_System$WorkItem$TroubleTicket$AnalystCommentLog AS AC ON R.TargetEntityId = AC.BaseManagedEntityId
WHERE I.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 like '%print%'
ORDER BY I.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 DESC, I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C, ac.EnteredDate_4E8F8D74_6B59_27B4_325B_60E901FFA4F6 ASC

About Dinesh Sharma

Experienced system architect, programmer, and trainer. This blog is a way of giving back and helping the community. So feel free to ask a question or to leave a comment.