Send e-mail to ticket owner when ticket is due
Is it possible to configure CASE so it sends an e-mail to the ticket owner the same day or the day before a ticket is due?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
11 years ago
I do this by creating a report that shows tickets due today (or tomorrow if you prefer). You can then schedule this report to be emailed every day.
Here's an example report for tickets due today:
SELECT HD_STATUS.NAME AS STATUS, HD_TICKET.ID, HD_TICKET.TITLE, S.FULL_NAME AS SUBMITTER_NAME, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED, HD_TICKET.DUE_DATE, O.FULL_NAME AS OWNER_NAME, HD_TICKET.PARENT_ID, HD_PRIORITY.NAME AS PRIORITY, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) WHERE (HD_TICKET.HD_QUEUE_ID = 13) AND ((DATE(HD_TICKET.DUE_DATE) = DATE(NOW()))) AND HD_STATUS.STATE != 'closed' GROUP BY HD_TICKET.ID ORDER BY ID
This finds all tickets due in a specific queue.
Comments:
-
Thanks.
Is it possibe to edit it so it wil select cases for specific users, like USER1, USER2? By doing this the users will get an e-mail with only the cases they are working on. - MesaMe 10 years ago-
Yes, you could tailor the report for specific users. You would do this by adding the following to the WHERE line:
and O.USER_NAME = "MesaMe"
Just to be clear, you would place this after HD_STATUS.STATE != "closed" and before GROUP BY.
You will need to create a separate report per user and schedule them accordingly. - chucksteel 10 years ago-
Thank you. - MesaMe 10 years ago
-
Even though I set due date for a case for today the result is says "No result found" and I guess this could be because of different date formats. In Norway we use DD/MM/YYYY and it seems like YYYY/MM/DD is used by SQL. - MesaMe 10 years ago
-
Interesting. I would assume that MySQL would handle any localization issues with regards to date formats. If you query the database for something like SELECT DATE(DUE_DATE), DATE(NOW()) FROM HD_TICKET are the results for both columns in the same format? - chucksteel 10 years ago
-
It displays all cases in KACE and the output is: Date(Due Date): 2013-05-30 Date(Now()): 2014-03-06 so it seems the date format is the same.
For the test-case with due date for today I dont get why the SQl-statement above states that no result are found. - MesaMe 10 years ago -
Did you change the QUEUE_ID to match your service desk's queue ID? - chucksteel 10 years ago
-
I didnt but have done now. Still no result. Do I have to change it to que number (if that exists) or is it sufficient to change it to name?
[code]
SELECT HD_STATUS.NAME AS STATUS, HD_TICKET.ID, HD_TICKET.TITLE, S.FULL_NAME AS SUBMITTER_NAME, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED, HD_TICKET.DUE_DATE, O.FULL_NAME AS OWNER_NAME, HD_TICKET.PARENT_ID, HD_PRIORITY.NAME AS PRIORITY, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) WHERE (HD_TICKET.HD_QUEUE_ID = 'DNK Helpdesk') AND ((DATE(HD_TICKET.DUE_DATE) = DATE(NOW()))) AND HD_STATUS.STATE != 'Closed' GROUP BY HD_TICKET.ID ORDER BY ID
[/code]
Edit: how do I ad proper code-tag? - MesaMe 10 years ago -
It's using the number so you need to use the ID. - chucksteel 10 years ago
-
Where do I find that? - MesaMe 10 years ago
-
If you are using the /adminui path to access your KBox then when you view the queue configuration page it will be in the URL. - chucksteel 10 years ago
-
Thanks. Finding the number did it.
Edit: Is it possible to make the ID colums as clickable links so its possible to open the case itself by clicking on it?
How do I remove colomn 'Hd Ticket Change Comment Grouped'? (If its possible to open the case from a clickable link). - MesaMe 10 years ago
