KACE ticket rule to alert on open tickets > 30 days
I need to create a KACE rule to email out notifications to the ticket owners when their ticket has been open for greater than 30 days. Doing it via the wizard seems like i have the options to do so, and seems easy enough, but no results show up. What is the properly syntax to use for 30 days? - Mo
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
Druis
7 years ago
Try this:-
Select 'noreply@emailserver.com' AS ALERTEREMAIL,
S.NAME AS STATUS,
T.ID AS ID,
T.TITLE AS ISSUE,
U.FULL_NAME AS SUBMITTER,
O.FULL_NAME AS OWNER,
T.CREATED AS CREATED,
T.MODIFIED AS STALLED
FROM HD_TICKET T
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN USER O ON (O.ID = T.OWNER_ID)
WHERE (S.NAME != 'Closed' AND DATEDIFF(NOW(), T.MODIFIED) >= 30)
AND T.HD_QUEUE_ID = 0
Email each recipient in query results
Subject: Whatever Column containing email addresses: ALERTEREMAIL
Message:
Whatever
Schedule: Hourly
Comments:
-
what do you mean by "noreply@emailserver.com" and the subject line of "Whatever Column containing email addresses: ALERTEREMAIL". - Mo - mmarchese@cookcountytreasurer.com 7 years ago
-
Nevermind Druis. It was a runon sentence and it threw me off. I'm trying this now. - mmarchese@cookcountytreasurer.com 7 years ago
-
Apologies, I've an error on the first line. It should read:
SELECT O.EMAIL AS ALERTEREMAIL,
Sorry about the run on, hazards of copy and paste. - Druis 7 years ago
Posted by:
chucksteel
7 years ago
Unfortunately, the wizard doesn't do date calculations correctly. You will need to modify the SQL code that it generates a little bit. Please post the current select statement for your rule and I can explain the changes you need to make.
