KACE Product Support

KACE Product Support : Kace Service Desk - Custom ticket rule to notify when ticket enters a queue (itninja.com)

Just remove the "created" line in the where statement to only email moved tickets.

Thanks Barchetta --

I have no SQL background.  I'm just not entirely sure what you're asking me to do.   

I want the behavior to be the same as it is now with all New Ticket submissions, but in addition, give an email notification if one department moves a ticket to another.  

where C.DESCRIPTION LIKE '%Ticket Created%' /* initial entry for ticket */   <--- is this the line you're saying to take the Created part out?  Should I create another rule with created as well to maintain the current behavior?

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.COMMENT_LOC as CMT_L10N, -- $cmt_l10n

C.DESCRIPTION, -- $description

C.LOCALIZED_DESCRIPTION as DESC_L10N, -- $desc_110n

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') as HISTORY, -- $history

group_concat( concat('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

 H.LOCALIZED_DESCRIPTION,'\n',H.COMMENT_LOC,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

 order by H.ID desc separator '\n') as HIST_l10n, -- $hist_l10n

-- 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

'ops@sandbergphoenix.com' as NEWTICKETEMAIL -- $newticketemail

from HD_TICKET

 /* latest change ***/ join HD_TICKET_CHANGE C on C.HD_TICKET_ID = HD_TICKET.ID

 /* initial change **/ left join HD_TICKET_CHANGE_FIELD F ON F.HD_TICKET_CHANGE_ID = C.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

LEFT JOIN HD_SERVICE_TICKET ON HD_TICKET.SERVICE_TICKET_ID = HD_SERVICE_TICKET.ID

where C.DESCRIPTION LIKE '%Ticket Created%' /* initial entry for ticket */

AND S.STATE != 'closed'

AND (HD_SERVICE_TICKET.ORDINAL IS NULL OR HD_SERVICE_TICKET.ORDINAL > 0)

AND HD_TICKET.OWNER_ID = 0

AND C.ID = <CHANGE_ID>

/* this is necessary when using group by functions */

GROUP BY HD_TICKET.ID

You would need to remove this "C.DESCRIPTION LIKE '%Ticket Created%' /* initial entry for ticket */

AND" 

To have this script send an email, you will need to tick the box which says you want to sent an email with the information developed from this script. So you will need to set the $newticketemail variable to your DL (and place that variable in the send to area of the email) and you will need to craft and email using the variables in this script. So anything with a $ is available to you. So for example you might want to say "A new ticket has been created; ticket Number: $ticknum."

Etc etc.. you can look up some of the available variables not in this script in the admin guide.    Lastly, there are other examples if you search.  I know I posted one recently.

If you have no sql background and this is a production environment where downtime wont easily be tolerated Id recommend setting up a test server if you havent.  The license allows for a non production server.