Systems Management : Ticket rule to email technician about ticket being due today

Ticket rule to email technician about ticket being due today

I have read quite a few similar posts to this, but can't find one to get this working yet. Just looking for a simple way to have a ticket rule email the assigned technician a reminder about a ticket's due date. Only a single queue. Only a single tech. Just a simple generic email to remind him/her to look at the ticket due 'today' (only running once a day a fine).

I tried using the wizard, but if I select "Due = CurDate" it comes up with 'No tickets found' even tho I do have a test ticket setup being due today.

Thanks in advanced!

0 Comments [ + ] Show comments

Answers (3)

Posted by: Hrkljus 9 years ago

I have this:

SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.DUE_DATE,
    IF((LENGTH(U1.FULL_NAME) = 0),
        U1.USER_NAME,
        U1.FULL_NAME) AS OWNER_NAME,
    IF((LENGTH(U2.FULL_NAME) = 0),
        U2.USER_NAME,
        U2.FULL_NAME) AS SUBMITTER_NAME,
    Q.NAME AS QUEUE_NAME
FROM
    (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
        LEFT JOIN
    USER U1 ON U1.ID = HD_TICKET.OWNER_ID
        LEFT JOIN
    USER U2 ON U2.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN
    USER U3 ON U3.ID = HD_TICKET.APPROVER_ID
        LEFT JOIN
    HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
        LEFT JOIN
    MACHINE M1 ON M1.ID = HD_TICKET.MACHINE_ID
WHERE
    HD_PRIORITY.ID = HD_PRIORITY_ID
        AND HD_STATUS.ID = HD_STATUS_ID
        AND HD_IMPACT.ID = HD_IMPACT_ID
        AND HD_CATEGORY.ID = HD_CATEGORY_ID
        AND (((HD_TICKET.DUE_DATE <= CURDATE())
        AND HD_STATUS.NAME != 'Closed')

and I just email the results to Help Desk.

I hope this helps.

Comments:

Thanks for the help! But I'm getting an sql syntax error.

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 27]

Any ideas? - HarborIT 9 years ago

Posted by: chucksteel 9 years ago

The reason the rules wizard doesn't work for this is because it does not handle dates correctly. It will create a where clause that looks like this:

HD_TICKET.DUE_DATE = 'CurDate'

This needs to be modified to:

HD_TICKET.DUE_DATE = DATE(NOW())

or, as Hrkljus answered:

HD_TICKET.DUE_DATE = CURDATE()

Also, the rule needs a column that contains the email address of the recipient. You can either hardcode that by including a line like:

"technician@company.com" AS TECH_EMAIL

Or, if you use the rules wizard it should create OWNER_EMAIL for you, and this should be the owner's email address.

Comments:

Hi chucksteel - Can this be done with any date field or do I have to use due_date? I am using a custom date field. With my SQL I get an email everyday instead of just on the due date. The email that is on the due date will populate variables. Any other days, the variables show up in the email as null. I just want it to only email me on the actual due date. Are you able to see what I am doing wrong?

SELECT

HD_STATUS.NAME AS STATUS,
HD_TICKET.ID,
HD_TICKET.TITLE, -- $title
S.FULL_NAME AS SUBMITTER_NAME,
HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.CREATED,
DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE7,'%b %d %Y') AS EMAIL_END, -- $email_end
EMPLOYEE.FULL_NAME as EMPLOYEE_FNAME, -- $employee_fname
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,
'user@abc.com' AS NEWTICKETEMAIL -- $newticketemail

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)
LEFT JOIN USER EMPLOYEE ON EMPLOYEE.ID = HD_TICKET.CUSTOM_FIELD_VALUE4

WHERE

