Service Desk email notifications
Hi all,
KACE seems to have pretty good reporting etc but one thing I can't figure out is how to get it to send an email to a specified group or select people whenever certain events take place.
I'd like KACE to send emails to certain people upon these events (as soon as they happen):
1) For the IT Service Desk, whenever a new ticket is logged by a user
2) For all queues, whenever a ticket is a assigned to that queue
3) For all queues, when a ticket has not been acknowledged by changing the status to something other than 'New' (for example)
Using reporting, I can set up something which emails a team if tickets have a status of 'New' every hour, but this is not ideal. I really want a team to be notified as soon as a ticket hits thier queue.
Any help is appreciated.
Thanks,
TWIT
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
12 years ago
The following KB article will help with 1 and 2:
http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk
Number 3 is a little more complicated but we can help once you get started. I normally start with the rules wizard for the basics of the rule (like checking if the status equals New) and then work with the SQL code to tweak it further.
Comments:
-
That's great, thanks. We won't worry about number 3 for now. I can just use my report which sends an email if any calls are still set to "New" every hour. - twit 12 years ago
-
If anyone is still watching this thread, the link in the first answer helped me to create the email notification when something new is added to the Service Desk queue and that works fine, but I would also like it to email specific distribution group email addresses if a ticket is moved to a different queue, e.g. Desktop Support. I can't get this working. Any help on a rule for that one?
Thanks. - twit 12 years ago -
You need to create the rule in the Desktop Support queue. It will be very similar to the rule in the KB article except this line:
C.DESCRIPTION LIKE 'TICKET CREATED%'
needs to change to something like:
C.DESCRIPTION LIKE '%Changed ticket Queue from%to%' - chucksteel 12 years ago-
Out of curiosity, where did C.DESCRIPTION come from. I have seen places where values are called and I cannot tell where they are coming from. There is not a table called C so it is not a column named DESCRIPTION on a table named C.
I have seen several letter that have been used in other SQL statements that I cannot determine their origin. Such as:
P.NAME AS PRIORITY – What is P.NAME
S.NAME AS STATUS – What is S.NAME
HD_TICKET_CHANGE H ON H.HD_TICKET_ID – What does the H in both of these stand for?
HD_PRIORITY P ON P.ID – Where does this P in both come from?
I have a feeling that if I can understand why these letters are being used, I can work more on statements without having to search online or ask a ton of questions. - jessburd 6 years ago-
There is a join statement to the HD_TICKET_CHANGE table that creates an alias for that table as C:
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
This specific join selects the current change, based on the CHANGE_ID variable. Using an alias for a table allows you to join to it several times using different criteria. For instance, there are three joins to the USER table:
/* 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 - chucksteel 6 years ago
-
Thanks. I can't get it to work though and it's frustrating me a little. The name of the queue I've created is "Desktop Administration". Can you put exactly what I'd need on the C.DESCRIPTION LIKE line based on that? I've tried every variation I can think of to no success. Thanks. - twit 12 years ago
-
Is the rule you created in the Desktop Administration queue? That's the first gotcha. Otherwise, please post the select query you have for the rule, and I can take a look at it. - chucksteel 12 years ago
-
Hi, I've created the rule in the Desktop queue. The rule itself doesn't contain anything which is specific to the name of the queue right? :
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://KBOX/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
'[EMAIL ADDRESS]' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_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 '%Changed ticket Queue from%to Desktop Administration.%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Thanks. - twit 12 years ago -
Also, I'm sure '%Changed ticket Queue from%to Desktop Administration.%' definitely isn't right but it's just the last variation (of many) that I tried. - twit 12 years ago
-
The rule needs to be in the destination queue because rules only work on tickets in the queue they are also in. - chucksteel 12 years ago
-
Yeah that's the situation as it stands. The rule is in the Desktop queue and the ticket is being assigned to the Desktop queue. Thanks. - twit 12 years ago
-
I have now got this working. Thanks for your help. - twit 12 years ago
-
This was a helpful post for us as well. I used Option one via the category cc list in the blog article link you provided. Thanks Chuck. - jschu67 11 years ago
