/build/static/layout/Breadcrumb_cap_w.png

K1000 Closed Ticket Metrics Script

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

Answers (1)

Posted by: grayematter 7 years ago
5th Degree Black Belt
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;



Comments:
  • Great..thanks! From this I think I can hack my own report now. Appreciate the assist! - jechols 7 years ago
 
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