/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Ticket Source

07/25/2019 346 views

There is a field in the master table called "TicketSource"

How is this set.

I thought it might identify tickets that are entered through the console/application and those that come from emails (we are wanting to understand the split of the number of tickets that come through email)

Have I misinterpreted this ?




0 Comments   [ + ] Show comments

Comments


All Answers

2

There isn't a TicketSource column in the HD_TICKET table, so I'm not sure what "master table" you are referencing.

That being said, the HD_TICKET_CHANGE table contains a VIA_EMAIL column which is populated with the email address that changed the ticket if the change was generated via email. Knowing that, we can look at the first "change" on a ticket to see if it was created via an email or the portal.

-- Report on number of tickets opened per month
-- Includes columns to indicate number opened via email and not (presumes opened via portal)
SELECT concat(month(CREATED), "/", year(CREATED)) as "Month/Year",
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL = "" THEN 1
END) AS PORTAL,
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL != "" THEN 1
END) AS EMAIL,
COUNT(HD_TICKET_CHANGE.ID) as TOTAL
FROM HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID = (SELECT MIN(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_QUEUE_ID = 2
GROUP BY YEAR(CREATED),MONTH(CREATED)
ORDER BY YEAR(CREATED),MONTH(CREATED)
Note that this is limited to queue ID 2. It also doesn't care who created the ticket. If you want a report that shows tickets that were created only by the submitter, then it will need some modification.


Answered 07/26/2019 by: chucksteel
Red Belt

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