/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Does anyone that have the SQL code for a custom ticket rule that would sent out a notification to the ticket owner if the ticket has not had it status change from "New" to "In Progress" in specified time?

09/02/2015 1331 views
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.

All 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.

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