Let me preface this with the declaration that I have absoluely no knowledge of SQL whatsoever, aside from knowing that just looking at it gives me a headache. That being said, One of the functions that would lead my organization towards using the KACE service desk module would be the ability to "focus" new ticket notification emails towards the IT persons most likely to resolve them. Towards such an end, I found an article on the Kace KB about ticket rule notifications for newly created tickets, and modified it to match our criteria:

   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://vk1000.company.local/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
-- -- static distribution list
'asevera@email.com,testone@email.com,testtwo@email.com' 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 'TICKET CREATED%'
AND
CAT.NAME IS 'Hardware'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1

However this doesn't actually work. I believe the failure point is the additional entry I made at the bottom, as without that snippet, the rule works correctly and emails the distribution list on the creation of any ticket.

     AND
CAT.NAME IS 'Hardware'

What I'd like to know is how I can modify this to notify only on creation of tickets in a specific category (I've got the usual, hardware, software, printer, network plus a few more prominent issues we deal with for specific software packages.

 

Any help would be greatly appreciated, since I'm learning all this by doing.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
CAT.NAME IS 'Hardware'
I believe you would either need to do a LIKE or = instead of is.  From my 
understanding you would need to create a seperate rule for 
each category, but I could be wrong.
 
Answered 01/02/2013 by: Timi
Seventh Degree Black Belt

Please log in to comment
1

@Timi, you are correct. The "IS" needs to be replaced with the "=". Once changed the query will work just fine:

AND CAT.NAME = 'Hardware'

Answered 01/02/2013 by: mwill_27
Blue Belt

  • Thank you both, Spot on.
Please log in to comment
0

If you are going to have a group of technicians per category then I would suggest adding a line to get the category CC list to your select statements and then use that for the field to send the email notification to:

 CAT.CC_LIST as CATEGORY_CC

Add that line just below the CAT.NAME entry. This way you don't need to have a separate rule per category.

Answered 01/02/2013 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share