Home› Analytics

SQL query to find the user that resolved/completed the work item

Philip_HåkansPhilip_Håkans Customer IT Monkey ✭
in Analytics

Hello everyone,

I'm trying to write an SQL query to get the user that resolved/completed a work item. (IR and SR)

I've looked around and saw some tips that ServiceManager.dbo.MT_System$WorkItem$Incident_Log contains the records of when the status changed from $notResolved/$notCompleted to $resolved/$completed and then somehow link that EntityChangeLogId to the user who made the change.

But it's that last part that I am struggling with. Does anyone have an SQL example of this? Or perhaps someone could point me in the right direction.

To summarize: I want to have a column that gives me the name of the agent that solved that particular WorkItem, be it SR or IR. So we have to look for when the WorkItem changed to Completed or Resolved and who made that change.

My knowledge of SQL is basic at best so I would really appreciate some help :)!

Answers

  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    edited May 2020

    This should get you started. It selects all IRs created in the past 7 days

    SELECT Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID]
, Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS [Title]
, AffectedUser.DisplayName AS [Affected User]
, SC.DisplayName AS [Classification]
, ST.DisplayName AS [Status]
, SG.DisplayName AS [Support Group]
, AssignedToUser.DisplayName AS 'Assigned User'
, CONVERT(nvarchar(24),CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688,105) AS 'Created Date'
, CONVERT(nvarchar(24),ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E,105) AS 'Resolved Date'
FROM MT_System$WorkItem$Incident WITH (NOLOCK)
-- Assigned to User
INNER JOIN [ServiceManager].[dbo].[Relationship] AssignedToUserRel WITH (NOLOCK)
ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AssignedToUserRel.[SourceEntityId]
AND AssignedToUserRel.[RelationshipTypeId] = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AssignedToUser WITH (NOLOCK)
ON AssignedToUserRel.[TargetEntityId] = AssignedToUser.[BaseManagedEntityId]
-- Affected User
INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel WITH (NOLOCK)
ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId]
AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser WITH (NOLOCK)
ON AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]
-- Support Group
INNER JOIN EnumType ET1 WITH (NOLOCK)
ON [MT_System$WorkItem$Incident].TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = ET1.EnumTypeId
INNER JOIN DisplayStringView SG WITH (NOLOCK)
ON MT_System$WorkItem$Incident.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = SG.MPElementId
-- Status
INNER JOIN EnumType ET2 WITH (NOLOCK)
ON [MT_System$WorkItem$Incident].Status_785407A9_729D_3A74_A383_575DB0CD50ED = ET2.EnumTypeId
INNER JOIN DisplayStringView ST WITH (NOLOCK)
ON MT_System$WorkItem$Incident.Status_785407A9_729D_3A74_A383_575DB0CD50ED = ST.MPElementId
-- Classification
INNER JOIN EnumType ET3 WITH (NOLOCK)
ON [MT_System$WorkItem$Incident].Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = ET3.EnumTypeId
INNER JOIN DisplayStringView SC WITH (NOLOCK)
ON MT_System$WorkItem$Incident.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = SC.MPElementId
WHERE
SG.LanguageCode = 'ENU'
AND ST.LanguageCode = 'ENU'
AND SC.LanguageCode = 'ENU'
AND DATEDIFF (DAY, [CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688], CURRENT_TIMESTAMP) < 7
ORDER BY Id_9A505725_E2F2_447F_271B_9B9F4F0D190C 


  • Philip_HåkansPhilip_Håkans Customer IT Monkey ✭

    Thanks for the reply!

    It did get me on the way!

    Is there any way to combine this with both service requests and incidents? My boss wants to see how many total cases are resolved by his agents each week.

    My current query looks like this (modified yours a little bit):

    SELECT DISTINCT Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID]
, Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS [Title]
, SC.DisplayName AS [Classification]
, ST.DisplayName AS [Status]
, SG.DisplayName AS [Support Group]
, ResolvedByUser.DisplayName AS 'Resolved by user'
, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 AS 'Created Date'
, ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E AS 'Resolved Date'
, DATEPART(ISO_WEEK, ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E) AS 'Week resolved'
FROM MT_System$WorkItem$Incident WITH (NOLOCK)
-- Assigned to User
INNER JOIN [ServiceManager].[dbo].[Relationship] ResolvedByUserRel WITH (NOLOCK) ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = ResolvedByUserRel.[SourceEntityId]
AND ResolvedByUserRel.[RelationshipTypeId] = 'f7d9b385-a84d-3884-7cde-e2c926d931a5'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] ResolvedByUser WITH (NOLOCK)
ON ResolvedByUserRel.[TargetEntityId] = ResolvedByUser.[BaseManagedEntityId]
-- Affected User
INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel WITH (NOLOCK)
ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId]
AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser WITH (NOLOCK)
ON AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]
-- Support Group
INNER JOIN EnumType ET1 WITH (NOLOCK)
ON [MT_System$WorkItem$Incident].TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = ET1.EnumTypeId
INNER JOIN DisplayStringView SG WITH (NOLOCK)
ON MT_System$WorkItem$Incident.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = SG.MPElementId
-- Status
INNER JOIN EnumType ET2 WITH (NOLOCK)
ON [MT_System$WorkItem$Incident].Status_785407A9_729D_3A74_A383_575DB0CD50ED = ET2.EnumTypeId
INNER JOIN DisplayStringView ST WITH (NOLOCK)
ON MT_System$WorkItem$Incident.Status_785407A9_729D_3A74_A383_575DB0CD50ED = ST.MPElementId
-- Classification
INNER JOIN EnumType ET3 WITH (NOLOCK)
ON [MT_System$WorkItem$Incident].Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = ET3.EnumTypeId
INNER JOIN DisplayStringView SC WITH (NOLOCK)
ON MT_System$WorkItem$Incident.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = SC.MPElementId
WHERE
SG.LanguageCode = 'ENU'
AND ST.LanguageCode = 'ENU'
AND SC.LanguageCode = 'ENU'
AND YEAR(CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688) = 2020
AND DATEPART(ISO_WEEK, ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E) = 18
AND SG.DisplayName IN ('Beställningar', 'Behörighet', 'IT-akuten', 'Leveranser', 'Onsite', 'Tibro Onsite', 'Skrivare', 'Telefoni', 'Hjo')
ORDER BY Id_9A505725_E2F2_447F_271B_9B9F4F0D190C 
  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    edited May 2020

    @Philip_Håkans

    What I did is to create a view that uses an union to merge both IRs and SR's

    It is probably some dirty SQL methods, but it works.

    Just one question do you have a datawarehouse? If you do it is prefered to use that for reporting.

    The only reason why I'm using the live DB is because the managers want real time data and the cireson portal tables does not have my extended classes in.

    You can just remove the extended classes for Campus.

    I like to use this view because when writing reports, it makes it much easier to reference all the fields I need

    Strap on, this is a loooong query :-)

    USE [ServiceManager]
GO

/****** Object:  View [dbo].[WorkitemLiveSummaryView]    Script Date: 2020/05/07 11:01:36 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[WorkitemLiveSummaryView] 
AS
SELECT
IRSR.[ID],
IRSR.[Title],
IRSR.[AffectedUser],
IRSR.[Campus],
IRSR.[Classification],
IRSR.[Status],
IRSR.[SupportGroup],
IRSR.[AssignedUser],
IRSR.[CreatedDate],
IRSR.[ResolvedDate]
FROM(
SELECT
SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID],
SR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS [Title],
AffectedUser.DisplayName AS [AffectedUser],
DSCAMP.DisplayName AS [Campus],
SC.DisplayName AS [Classification],
ST.DisplayName AS [Status],
SG.DisplayName AS [SupportGroup],
AssignedToUser.DisplayName AS [AssignedUser],
SR.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 AS [CreatedDate],
SR.CompletedDate_3116AB82_745B_654E_6B48_74E8AAF48065 AS [ResolvedDate]
FROM MT_System$WorkItem$ServiceRequest SR WITH (NOLOCK)
-- Campus
INNER JOIN MT_ClassExtension_8356c9c8_4e0d_4eb0_bae9_7e1c937dd9e3 CAMP WITH (NOLOCK)
ON SR.BaseManagedEntityId = CAMP.BaseManagedEntityId
INNER JOIN DisplayStringView DSCAMP WITH (NOLOCK)
ON CAMP.NWUCampusList_5C47EA01_E264_2284_3F03_DC873A1AAA96 = DSCAMP.LTStringId
-- Assigned to User
INNER JOIN [Relationship] AssignedToUserRel WITH (NOLOCK)
ON SR.[BaseManagedEntityId] = AssignedToUserRel.[SourceEntityId]
AND AssignedToUserRel.[RelationshipTypeId] = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
INNER JOIN [MT_System$Domain$User] AssignedToUser WITH (NOLOCK)
ON AssignedToUserRel.[TargetEntityId] = AssignedToUser.[BaseManagedEntityId]
-- Affected User
INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel WITH (NOLOCK)
ON SR.[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId]
AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser WITH (NOLOCK)
ON AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]
-- Support Group
INNER JOIN EnumType ET1 WITH (NOLOCK)
ON SR.SupportGroup_766D07C2_2680_CD38_4638_D3A7813E6488 = ET1.EnumTypeId
INNER JOIN DisplayStringView SG WITH (NOLOCK)
ON SR.SupportGroup_766D07C2_2680_CD38_4638_D3A7813E6488 = SG.MPElementId
-- Status
INNER JOIN EnumType ET2 WITH (NOLOCK)
ON SR.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = ET2.EnumTypeId
INNER JOIN DisplayStringView ST WITH (NOLOCK)
ON SR.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = ST.MPElementId
-- Classification
INNER JOIN EnumType ET3 WITH (NOLOCK)
ON SR.Area_0E2739F7_6889_09EF_B260_35CD954D3563 = ET3.EnumTypeId
INNER JOIN DisplayStringView SC WITH (NOLOCK)
ON SR.Area_0E2739F7_6889_09EF_B260_35CD954D3563 = SC.MPElementId
WHERE
SG.LanguageCode = 'ENU'
AND DSCAMP.LanguageCode = 'ENU'
AND ST.LanguageCode = 'ENU'
AND SC.LanguageCode = 'ENU'


UNION ALL


SELECT
IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID],
IR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS [Title],
AffectedUser.DisplayName AS [AffectedUser],
DSCAMP.DisplayName AS [Campus],
SC.DisplayName AS [Classification],
ST.DisplayName AS [Status],
SG.DisplayName AS [SupportGroup],
AssignedToUser.DisplayName AS [Assigned User],
IR.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688  AS [CreatedDate],
IR.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E AS [ResolvedDate]
FROM MT_System$WorkItem$Incident IR WITH (NOLOCK)
-- Campus
INNER JOIN MT_ClassExtension_2285e5a9_43d1_4530_a1ae_36de68a59e31 CAMP WITH (NOLOCK)
ON IR.BaseManagedEntityId = CAMP.BaseManagedEntityId
INNER JOIN DisplayStringView DSCAMP WITH (NOLOCK)
ON CAMP.NWUCampusList_87D19FCE_090D_AF75_88C2_38E8FB52DBCB = DSCAMP.LTStringId
-- Assigned to User
INNER JOIN [Relationship] AssignedToUserRel WITH (NOLOCK)
ON IR.[BaseManagedEntityId] = AssignedToUserRel.[SourceEntityId]
AND AssignedToUserRel.[RelationshipTypeId] = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
INNER JOIN [MT_System$Domain$User] AssignedToUser WITH (NOLOCK)
ON AssignedToUserRel.[TargetEntityId] = AssignedToUser.[BaseManagedEntityId]
-- Affected User
INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel WITH (NOLOCK)
ON IR.[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId]
AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser WITH (NOLOCK)
ON AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]
-- Support Group
INNER JOIN EnumType ET1 WITH (NOLOCK)
ON IR.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = ET1.EnumTypeId
INNER JOIN DisplayStringView SG WITH (NOLOCK)
ON IR.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = SG.MPElementId
-- Status
INNER JOIN EnumType ET2 WITH (NOLOCK)
ON IR.Status_785407A9_729D_3A74_A383_575DB0CD50ED = ET2.EnumTypeId
INNER JOIN DisplayStringView ST WITH (NOLOCK)
ON IR.Status_785407A9_729D_3A74_A383_575DB0CD50ED = ST.MPElementId
-- Classification
INNER JOIN EnumType ET3 WITH (NOLOCK)
ON IR.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = ET3.EnumTypeId
INNER JOIN DisplayStringView SC WITH (NOLOCK)
ON IR.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = SC.MPElementId
WHERE
SG.LanguageCode = 'ENU'
AND DSCAMP.LanguageCode = 'ENU'
AND ST.LanguageCode = 'ENU'
AND SC.LanguageCode = 'ENU'
)IRSR

GO


  • David_JohnsenDavid_Johnsen Customer Advanced IT Monkey ✭✭✭

    @Philip_Håkans Hey Philip, did you get sorted with this query. Its exactly what my manager needs too!

  • Philip_HåkansPhilip_Håkans Customer IT Monkey ✭

    @David_Johnsen

    I'm afraid I never got sorted with this query. I know my way around some basic SQL but I couldn't transform Gerhards query into something that I could present to my manager.

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Is the SCSM operational DB your only choice? Or is the SCSM DW or Cireson Analytics on the table?

  • Philip_HåkansPhilip_Håkans Customer IT Monkey ✭

    Operational or Cireson Analytics (we don't have a DW), if the data can be retrieved from these it does not matter from where. It's been a while since I last attempted this, but I think I remember facing the issue of there not being a "resolved by" relationship to find what agent actually solved the case. Only who was assigned to it when it was closed. But I may be wrong, like I said it's been a while 😊

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited August 2020

    There actually is another option on the table worth exploring - our Platform's OData tables.

    You can retrieve the OData through the Platform's API via Excel or directly query it in your ServiceManagement database. These tables typically start with "cachert*" and if you're syncing Incidents into the platform the Resolved By, plain text no joins required display name, is there.

    If you're like to see a preview of it in your respective deployment, just update the following URL:

    http://portal.domain.tld/platform/api/Cached_MT_System_WorkItem_Incident


    The following Knowledge Article also outlines how to configure Excel OData connections.

  • Philip_HåkansPhilip_Håkans Customer IT Monkey ✭

    Doesn't look like we're syncing MT_System$WorkItem$Incident, but I guess it's just as simple as adding it?

    image.png


  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    You would be 100% correct in that statement.

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    I would advise just adding MT_System$WorkItem

    This is because that table is a starts with, that way you can get all the other workitem tables in without the extra work :-)

    Thanks,

    Shane

Sign In or Register to comment.