KACE Product Support

Where would I put such code though? Thanks for your help - glennvelsol 10 years ago

Could you post the select query for your custom rule? For a sample that targets the Ticket Created, check http://www.itninja.com/question/email-ticket-owners-label-on-new-unassigned-ticket . - grayematter 10 years ago

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,
'email@email.com' as NEWTICKETEMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
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>'
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
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_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%' - glennvelsol 10 years ago

In the last line of your code, you are catching all tickets on creation. Add "NOT" before "LIKE" to catch everything after the initial ticket creation. - grayematter 10 years ago

Sorry, I'm a bit confused, that code I posted simply sends an email to the submitter that their ticket was successfully created. Adding that word of "Not" before like still sends the email that it was created and prevents the other notification from triggering that a the ticket was created? - glennvelsol 10 years ago

You will need two rules, one to generate the "Ticket Created" email and a separate one to generate emails for subsequent changes. - grayematter 10 years ago

So I turn off the built in comments for submitter and run the two rules? - glennvelsol 10 years ago

Exactly. Turn off the email on event and enable both rules. - grayematter 10 years ago

OK, we are getting somewhere. How do I "call" the comment into the email? I tried $change_desc but it just showed up as plain text in the email trigger. Also... is there a way to have the "owner only" stll work? When I selected owner only it still triggered the email to the submitter. - glennvelsol 10 years ago

OK, I got the "Owners Only" option working so it won't trigger an email when owners only is checked by adding this code to my rule
HD_TICKET_CHANGE.OWNERS_ONLY=0

Now the only thing left is how do I get the comment in the email, lol.

Thanks for all your help, we're almost there! - glennvelsol 10 years ago

You are pulling the change comment with COMMENT in the fields selected. To reference this field in the email, use $comment.

You could filter on the HD_TICKET_CHANGE.OWNERS_ONLY field to exclude any updates flagged as Owners Only. A 0 indicates "public" comment and a 1 indicates an owners only comment. The line in the where clause should be
HD_TICKET_CHANGE.OWNERS_ONLY = 0 - grayematter 10 years ago

Thanks I got everything working! Except one thing now, lol. When I closed the ticket and inputted a resolution, it triggered a email with no comments. How can I tell it to ignore the " Added resolution text"
and "Changed ticket Status from "Opened" to "Closed"" so those don't trigger emails either? - glennvelsol 10 years ago

Ok, I got it so it won't send those emails either by adding more "Not Like" lines for status and resolution

THANK YOU FOR YOUR HELP! - glennvelsol 10 years ago