Best Practices

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 - grayematter 10 years ago

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. - chucksteel 10 years ago

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. - dalton.blanchard 10 years ago

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? - dalton.blanchard 10 years ago

That's perfect actually; I can use it with that code and do scheduled monthly reports without changing a thing. Thanks Chuck! - dalton.blanchard 10 years ago

Hi Dalton,

Could you share the final sql coding result you got it working for your K1000 SLA reporting?

I am not a SQL expert and as same as you have given the task to create one for our company.


Thank you in advance for your support! - melcramn 7 years ago