(HD_TICKET.HD_QUEUE_ID = 10) AND ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE7) = CURDATE())) AND (HD_STATUS.STATE != 'closed') - totero21 8 years ago
- I would suggest running the select statement in MySQL Workbench to check the results. I believe that KACE stores the data in the custom fields as text values so I'm not sure if functions like DATE() will work on them correctly. You can see how the date is stored in the database by browsing the HD_TICKET table. - chucksteel 8 years ago
  - In MySQL, it will return results if I have a test ticket set to todays date and not return anything when it is a different date. I can't figure out why it is still kicking off an email every day in Kace. - totero21 8 years ago
  - Try adding DATE(HD_TICKET.CUSTOM_FIELD_VALUE7) and CURDATE() to your select list and see what values are returned in oder to verify whether or not they are matching. I would also clean up the parentheses to make sure there isn't anything weird going on there.
    WHERE HD_TICKET.HD_QUEUE_ID = 10
    and DATE(HD_TICKET.CUSTOM_FIELD_VALUE7) = CURDATE()
    and HD_STATUS.STATE != 'closed'
    
    That's much cleaner. - chucksteel 8 years ago
  - I did what you suggested by adding DATE(HD_TICKET.CUSTOM_FIELD_VALUE7) and CURDATE(). I am still getting an email every day instead of just the exact date match.
    
    Here is my email on the exact day:
    Email forwarding ends today for TESTUSER.
    
    Here is my email every other day. (which I don't want to happen):
    Email forwarding ends today for .
    
    Thanks,
    HR Support Desk - totero21 8 years ago
  - When you run the query in MySQL Workbench on days without a matching ticket what kind of results do you get? - chucksteel 8 years ago
  - When I run it in MySQL, the days without a matching ticket do not show up in the results list. - totero21 8 years ago
  - Can you post your query? - chucksteel 8 years ago
  - SELECT
    
    HD_STATUS.NAME AS STATUS,
    HD_TICKET.ID,
    HD_TICKET.TITLE, -- $title
    S.FULL_NAME AS SUBMITTER_NAME,
    HD_CATEGORY.NAME AS CATEGORY,
    HD_TICKET.CREATED,
    DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE7,'%b %d %Y') AS EMAIL_END, -- $email_end
    DATE(HD_TICKET.CUSTOM_FIELD_VALUE7),
    CURDATE(),
    EMPLOYEE.FULL_NAME as EMPLOYEE_FNAME, -- $employee_fname
    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,
    'user@company.com' AS NEWTICKETEMAIL -- $newticketemail
    
    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)
    LEFT JOIN USER EMPLOYEE ON EMPLOYEE.ID = HD_TICKET.CUSTOM_FIELD_VALUE4
    
    WHERE HD_TICKET.HD_QUEUE_ID = 10
    and DATE(HD_TICKET.CUSTOM_FIELD_VALUE7) = CURDATE()
    and HD_STATUS.STATE != 'closed'
    
    ---------------------------------------------
    Subject:
    Email End Test: $title
    
    Column containing email addresses:
    NEWTICKETEMAIL
    
    Message:
    Email forwarding ends today for $employee_fname.
    
    Thanks, - totero21 8 years ago
  - I don't think this will make a difference but what happens if you use DATE(NOW()) instead of CURDATE() ? - chucksteel 8 years ago
  - I think that is what I originally had and tested with CURDATE(). I changed it to DATE(NOW()) and I get the same result...
    
    Email forwarding ends today for .
    
    Thanks, - totero21 8 years ago
  - I just ran this in MySQL and it returns 1 result of all NULL values except for CURDATE is today's date and NEWTICKETEMAIL is my email. - totero21 8 years ago
  - Try making all of the joins LEFT joins to see if that helps. - chucksteel 8 years ago
  - Left Join did not make a difference.
    
    I did make a change to the date format for (HD_TICKET.CUSTOM_FIELD_VALUE7,'%b %d %Y') to (HD_TICKET.CUSTOM_FIELD_VALUE7,'%Y-%m-%d') because I noticed that it was returning different date formats for curdate() and Custom_field_value7.
    
    It was returning this...
    2016-02-17
    Feb 17 2016
    Now it is returning this...
    2016-02-17
    2016-02-17
    
    I thought that was my problem but when I tested I got the same result unfortunately. - totero21 8 years ago

Posted by: HarborIT 9 years ago

Hey thanks for the help - I was able to get it working. Hrkljus - it was missing one ) at the very end. I also edited to be:

AND (((HD_TICKET.DUE_DATE = CURDATE())

instead of:

AND (((HD_TICKET.DUE_DATE <= CURDATE())

Other than that I should be good to go. Thanks again for the help.

Comments:

Would you be able to share the working version of the script? I see that it is supposed to be working, but I cannot seem to get mine to work. - hutcha4113 9 years ago

Answer this question

Posted by:

View more:

Systems Management Questions

question