Home Service Manager

SQL Query for Support Group routing

Dean_LinternDean_Lintern Customer IT Monkey ✭

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_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭


    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.


  • Dean_LinternDean_Lintern Customer IT Monkey ✭

    @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_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭

    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


    CIUser.Id = WI.AffectedUserId

    WHERE PreStatusDS.DisplayName = 'Resolved'

    AND @createdFilter

    Order By WorkItemId

Sign In or Register to comment.