SQL Query Examples

SQL Query Examples#

Eight complete queries covering the most common Znuny reporting use cases. All queries have been tested against a live Znuny database. Run them in Admin → SQL Box or via a direct database connection.

Note

All queries filter archive_flag = 0 to exclude archived tickets unless the example specifically addresses archived tickets.

1. Open Tickets per Queue with Owner Name#

SELECT
    q.name                                         AS queue,
    COUNT(*)                                       AS open_tickets,
    CONCAT(u.first_name, ' ', u.last_name)         AS owner
FROM   ticket t
JOIN   ticket_state      ts ON ts.id = t.ticket_state_id
JOIN   ticket_state_type st ON st.id = ts.type_id
JOIN   queue             q  ON  q.id = t.queue_id
JOIN   users             u  ON  u.id = t.user_id
WHERE  st.name    = 'open'
AND    t.archive_flag = 0
GROUP BY q.id, q.name, u.id, u.first_name, u.last_name
ORDER BY q.name, open_tickets DESC;

2. Ticket Volume by State — Last 30 Days#

SELECT
    ts.name  AS state,
    COUNT(*) AS tickets
FROM   ticket t
JOIN   ticket_state ts ON ts.id = t.ticket_state_id
WHERE  t.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND    t.archive_flag  = 0
GROUP BY ts.id, ts.name
ORDER BY tickets DESC;

3. Average First-Response Time per Queue (Minutes)#

Uses ticket_history to find when the first agent response was sent per ticket, then averages the gap from ticket creation.

SELECT
    q.name                                        AS queue,
    COUNT(DISTINCT t.id)                          AS tickets,
    ROUND(AVG(
        TIMESTAMPDIFF(MINUTE, t.create_time, fr.first_response_at)
    ))                                            AS avg_first_response_min
FROM ticket t
JOIN queue q ON q.id = t.queue_id
JOIN (
    SELECT   th.ticket_id,
             MIN(th.create_time) AS first_response_at
    FROM     ticket_history      th
    JOIN     ticket_history_type tht ON tht.id = th.history_type_id
    WHERE    tht.name IN ('EmailAgent', 'SendAnswer', 'PhoneCallAgent')
    GROUP BY th.ticket_id
) fr ON fr.ticket_id = t.id
WHERE t.create_time  >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND   t.archive_flag  = 0
GROUP BY q.id, q.name
ORDER BY avg_first_response_min;

4. All Inbound Emails Received Today#

SELECT
    t.tn,
    adm.a_from        AS sender,
    adm.a_subject     AS subject,
    a.create_time     AS received_at,
    q.name            AS queue
FROM   article               a
JOIN   article_data_mime     adm ON adm.article_id = a.id
JOIN   article_sender_type   ast ON ast.id = a.article_sender_type_id
JOIN   communication_channel cc  ON  cc.id = a.communication_channel_id
JOIN   ticket                t   ON  t.id  = a.ticket_id
JOIN   queue                 q   ON  q.id  = t.queue_id
WHERE  ast.name            = 'customer'
AND    cc.name             = 'Email'
AND    DATE(a.create_time) = CURDATE()
ORDER BY a.create_time DESC;

5. Tickets with a Specific Dynamic Field Value#

Replace 'Category' with the internal field name and 'Hardware' with the value you are looking for.

SELECT
    t.tn,
    t.title,
    dfv.value_text  AS category,
    t.create_time
FROM   ticket              t
JOIN   dynamic_field_value dfv ON dfv.object_id = t.id
JOIN   dynamic_field       df  ON df.id = dfv.field_id
WHERE  df.name        = 'Category'
AND    df.object_type = 'Ticket'
AND    dfv.value_text = 'Hardware'
AND    t.archive_flag = 0
ORDER BY t.create_time DESC;

6. Tickets by Priority × State (Pivot)#

SELECT
    tp.name                                        AS priority,
    SUM(CASE WHEN tst.name = 'new'    THEN 1 ELSE 0 END)  AS new_tickets,
    SUM(CASE WHEN tst.name = 'open'   THEN 1 ELSE 0 END)  AS open_tickets,
    SUM(CASE WHEN tst.name = 'closed' THEN 1 ELSE 0 END)  AS closed_tickets,
    COUNT(*)                                       AS total
FROM   ticket              t
JOIN   ticket_priority     tp  ON tp.id  = t.ticket_priority_id
JOIN   ticket_state        ts  ON ts.id  = t.ticket_state_id
JOIN   ticket_state_type   tst ON tst.id = ts.type_id
WHERE  t.create_time  >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND    t.archive_flag  = 0
GROUP BY tp.id, tp.name
ORDER BY tp.id;

7. Currently Escalated Tickets#

Tickets where any escalation clock has passed its deadline right now.

SELECT
    t.tn,
    t.title,
    q.name                                           AS queue,
    CONCAT(u.first_name, ' ', u.last_name)           AS owner,
    FROM_UNIXTIME(t.escalation_time)                 AS escalated_at,
    CASE
        WHEN t.escalation_response_time > 0
         AND t.escalation_response_time < UNIX_TIMESTAMP(NOW())
        THEN 'First Response'
        WHEN t.escalation_update_time > 0
         AND t.escalation_update_time < UNIX_TIMESTAMP(NOW())
        THEN 'Update'
        WHEN t.escalation_solution_time > 0
         AND t.escalation_solution_time < UNIX_TIMESTAMP(NOW())
        THEN 'Solution'
        ELSE 'Other'
    END                                              AS escalation_type
FROM   ticket t
JOIN   queue  q ON q.id = t.queue_id
JOIN   users  u ON u.id = t.user_id
WHERE  t.escalation_time  > 0
AND    t.escalation_time  < UNIX_TIMESTAMP(NOW())
AND    t.archive_flag     = 0
ORDER BY t.escalation_time;

8. Customer Companies Ranked by Open Ticket Count#

SELECT
    COALESCE(cc.name, t.customer_id)               AS company,
    COUNT(*)                                        AS open_tickets
FROM   ticket               t
LEFT JOIN customer_company  cc  ON cc.customer_id = t.customer_id
JOIN   ticket_state         ts  ON ts.id = t.ticket_state_id
JOIN   ticket_state_type    tst ON tst.id = ts.type_id
WHERE  tst.name       IN ('new', 'open')
AND    t.archive_flag  = 0
AND    t.customer_id  IS NOT NULL
AND    t.customer_id  != ''
GROUP BY t.customer_id, cc.name
ORDER BY open_tickets DESC
LIMIT 25;