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?
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
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.
Comments:
-
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. - glenhooper 8 years ago
-
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? - chucksteel 8 years ago-
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. - glenhooper 8 years ago -
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") - glenhooper 8 years ago
-
You are missing a comma after OWNEREMAIL. - chucksteel 8 years ago
-
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 - glenhooper 8 years ago
-
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. - chucksteel 8 years ago
-
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. - glenhooper 8 years ago
-
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 - chucksteel 8 years ago -
That works, but I had to use http instead of https. I was getting SSL errors. - glenhooper 8 years ago
