Home› General Discussion
Options

Reporting Question

Kevin_GoodwinKevin_Goodwin Customer Advanced IT Monkey ✭✭✭
in General Discussion
We recently had a senior management change that has required us to provide some detailed historical information from our tickets. While I am excited about senior management wanting to know our ticketing metrics, I am struggling with how to provide the information they are looking for. Specifically they want the following:
-2017 Average time to resolution
-2017 Tickets that remained open longer than 60 days
-2017 SLA's met vs breached
Going forward I will need to provide this data on a weekly basis as well, but I figure once I find out how to get it for last year, I should be able to adjust date filters and get the data I need. My big problem right now is figuring out how to get to this data now.
(FYI - My SQL skills are quite limited, but I do have a SQL engineer who can help if needed.)

Best Answer

Answers

  • Options
    Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    This is a constant struggle defining time, with my environment having so many different management teams no one sees time the same way. As some refer to the difference between opened->completed others want to look at time worked (billing time)
    Some things you want to define up front to make sure your management agrees before working out the SQL query logic.
    -2017 Average time to resolution
    --What are they looking for the average of? Just the difference of create date to completed date? or do they want you to consider business days, or on top of that also business hours.
    Example being a SR is opened Friday and Noon and Closed Monday at Noon. If you are closed weekends and a 9-5 shop. Opened/Completed would return a 48hrs value. Business days would return 24 hours, and business hours 8hrs. Makes a big difference if they attempt to use those numbers in relationship to staffing hours.

    -2017 Tickets that remained open longer than 60 days
    --Again same thing about business days

    --SLA
    Unless you are looking for a query from the warehouse.
    HTH
  • Options
    Kevin_GoodwinKevin_Goodwin Customer Advanced IT Monkey ✭✭✭

    Hi Brian,

    Luckily, I only have one management group asking me at the moment, so I can start with one way and steer future groups to look at things the same way.

    I hadn't thought about the different ways time would come across, so I need to verify that, but right now let's say they wanted business hours.

    I think I am going to need all of the 2017 info from the warehouse as we are only retaining data for 270 days across all work items. I also have the added benefit of only IR's being looked at for SLA's so I'm hoping that simplifies things.

  • Options
    Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Business hours is the biggest challenge metric to pull, especially if you do not have an SLA tracking resolution time. You would have to build into your query rules for each day to only count the hours that the business is open.
    Basically you would task the SQL engineer to run a calculation of the time difference between create date and completed date, at the same time the query would only count the hours the business is open. So back to my example of Mon-Fri 9am-5pm where it would have to be Ticket IR123 opened Friday noon and completed Mon at noon. The query would have to calculate Fri, = 5 Sat = 0, Sun = 0, & Mon = 4 for a total of 9hrs Weekends are not to bad to figure this out, its holidays that really clutter the query.
    HTH
  • Options
    Kevin_GoodwinKevin_Goodwin Customer Advanced IT Monkey ✭✭✭

    Thank you for your help Brian. I am going to get with my SQL guy and see if we can make this happen.

    How would you go about getting the SLA's? We are basically just looking to get if they breached or were met, for example, I need to be able to say we had 100 total IR's this week, 80 were completed within SLA. Additionally I might add a % breach, but that should be easy to calculate once I can find out how many were completed that met their SLA.

  • Options
    Kevin_GoodwinKevin_Goodwin Customer Advanced IT Monkey ✭✭✭
    Thanks again Brian! Happy Thanksgiving!
Sign In or Register to comment.