/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


KACE - Avg Ticket Time report help

07/24/2017 896 views
Ok I am not good with SQL but I managed to modify someone elses report to only show the # of tickets for each queue and the avg close time. My question is, how do I get the report to show the name of the queue instead of the queue ID?

Here is the report query:
SELECT
HD_TICKET.HD_QUEUE_ID as QUEUE NAME,
count(HD_TICKET.ID) AS NUMBER_OF_TICKETS,

    IF (
        DATE(TIME_OPENED) = DATE(TIME_CLOSED),
        CONCAT(ROUND((SUM(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, IF (TIME_OPENED = '0', TIME_CLOSED, TIME_OPENED)))) / COUNT(HD_TICKET.ID) MOD 86400) / 3600, 1), ' Hours'),
        CONCAT((GREATEST(0, ROUND(SUM(5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - (
                        SELECT
                            COUNT(*)
                        FROM
                            HD_SLA_HOLIDAYS
                        WHERE
                            (WEEKDAY(VALUE) < 5)
                        AND (
                        VALUE BETWEEN TIME_OPENED AND TIME_CLOSED
                        )
                    ))/COUNT(HD_TICKET.ID), 1))), ' Days')
        ) AS AVG_TIME_TO_CLOSE
    
FROM
    HD_TICKET
LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID

WHERE
HD_STATUS. NAME = 'Closed'
AND TIME_CLOSED <> 0
AND TIME_CLOSED > '2017-05-01' /*change the start date here*/
AND TIME_CLOSED < '2017-12-01' /*change the end date here*/
        AND HD_TICKET.HD_QUEUE_ID IN (1,2) /*add queue numbers here separated by commas*/

GROUP BY
HD_TICKET.HD_QUEUE_ID
The report looks like this:

# Hd Queue Id Number Of Tickets Avg Time To Close
1 1                 133                         1.1 Days
2 2                 44                         3.8 Days

Under "HD Queue ID" I would like it to show an actual queue name (IT HELP DESK, ETC).

This is probably really easy but I have no clue lol. I know just enough to be dangerous. Thanks in advance!

EDIT:

OK I was able to get the query working by adding a CASE to the select as follows:

SELECT
CASE
WHEN HD_TICKET.HD_QUEUE_ID = 1 THEN 'IT Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 2 THEN 'BAIT Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 10 THEN 'Accounting Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 8 THEN 'Card ServicesHelpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 7 THEN 'Facilities Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 11 THEN 'HR Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 3 THEN 'Marketing Requests'
WHEN HD_TICKET.HD_QUEUE_ID = 12 THEN 'Meridian Link Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 4 THEN 'PDC Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 6 THEN 'Reporting Helpdesk'
ELSE 'unknown'
END AS 'Queue Name',


That now generates this:

# Queue Name Number Of Tickets Avg Time To Close
1 IT Helpdesk                         143 1.0 Days
2 BAIT Helpdesk                   45 3.8 Days
3 Marketing Requests             8 12.6 Days
4 Facilities Helpdesk                     7
5 Meridian Link Helpdesk             3 0.3 Days

Now I am having a new Issue.

1- The facilities helpdesk line doesn't have an avg time to close, weird.
2- If I change the date (AND TIME_CLOSED < '2017-12-01') to '2017-6-17' then I only get 1 results and I know thats not right.



Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
The queue's name is stored in the HD_QUEUE table, so you need to create a join to that table:
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
This line goes with the other join statements. 

With that in place you can select columns from the HD_QUEUE table, like the queue's name:
HD_QUEUE.NAME as "Queue Name"
This line goes with the other selected columns. 

Your complete SQL statement should look like this:
SELECT
HD_TICKET.HD_QUEUE_ID as "Queue ID",
HD_QUEUE.NAME as "Queue Name",
count(HD_TICKET.ID) AS NUMBER_OF_TICKETS,
    IF (
        DATE(TIME_OPENED) = DATE(TIME_CLOSED),
        CONCAT(ROUND((SUM(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, IF (TIME_OPENED = '0', TIME_CLOSED, TIME_OPENED)))) / COUNT(HD_TICKET.ID) MOD 86400) / 3600, 1), ' Hours'),
        CONCAT((GREATEST(0, ROUND(SUM(5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - (
                        SELECT
                            COUNT(*)
                        FROM
                            HD_SLA_HOLIDAYS
                        WHERE
                            (WEEKDAY(VALUE) < 5)
                        AND (
                        VALUE BETWEEN TIME_OPENED AND TIME_CLOSED
                        )
                    ))/COUNT(HD_TICKET.ID), 1))), ' Days')
        ) AS AVG_TIME_TO_CLOSE
    
FROM
    HD_TICKET
LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE
HD_STATUS. NAME = 'Closed'
AND TIME_CLOSED <> 0
AND TIME_CLOSED > '2017-05-01' /*change the start date here*/
AND TIME_CLOSED < '2017-12-01' /*change the end date here*/
        AND HD_TICKET.HD_QUEUE_ID IN (1,2) /*add queue numbers here separated by commas*/
GROUP BY
HD_TICKET.HD_QUEUE_ID

I also changed the alias for the queue ID.

Answered 07/26/2017 by: chucksteel
Red Belt

  • Much more elegant than my fix lol.
  • One more question if I may. The 2nd CONCAT statement. If I want it to show HOURS instead of DAYS, how would I do that? I tried modifying it and couldnt get it to work.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