This option isn't part of KACE out of the box.  Which was disappointing to me because a lot of companies take their SLA's seriously.  KACE only has email notifications to the ticket owner if the ticket goes into overdue status.  I need more options with ticket notifications.
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
This select statement should work:
SELECT HD_TICKET.*,
OWNER.EMAIL AS OWNEREMAIL
FROM ORG1.HD_TICKET
JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
WHERE HD_STATUS.NAME = "New"
AND HD_TICKET.CREATED < NOW() - INTERVAL 2 HOUR
This will find tickets that have a status of New and have been open for more than two hours. It places the owner's email address in a column named OWNEREMAIL that you can use for the notification.

Answered 09/03/2015 by: chucksteel
Red Belt

  • Thank you chucksteel. This works with a bit of modification. But only if the ticket is in "New" status for the time interval that is entered for a ticket priority that is marked General. I need to set up my SLA notifications for 3 other scenarios. Moderate, Serious and Critical. Can this code be modified to create those 3 more mail rules? Thank you in advance.
    • To include priority you need another join:
      JOIN HD_PRIORITY on HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID

      and you can add this to the where statement:
      AND HD_PRIORITY.NAME = "General"

      so the entire statement looks like this:

      SELECT HD_TICKET.*,
      OWNER.EMAIL AS OWNEREMAIL,
      HD_PRIORITY.NAME,
      HD_STATUS.NAME
      FROM ORG1.HD_TICKET
      JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
      JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
      JOIN HD_PRIORITY on HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID
      WHERE HD_STATUS.NAME = "New"
      AND HD_PRIORITY.NAME = "General"
      AND HD_TICKET.CREATED < NOW() - INTERVAL 2 HOUR

      To setup different rules for the various priorities change the HD_PRIORITY.NAME = "General" line to the appropriate priority name and adjust the interval if necessary.
      Does that answer your question?
      • Yes I think this will work wonderfully. I had to add the line "HD_TICKET.ID as ticket_number, -- $ticket_number" to the original code you gave me to get it to work. Dell KACE support helped me with that. The code I have now is below.

        SELECT HD_TICKET.*,
        HD_TICKET.ID as ticket_number, -- $ticket_number
        OWNER.EMAIL AS OWNEREMAIL
        FROM ORG1.HD_TICKET
        JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
        JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
        WHERE HD_STATUS.NAME = "New"
        AND HD_TICKET.CREATED < NOW() - INTERVAL 48 HOUR

        I changed it to 48 hour to match our General SLA of 2 days. I think with your help I will have completed a big task put on my by my boss! Very much appreciated chucksteel.
      • Unfortunately there is an error in the syntax and now it doesn't work . Below is the log from one of the mail rules.

        09/24/2015 10:45:02> Starting: 09/24/2015 10:45:02 09/24/2015 10:45:02> Executing Select Query... 09/24/2015 10:45:02> 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 'HD_PRIORITY.NAME, HD_STATUS.NAME FROM ORG1.HD_TICKET JOIN HD_STATUS on HD_STATUS' at line 4] in EXECUTE("SELECT HD_TICKET.*, HD_TICKET.ID as ticket_number, -- $ticket_number OWNER.EMAIL AS OWNEREMAIL HD_PRIORITY.NAME, HD_STATUS.NAME FROM ORG1.HD_TICKET JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID JOIN HD_PRIORITY on HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID WHERE HD_STATUS.NAME = "New" AND HD_PRIORITY.NAME = "Serious" AND HD_TICKET.CREATED < NOW() - INTERVAL 4 HOUR")
    • You are missing a comma after OWNEREMAIL.
      • Oh okay. I guess I was going by the original code you provided at the top of this thread. It didn't have the comma
      • Correct. The original code didn't have a comma, but when you add the other fields to be selected (HD_PRIORITY.NAME, HD_STATUS.NAME) you need to add one. The fields you are selecting in the statement are comma separated.
      • I understand now. And now the mail rule are working. I have but one last question however. In the message, I put "For complete details, see: $ticket_url". But that isn't working. It is showing the exact text in the body of the message, but not the link.
      • In order to use a variable in the email it has to be generated by the select statement. I tend to build the URL in the email itself rather than in the SQL. So to make a link to the adminui for technicians use:
        https://k1000server/adminui/ticket?ID=$ticket_number
      • That works, but I had to use http instead of https. I was getting SSL errors.
Please log in to comment
Answer this question or Comment on this question for clarity