Dell KACE Email Notification Setup
First, thanks for taken the time to read this. This is my first post to this forum area as I am fairly new to the KACE world. My question has a few parts to it but I think it should be something pretty easy to get accomplished.
I'm trying to setup ticket rules for email notification. I've been able to get a rule setup that emails on every change done to a ticket, but I think that will be overwhelming to the end users. I'd like just to create an email notification for a few key parts of the ticketing process.
- When a new ticket is created, so that the end users knows we got it.
- When work comments or work logs are entered onto the ticket
- When the ticket is closed and the resolution to the problem
I'm pretty sure that I have to create a ticket rule query for each of those items, but my ability to write queries is very lacking. If I could get some help with this I would greatly appreciate it. Here is the current query that I'm using that emails any time an update is done.
Select Query:
SELECT
-- ticket fields
HD_TICKET.ID, ------------ $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kace/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields
-- -- example of static distribution list
'admin@domain.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.DESCRIPTION LIKE 'TICKET CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Send an email for each result row:
Subject: [TICK:$ticknum] NEW TICKET: $title
Email Column: SUBMITTER_EMAIL
Email Body:
Dear $submitter_fname, a ticket was created in response to your submission. You may see more details and track progress of your new ticket at:
http://kace/userui/ticket?ID=$ticknum
The submission was:
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Comment: $comment
Thanks!
Joey
Answers (1)
Joey, my team changes the status any time work is done to a ticket request to notify the end user. What I am doing in my email notification is creating a generic email body in the custom rule that pulls a ticket summary from the script itself. I have a similar SQL query as the one you posted, the difference is in my $history group concatenation. Instead of pulling the $comment into my email, I pull the $history field in, using the Group Concatenation I am able to inject custom text into the email no matter the ticket status, and hopefully only have to have a single custom ticket rule for change notifications.
I have done something like this, of course you can add anything to the CASE WHEN statements to filter for any info you want:
CASE WHEN S.NAME = 'New' THEN
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
C.COMMENT,'\n\nThank you for submitting a ticket through KACE, tech department staff will address your issue as soon as possible.
If you have questions you can respond to this message or call the helpline.','\n') ORDER BY C.ID DESC SEPARATOR '\n')
ELSE
CASE WHEN S.NAME IN ('Opened', 'Work in Progress', 'Waiting for Parts or Service') THEN
GROUP_CONCAT(CONCAT('\nThere has been an update or change to your help desk ticket\n\n',
'----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n', C.COMMENT,'\n')
ORDER BY C.ID DESC SEPARATOR '\n')
ELSE
CASE WHEN S.NAME = 'Closed' THEN
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' ------\n',C.COMMENT,'Your ticket has
been closed by a technician for one of the following reasons:\n 1. Your ticket was in a wating for
response status for seven days without comment and has automatically closed\n 2. You have declared
that the issue has been resolved\n 3. A technician has completed the request\n','If you believe
the issue has not been resolved please see your ticket at the above link and add a comment to reopen it.
If you agree that the issue is resolved, please help us provide a higher level of service by providing
your feedback and rating of the service provided for this issue by completing the User Satisfaction Survey
that is now present in your ticket.\n','\nThank you from the School District of New Berlin Technology
Department') ORDER BY C.ID DESC SEPARATOR '\n')END
END
END HISTORY, -- $history
