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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

- When a new ticket is created, so that the end users knows we got it.

  If the user submits a ticket via email, the KBOX already sends them an email saying it got it. If they create it through the user portal no email goes out because they can see the ticket in the KBOX.
- When work comments or work logs are entered onto the ticket

  You can send comments out based on the checkboxes you see in the queue, when an event happens. The owner, submitter can get comments entered into the ticket. 
- When the ticket is closed and the resolution to the problem

   This one is also sent out when the ticket is closed but if you want to add resolution then yes that might be a custom query to write but the two request are in the KBOX already. 

Answered 10/01/2012 by: nshah
Red Belt

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

Share