Hello guys! I'm a systems analyst and was given the K1000 as my project. I've gotten it tweaked and honed to work perfectly, but I have one thing that's missing: a good SLA report that I can send to management.

With our old system, we had a breakdown of the last three months and the tickets divided by Low, Medium, and High ticket level. Then, the average response time (from a "New" state to an "opened" state) and also the resolution time ("opened" state to "closed" state). 

You can see below what our old report showed:

I'm not so much interested in having a three-month view as seen above. A single month view would be sufficient. I'm not a database guy so my SQL knowledge is limited to what I learned in about two college courses, haha. Any help would be appreciated! Thanks in advance. 

Additional Info:

We currently have three Service Desk queues: service desk, technical, and application. I would want all the queues to be integrated into a single report, I wouldn't need a distinction between the three (i.e. all the tickets from all three queues could be meshed together). 

Our reports are generated monthly. I plan on generating these as scheduled monthly reports. 

 

 

5 Comments   [ + ] Show Comments

Comments

  • One of our reports that might help generates counts for tickets created, closed, first call resolution, and tickets open at run time. You could do something similar for your categories each month. With some work, you could get a report to look like your charts.

    SELECT
    metric, counts
    FROM
    ((select
    1 AS ORD,
    'Tickets_Created' as metric,
    count(HD_TICKET_CHANGE.ID) as counts
    from
    HD_TICKET_CHANGE
    inner join HD_TICKET ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
    where
    description like 'Ticket Created%' and timestamp > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2) union (select
    2 AS ORD,
    'Tickets_Closed' as metric,
    count(HD_TICKET.ID) as counts
    from
    HD_TICKET
    where
    TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2) union (select
    3 AS ORD,
    'Tickets_Currently_Open' as metric,
    count(HD_TICKET.ID) as counts
    from
    HD_TICKET
    inner join HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
    where
    HD_STATUS.STATE <> 'closed' and HD_TICKET.HD_QUEUE_ID <> 2) union (select
    4 AS ORD, 'FCR' as metric, COUNT(ID) AS counts
    from
    HD_TICKET
    WHERE
    CUSTOM_FIELD_VALUE2 = 'Yes' and TIME_CLOSED > date_sub(now(), interval 1 month) and HD_TICKET.HD_QUEUE_ID <> 2)) TMP
    ORDER BY ORD;

    One page that may be of assistance on aggregate functions is: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html
  • Since you want to make a report based on the state changing this is difficult because the HD_TICKET_CHANGE and the HD_TICKET_CHANGE_FIELD tables don't track that explicitly. If you were looking for explicit status names you could find the difference in time from when a ticket was opened until there was an entry in HD_TICKET_CHANGE like "Changed ticket status from "New" to "Opened"". You can actually find how long it took tickets to go from New to something else by putting a wildcard after "New".

    On the other end, if you only have one closed state, e.g. "Closed" you could search for changes there "Changed ticket status from % to "Closed"" but of course finding the time to start from is difficult since we would need to corresponding time that it went to the previous state.

    As I was playing with this I did create an interesting query that may help:
    SELECT CF.*, C.TIMESTAMP, STATBEFORE.NAME, STATBEFORE.STATE, STATAFTER.NAME, STATAFTER.STATE
    FROM ORG1.HD_TICKET_CHANGE_FIELD CF
    JOIN HD_TICKET_CHANGE C on C.ID = CF.HD_TICKET_CHANGE_ID
    JOIN HD_STATUS STATBEFORE on STATBEFORE.ID = CF.BEFORE_VALUE
    JOIN HD_STATUS STATAFTER on STATAFTER.ID = CF.AFTER_VALUE
    WHERE FIELD_CHANGED = "HD_STATUS_ID"

    This shows when the status changed on tickets and what the status and state changes were. You could possibly be able to combine some of this data with other ticket data to get what you're looking for. I might put some more thought into this and I'll post anything if I come up with something.
  • Thanks guys! I'll be able to take what both of you have given me and use it for my reports! This is what I found in the forum that's been closest to what I've wanted:

    SELECT

    (case when
    TIME_OPENED<DATE_ADD(IF(CREATED='0',TIME_OPENED,CREATED),
    INTERVAL 1 HOUR) then '0-1 hour'
    when
    TIME_OPENED<DATE_ADD(IF(CREATED='0',TIME_OPENED,CREATED), INTERVAL
    24 HOUR) then '1-24 hours'
    when
    TIME_OPENED>DATE_ADD(IF(CREATED='0',TIME_OPENED,CREATED),INTERVAL
    24 HOUR) then '>24 hours'
    else 'error' end ) as OPEN_GROUP,

    count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id)
    div 86400),'d ',
    TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_RESPONSE_TIME from HD_TICKET,
    HD_STATUS
    where
    HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
    and HD_STATUS.NAME='opened'
    and TIME_OPENED<>0
    and TIME_OPENED>'2014-01-01' /*change the start date here*/
    and TIME_OPENED<'2014-01-30' /*change the end date here*/
    and HD_TICKET.HD_QUEUE_ID in (1,2,3) /*add queue numbers here*/
    group by OPEN_GROUP

    UNION
    select 'all' as OPEN_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id)
    div 86400),'d ',
    TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id)
    mod 86400 ), '%kh %im %ss')) as AVG_RESPONSE_TIME from HD_TICKET
    ,HD_STATUS
    where
    HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
    and HD_STATUS.NAME='opened'
    and TIME_OPENED<>0
    and TIME_OPENED>'2014-01-01' /*change the start date here*/
    and TIME_OPENED<'2014-01-30' /*change the end date here */
    and HD_TICKET.HD_QUEUE_ID in (1,2,3) /*add queue numbers here*/
    group by OPEN_GROUP


    But instead of grouping by time opened, I'd want it grouped by the three critical levels (Low, Medium, and High). I think this query above takes the difference between time opened and time created (which will give me response time) and I can easily change Time_Opened and Created to Time_Opened and Time_Closed which will give me resolution time.

    Please let me know if this won't work or if you have any ideas how to morph the grouping from Time_Opened to Criticality, like I wanted.

    Thanks again for all the help! I really do appreciate it.
  • Ok Guys, I started using MySQL workbench and I came up with the following:

    SELECT HD_PRIORITY_ID as PRIORITY,
    count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id)
    div 86400),'d ',
    TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_RESPONSE_TIME from HD_TICKET,
    HD_STATUS
    where
    HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
    and HD_STATUS.NAME='closed'
    and TIME_CLOSED<>0
    and TIME_CLOSED>'2014-03-01' /*change the start date here*/
    and TIME_CLOSED<'2014-03-30' /*change the end date here*/
    and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
    group by PRIORITY

    It works to a certain degree. The only problem is with the where statement because it only includes tickets that are closed. I wish there was also a way to include currently open tickets, but I know that's going to create a whole new set of problems.

    Thoughts? Ideas? Critiques?
    • I'm not a fan of hard coded dates so I would normally use something like:
      MONTH(TIME_CLOSED) = MONTH(NOW()) for this month or
      MONTH(TIME_CLOSED) = MONTH(NOW()) -1 for the previous month
  • That's perfect actually; I can use it with that code and do scheduled monthly reports without changing a thing. Thanks Chuck!
    • SELECT HD_PRIORITY_ID as PRIORITY,
      avg(NULLIF(HD_TICKET.SATISFACTION_RATING,0)) as Average_Satisfaction_Rating,
      count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
      CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id)
      div 86400),'d ',
      TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_OPENED,IF(CREATED='0',TIME_OPENED,CREATED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_RESPONSE_TIME from HD_TICKET,
      HD_STATUS
      where
      HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
      and HD_STATUS.NAME='closed'
      and TIME_CLOSED<>0
      and MONTH(TIME_CLOSED) = MONTH(NOW())
      and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
      group by PRIORITY
      order by PRIORITY

      This is what it looks like right now. I added the average satisfaction rating as per my VPs request; thank God for the null if statement (I think I used it right). Do you guys think this'll work or am I missing something glaringly obvious that a newbie like me can't see?
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity