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

Comments

Please log in to comment

Answers

1

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

Answered 09/26/2017 by: Druis
Third Degree Blue Belt

  • what do you mean by "noreply@emailserver.com" and the subject line of "Whatever Column containing email addresses: ALERTEREMAIL". - Mo
    • Nevermind Druis. It was a runon sentence and it threw me off. I'm trying this now.
      • 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.
Please log in to comment
1
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.
Answered 09/26/2017 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity