I've just inherited Kace from my new employer. I do not have a SQL background but I've been able to muddle my was on some basic reporting. One report I need is a 30 day (or whatever timeframe) metric report of assigned tech users closed ticket count. Just a count per each user, not a listing of all closed tickets. I've been searching a lot of the scripts but I'm not finding one that I can modify to work. Anybody have one already created? Thanks for the assist.

Closed Ticket for Techs

Assigned Tech's name #1  <closed ticket count>

Assigned Tech's name #2  <closed ticket count>

Assigned tech's name #3  <closed ticket count>

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

This is a bit of overkill, but it's one of our operational reports.  It lists technician, number of tickets open for more than 14 days, number of tickets closed in the last 7 days, number of ticket closed in the last 28 days, number of tickets closed in the last 56 days, assigned open tickets created in the last 7 days, and total open tickets.  These counts are regardless of queue.  You can adjust the dates and selection criteria to meet your specific needs.

SELECT 
    USER.USER_NAME AS Technician,
    (SELECT
            COUNT(a.ID)
        FROM
            HD_TICKET a
                LEFT JOIN
            HD_STATUS b ON a.HD_STATUS_ID = b.ID
        WHERE
            b.STATE <> 'closed'
                AND a.MODIFIED > DATE_SUB(NOW(), INTERVAL 14 DAY)
                AND a.OWNER_ID = USER.ID) AS 'Open_over_14_days',
    (SELECT
            COUNT(c.ID)
        FROM
            HD_TICKET c
                LEFT JOIN
            HD_STATUS d ON c.HD_STATUS_ID = d.ID
        WHERE
            d.STATE = 'closed'
                AND c.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
                AND c.OWNER_ID = USER.ID) AS 'Closed_last_7_days',
    (SELECT
            COUNT(e.ID)
        FROM
            HD_TICKET e
                LEFT JOIN
            HD_STATUS f ON e.HD_STATUS_ID = f.ID
        WHERE
            f.STATE = 'closed'
                AND e.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 28 DAY)
                AND e.OWNER_ID = USER.ID) AS 'Closed_last_28_days',
    (SELECT
            COUNT(g.ID)
        FROM
            HD_TICKET g
                LEFT JOIN
            HD_STATUS h ON g.HD_STATUS_ID = h.ID
        WHERE
            h.STATE = 'closed'
                AND g.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 56 DAY)
                AND g.OWNER_ID = USER.ID) AS 'Closed_last_56_days',
    (SELECT
            COUNT(i.ID)
        FROM
            HD_TICKET i
                LEFT JOIN
            HD_STATUS j ON i.HD_STATUS_ID = j.ID
        WHERE
            j.STATE <> 'closed'
                AND i.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
                AND i.OWNER_ID = USER.ID) AS 'Created_last_7_days',
    (SELECT
            COUNT(k.ID)
        FROM
            HD_TICKET k
                LEFT JOIN
            HD_STATUS l ON k.HD_STATUS_ID = l.ID
        WHERE
            l.STATE <> 'closed'
                AND k.OWNER_ID = USER.ID) AS 'Total_open_tickets'
FROM
    HD_TICKET
        LEFT JOIN
    USER ON HD_TICKET.OWNER_ID = USER.ID
GROUP BY USER.USER_NAME
ORDER BY USER.USER_NAME;


Answered 07/26/2016 by: grayematter
Fourth Degree Black Belt

  • Great..thanks! From this I think I can hack my own report now. Appreciate the assist!
Please log in to comment
Answer this question or Comment on this question for clarity