Hello,

I am trying to generate a report in Kace that counts the total number of tickets created within a month based on custom fields values in all our queues. Below is the custom sql query i have generated so far however stuck on how/where to input the "count" to account for how many number of tickets where created with Custom fields 1 and Custom field 2 in the last 30 days. Any assistance will be much appreciated, thanks in advance.

SELECT HD_TICKET.CREATED, HD_STATUS.NAME AS STATUS, HD_TICKET.ID, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE1  FROM HD_TICKET  JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID in (5,2,8,9,12,14,18,16,1,15,10,11,3,13,17,25,20)) AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 30 DAY)))  ORDER BY CREATED

4 Comments   [ + ] Show Comments

Comments

  • Are you looking for a count of tickets with any values in either custom1 or custom2 (a simple count)? Such as:

    Tickets with any value in Custom1 or Custom2: 45

    Are you looking for counts grouped by the values of custom1 and custom2 (a data cube)? Such as:

    Custom1 Custom2 Count
    value_a value_b 3
    value_a Value_c 5
    value_d value_b 10
    value_d value_e 14
  • I am looking for a count of custom field value categories on all tickets for all our queues. On CUSTOM_FIELD_VALUE1 is our "Ticket Type" category with values "Break Fix" or "Service Request" while our CUSTOM_FIELD_VALUE2 is our "Ticket Source" category with values/choices "Phone" "Web Browser" "Email" or "Chat"

    Thanks,
  • I think this is what you are trying to get at.

    SELECT HD_STATUS.NAME AS STATUS,
    HD_TICKET.CUSTOM_FIELD_VALUE2,
    HD_TICKET.CUSTOM_FIELD_VALUE1,
    count(*) as count_of_both
    FROM HD_TICKET
    JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
    WHERE (HD_TICKET.HD_QUEUE_ID in (5,2,8,9,12,14,18,16,1,15,10,11,3,13,17,25,20))
    AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 30 DAY)))
    GROUP BY HD_STATUS.NAME, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE1
    ORDER BY HD_STATUS.NAME, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE1

    Something like this will give you data that you can import into an excel pivot table. You can then slice and dice the data as you wish. If you want to add the option to view counts by queue, add HD_TICKET.HD_QUEUE_ID to the select, group by, and order by clauses.
  • The sql query above did not work/show the needed result. It did not present a total value of the custom field values for each queue. Each Queue should be listed as a row with columns for total amounts of values in custom field value 1 and custom field value 2.

    Thanks
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity