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

Comments

Please log in to comment

Answers

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.

Answered 10/23/2013 by: chucksteel
Red Belt

  • 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.
    • 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.
  • 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.
    • 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?
      • 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.
      • Did you change the QUEUE_ID to match your service desk's queue ID?
      • 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?
      • It's using the number so you need to use the ID.
      • Where do I find that?
      • 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.
      • 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).
Please log in to comment
Answer this question or Comment on this question for clarity