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?

Best Answer


  • Options
    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.


  • Options
    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.

  • Options
    Melody_DeguzmanMelody_Deguzman Customer IT Monkey ✭
    edited May 28

    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?

Sign In or Register to comment.