/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 10 years ago
Red Belt
0

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

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