KACE Product Support

I should have said.. it is my belief... if Im wrong someone please chime in.. if it helps, I had to do this recently, move to all rule notifications.. here is a handy rule which uses a label for sending.. so you could exclude your externals that way. It uses a concat to build the email address list.. I sure wish more people would submit rules here. It also includes moved tickets.. one thing I had to do right away was notifications on moved tickets.. I cannot believe nothing built in for moved tickets from one Q to another.


SELECT
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

-- 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
S.NAME AS STATUS, -- $status
I.NAME AS IMPACT, -- $impact
CAT.NAME AS CATEGORY, -- $category
-- other fields
(SELECT GROUP_CONCAT(USER.EMAIL SEPARATOR ', ') AS ADDRESSLIST
FROM
LABEL
INNER JOIN USER_LABEL_JT ON USER_LABEL_JT.LABEL_ID = LABEL.ID
INNER JOIN USER ON USER.ID = USER_LABEL_JT.USER_ID
WHERE
LABEL.NAME = 'YOUR LABEL NAME HERE') AS GROUPMAIL
FROM HD_TICKET

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
LEFT JOIN HD_CATEGORY CTEXT ON (CTEXT.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE
(C.DESCRIPTION LIKE '%CREATED%' OR C.DESCRIPTION LIKE concat("%Changed ticket Queue from%to%.%"))
AND HD_STATUS.NAME != 'Closed'
AND HD_STATUS.NAME not LIKE '%HOLD%'
AND HD_TICKET.OWNER_ID = 0
AND HD_TICKET.HD_QUEUE_ID = '4'
AND NOT (CTEXT.NAME like '%Access Control%')
GROUP BY HD_TICKET.ID
HAVING 1=1 - barchetta 1 year ago