SQL Query for Support Group routing

Dean_Lintern

Our service desk has asked us if they can have an SQL query that will show when a work item is moved from one support group to another on IR and SR.

So I have been looking around the history 'property changes'

Property: Old Value---->New Value

Has anyone got anything similar based around this query?

Brett_Moffett

The SQL properties for the history logs are held in the Entity Change Log.

You would need to link this to the Work Item and find the result when the change is not carried out by the service account.

I've not created the exact query before but here is one that I have that shows when a ticket has been reopened.

SELECT DISTINCT WI.WorkItemId, WI.Title, WI.AssignedUser, IR.System_WorkItemRelatesToRequestOffering_DisplayName [Type], WI.AffectedUser, CIUser.Office, CIUser.Department, WI.Created

FROM WorkItem WI

INNER JOIN [ServiceManager].[dbo].[MT_System$WorkItem$Incident_Log] ILog ON 

ILog.BaseManagedEntityId = WI.Id

LEFT OUTER JOIN [ServiceManager].[dbo].[DisplayStringView] PreStatusDS ON

PreStatusDS.LTStringId = ILog.Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED AND PreStatusDS.LanguageCode = 'ENU'

LEFT OUTER JOIN [ServiceManager].[dbo].[DisplayStringView] PostStatusDS ON

PostStatusDS.LTStringId = ILog.Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED AND PostStatusDS.LanguageCode = 'ENU'

LEFT OUTER JOIN cachert.csnCached_MT_System_WorkItem_Incident IR ON

IR.Base_Name = WI.WorkItemId

LEFT OUTER JOIN CI$User CIUser ON

CIUser.Id = WI.AffectedUserId

WHERE PreStatusDS.DisplayName = 'Resolved'

AND @createdFilter

Order By WorkItemId

Brett_Moffett

@Dean_Lintern

What are you trying to achieve with this SQL query?

Do you want to show how many times a single ticket gets re-assigned to different support groups?

Do you want to show how many WI's get re-assigned at any point in their life? (Once or more)

Do you want to show how many times a given team re-assigns tickets to a specific team?

The key with reporting is knowing what outcome you are trying to achieve, setting goals or targets, having a plan in place for when that goal\target is met\exceeded\breached etc. and then writing a report to achieve this.

For example: If you believe that a given team is reassigning too many WI's away from themselves and you want to show the numbers for this,

• How does this impact business in a negative way?
• How many reassignments is too many?
• What are you going to do if that number is breached?

Linking the WorkItem table with the Entity Change log table is not overtly difficult but it depends on what sort of data you are wanting out of it that will change the approach to the query.

Let us know what you wish to achieve with this report and lets see if we can't build a good SQL query for this together.

Brett

Dean_Lintern

@Brett_Moffett Hi Brett, thank you for your response!

In relation to the goals/targets etc, we aren't too concerned with the answers to the questions you have posed. Its just our job to supply the data and the Service Desk manager will decide on the outcomes.

What he has asked for is a table of all requests that are moved from point A to point B.

We have a Support Group called 'New', the service desk work all tickets from this pot, and either close off ticket, if they have the resources to do so. Or they triage to another support group to complete the IR/SR.

So SQL would be any movement from the 'New' queue to any other support group (we have about 25 in place) but the caveat is only if moved manually. So would need a user not a workflow/service account record, as we use post approval support group extensions and have some ticket auto assigned out of that queue.

I noticed in the history, it logs this data under

'Property Changes'

And was trying to find in SQL Property: Old Value---->New Value

without any success currently.

Brett_Moffett

The SQL properties for the history logs are held in the Entity Change Log.

You would need to link this to the Work Item and find the result when the change is not carried out by the service account.

I've not created the exact query before but here is one that I have that shows when a ticket has been reopened.

SELECT DISTINCT WI.WorkItemId, WI.Title, WI.AssignedUser, IR.System_WorkItemRelatesToRequestOffering_DisplayName [Type], WI.AffectedUser, CIUser.Office, CIUser.Department, WI.Created

FROM WorkItem WI

INNER JOIN [ServiceManager].[dbo].[MT_System$WorkItem$Incident_Log] ILog ON 

ILog.BaseManagedEntityId = WI.Id

LEFT OUTER JOIN [ServiceManager].[dbo].[DisplayStringView] PreStatusDS ON

PreStatusDS.LTStringId = ILog.Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED AND PreStatusDS.LanguageCode = 'ENU'

LEFT OUTER JOIN [ServiceManager].[dbo].[DisplayStringView] PostStatusDS ON

PostStatusDS.LTStringId = ILog.Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED AND PostStatusDS.LanguageCode = 'ENU'

LEFT OUTER JOIN cachert.csnCached_MT_System_WorkItem_Incident IR ON

IR.Base_Name = WI.WorkItemId

LEFT OUTER JOIN CI$User CIUser ON

CIUser.Id = WI.AffectedUserId

WHERE PreStatusDS.DisplayName = 'Resolved'

AND @createdFilter

Order By WorkItemId

Melody_Deguzman

Piggy backing off of this post. Hello @Brett_Moffett looks like you are an SQL guru where I am definitely not but Management is requesting a query to list IRs and SRs in a particular month that shows when it was assigned and if it was reassigned to the same group again. Is that possible?