/bundles/itninjaweb/img/Breadcrumb_cap_w.png
For some reason, when a ticket is set to Resolved status and the user reopens or updates it, the owner is not notified and I cannot find a rule that does this. Can anyone help with this? I know nothing about SQL and am quite new to KACE.
1 Comment   [ - ] Hide Comment

Comments

  • Thank you ever so much ChuckSteel.
Please log in to comment

Answer this question or Comment on this question for clarity

Answers

1
You are going to want to create a new rule (Service Desk, Configuration, Rules, from the View By menu select the appropriate queue). Under Choose action, select New (SQL).
Give your rule a name and description
Enter the following in the Select SQL textbox:
select HD_TICKET.ID, 
HD_TICKET.ID as TICKNUM, 
HD_TICKET.TITLE, 
U1.USER_NAME as OWNER_NAME, 
U3.USER_NAME as LASTINPUTNAME,  
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, 
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED, 
HD_STATUS.NAME AS STATUS_NAME, 
HD_STATUS.ORDINAL as STATUS_ORDINAL, 
STATE, 
U1.FULL_NAME as OWNER_FULLNAME, 
U1.EMAIL as OWNER_EMAIL, 
U2.USER_NAME as SUBMITTER_NAME, 
U2.FULL_NAME as SUBMITTER_FULLNAME, 
U2.EMAIL as SUBMITTER_EMAIL, 
U3.EMAIL as UPDATEREMAIL, 
U3.FULL_NAME as UPDATERNAME,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_TICKET_CHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
SUBMITTER_LOCATION.NAME AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, 
HD_PRIORITY, 
HD_STATUS, 
HD_IMPACT, 
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 
 and HD_TICKET_CHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID 
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID 
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
JOIN ASSET SUBMITTER_LOCATION on SUBMITTER_LOCATION.ID = U2.LOCATION_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID  and 
HD_STATUS.ID = HD_STATUS_ID  and 
HD_IMPACT.ID = HD_IMPACT_ID  and 
HD_CATEGORY.ID = HD_CATEGORY_ID  and
HD_TICKET.OWNER_ID != HD_TICKET_CHANGE.USER_ID and
HD_STATUS.STATE = 'closed'

Check the box for Email each recipient in query results 
Enter a subject
For Column containing email addresses enter OWNER_EMAIL
Enter a message
Note that for both the subject and message you can you can use variables based on the columns selected in the query. In the first part of the query you can see SELECT table.column as SOMETHING, table.othercolumn as SOMETHINGELSE. The SOMETHING and SOMETHINGELSE are called aliases and the appliance turns them into variables like $something and $something else. For example, U3.FULL_NAME as UPDATERNAME generates the $updatername variable, so your message can include something like: 
$updatername updated ticket $id

Uncheck the Run Update checkbox

Set the Schedule Frequency to "On Ticket Save"

Save the rule.

Answered 03/08/2018 by: chucksteel
Red Belt

Please log in to comment