Ticket Source
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 ?
Answers (4)
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)
looking to add an additional column to the report for user created tickets via the user portal. I know the U3.ROLE_ID = (3) represents the non-IT users.
Comments:
-
The ROLE_ID is what role they are assigned (see Settings, Roles), so that doesn't really equate to the ticket source. - chucksteel 1 year ago
-
i understand the role_id purpose. What we are trying to accomplish is using the above report but adding a separate column for tickets that were entered via the user portal which would only be users with role id 3 . i have a different report that gives me the users and ticket count but we are looking to see trends as we roll out to more departments. - jjayko 1 year ago
-
Ah, got it.
Since this query doesn't join to the user table, that join will need to be added. Since you really want tickets created by users and not just tickets where the submitter is a user, the join should be based on the user that made the first change:
JOIN USER TICKETCREATOR on TICKETCREATOR.ID = HD_TICKET_CHANGE.USER_ID
Be sure to add that join after the join to the HD_TICKET_CHANGE table.
You could then add a column that counts tickets that were entered via the portal and were created by a user with a role of 3.
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL = "" and TICKETCREATOR.ROLE_ID = 3 THEN 1
END) AS USERPORTAL - chucksteel 1 year ago
I get error 1052 column Created is ambiguous
-- 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(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL = "" and TICKETCREATOR.ROLE_ID = 3 THEN 1
END) AS USERPORTAL,
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)
JOIN USER TICKETCREATOR on TICKETCREATOR.ID = HD_TICKET_CHANGE.USER_ID
WHERE HD_TICKET.HD_QUEUE_ID = 3
GROUP BY YEAR(CREATED),MONTH(CREATED)
ORDER BY YEAR(CREATED),MONTH(CREATED)
Comments:
-
Because there are now multiple tables with the CREATED column, you need to specify the table along with the column name. Any of the CREATED columns should be HD_TICKET.CREATED - chucksteel 1 year ago
ok , got it to work with. now to try to have it pull from archived tickets. as well
-- Report on number of tickets opened per month
-- Includes columns to indicate number opened via email, portal and user portal
SELECT concat(month(HD_TICKET.CREATED), "/", year(HD_TICKET.CREATED)) as "Month/Year",
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL = "" and TICKETCREATOR.ROLE_ID = 3 THEN 1
END) AS "User Created",
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL = "" THEN 1
END) AS "IT - Portal Created",
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL != "" THEN 1
END) AS "IT - Email Created",
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)
JOIN USER TICKETCREATOR on TICKETCREATOR.ID = HD_TICKET_CHANGE.USER_ID
WHERE HD_TICKET.HD_QUEUE_ID = 3
GROUP BY YEAR(HD_TICKET.CREATED),MONTH(HD_TICKET.CREATED)
ORDER BY YEAR(HD_TICKET.CREATED),MONTH(HD_TICKET.CREATED)
