SQL Query for Support Group routing

Answers

• Options

  Brett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭

  September 2020

  @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

  0
• Options

  Dean_Lintern Customer IT Monkey ✭

  September 2020

  @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.

  0
• Options

  Brett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭

  September 2020 Answer ✓

  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

  5