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

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

0

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.

Answered 05/19/2015 by: Hrkljus
Orange Belt

  • 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?
Please log in to comment
0
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. 

Answered 05/20/2015 by: chucksteel
Red Belt

  • 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')
    • 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.
      • 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.
      • 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.
      • 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
      • When you run the query in MySQL Workbench on days without a matching ticket what kind of results do you get?
      • When I run it in MySQL, the days without a matching ticket do not show up in the results list.
      • Can you post your query?
      • 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,
      • I don't think this will make a difference but what happens if you use DATE(NOW()) instead of CURDATE() ?
      • 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,
      • 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.
      • Try making all of the joins LEFT joins to see if that helps.
      • 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.
Please log in to comment
0
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.
Answered 05/21/2015 by: HarborIT
Senior White Belt

  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity

Share