Move Queue Notifications to Group
I have the following ticket rule based on barchetta had posted . I am trying to get the emails to go to a group label. I have 2 test Q set up "A and B". The code in Mysql workbench looks good. I have the Email Each Recipient Checked off and GROUPMAIL in the Column containing email addresses. I test moving a ticket from a to b and b to a and i get
"08/31/2022 16:44:08> Starting: 08/31/2022 16:44:08 08/31/2022 16:44:08> Executing Select Query... 08/31/2022 16:44:08> selected 0 rows" in the log.
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 https://kbox./adminui/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
'Test Queue - Helpdesk Notifications' AS GROUPMAIL -- $GROUPMAIL
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
/* group email */
JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID
/* queue */
JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE C.DESCRIPTION like concat("%Changed ticket Queue from%to%.%")
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
----------
Thanks in advance
Answers (5)
So you have your email address here right?
'youremail@yourdomain.com' AS GROUPMAIL -- $GROUPMAIL
If you are going to use a static dis list and not build it from owners of the q you can delete this:
/* group email */
JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID
After you move a ticket you could check it in your sql editor. Just add the ticket # to your where statement in your editor.
Where
hd_ticket.id = 'yourticketrnumber' (I think thats right, check it)
and
C.DESCRIPTION like concat("%Changed ticket Queue from%to%.%")
GROUP BY HD_TICKET.ID
HAVING 1=1
so the 'Test Queue - Helpdesk Notifications' is a label that contains the owners of the queue. we are going to use the labels to isolate out which owners actually get the notifications. not all owners need to get them. we have a main ticket rule for New Unassigned Tickets that uses the same type of LABEL for notifications. That rule works. This is the syntax of that rule on the test queues.
Select
HD_TICKET.*,
HD_STATUS.NAME As STATUS_NAME,
HD_IMPACT.NAME As IMPACT_NAME,
HD_CATEGORY.NAME As CATEGORY_NAME,
HD_PRIORITY.NAME As PRIORITY_NAME,
HD_STATUS.STATE,
U2.FULL_NAME As SUBMITTER_FULLNAME,
U2.EMAIL As SUBMITTER_EMAIL,
Q.NAME As QUEUE_NAME,
(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 = 'Test Queue - Helpdesk Notifications') As GROUPMAIL
From
HD_TICKET Left Join
USER U1 On U1.ID = HD_TICKET.OWNER_ID Left Join
USER U2 On U2.ID = HD_TICKET.SUBMITTER_ID Left Join
USER U3 On U3.ID = HD_TICKET.APPROVER_ID Left Join
HD_QUEUE Q On Q.ID = HD_TICKET.HD_QUEUE_ID Left Join
MACHINE M1 On M1.ID = HD_TICKET.MACHINE_ID,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY
Where
HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And
HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And
HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And
HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And
HD_TICKET.OWNER_ID = 0 And
HD_TICKET.HD_QUEUE_ID = '6,7' And
HD_STATUS.NAME = 'New'
Comments:
-
ok.. that all being said, I dont know why your select statement is failing. I checked one I run in production and it looks to be identical code. Assuming current version of kace sma. Like I said, what I would do is move a ticket and then run the script from workbench or other sql editor. - barchetta 2 years ago
-
I ran from workbench after moving ticket and got 0 row(s) returned moved it again and got same result - jjayko 2 years ago
-
Id have to be in your environment to help Im afraid. I guess what I would do is see what the C.DESCRIPTION looks like.. that is tricky one to look at because you have to look at the last change, Here is a more consolidated version of that script I am using in production right now. This one sends to all owners of the queue but that is easy to change.
SELECT
HD_TICKET.CREATED,
HD_PRIORITY.NAME AS PRIORITY,
HD_TICKET.TITLE,
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_CATEGORY.NAME AS CATEGORY,
C.COMMENT,
C.DESCRIPTION,
S.FULL_NAME AS SUBMITTER_NAME,
Q.NAME AS QUEUE_NAME, O.FULL_NAME AS OWNER_NAME,
HD_TICKET.ID as TOPIC_ID , CONCAT('TICK: ', HD_TICKET.ID) AS Number,
OLIST.EMAIL AS NEWTICKETEMAIL, -- $newticketemail
CAT.NAME AS CATEGORY, -- $category
HD_STATUS.NAME AS STATUS
FROM
HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
LEFT JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN MACHINE ON (MACHINE.ID = HD_TICKET.MACHINE_ID)
LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS
and HD_TICKET.HD_SERVICE_STATUS_ID
and HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID
LEFT JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
LEFT JOIN HD_CATEGORY CTEXT ON (CTEXT.ID = HD_TICKET.HD_CATEGORY_ID)
/* group email */
JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID
/* queue */
JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
HD_TICKET.HD_QUEUE_ID = '6' and C.DESCRIPTION LIKE concat("%Changed ticket Queue from%to%.%") - barchetta 2 years ago
with your code i get an error in workbench on the C.ID=<CHANGE_ID> saying < is not valid at this position
Comments:
-
Yeah, I dont think you can run that as is in workbench. You'd need to comment change_id out because it is a kace internal variable. - barchetta 2 years ago
ok, i got it working with the exception it generates 2 emails for each ticket move. this is what i ended up with
SELECT
HD_TICKET.CREATED,
HD_PRIORITY.NAME AS PRIORITY,
HD_TICKET.TITLE AS SUMMARY,
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_CATEGORY.NAME AS CATEGORY,
C.COMMENT,
C.DESCRIPTION,
S.FULL_NAME AS SUBMITTER_NAME,
U2.FULL_NAME As SUBMITTER_FULLNAME,
U2.EMAIL As SUBMITTER_EMAIL,
Q.NAME AS QUEUE_NAME, O.FULL_NAME AS OWNER_NAME,
HD_TICKET.ID as TOPIC_ID , CONCAT('TICK: ', HD_TICKET.ID) AS Number,
OLIST.EMAIL AS NEWTICKETEMAIL, -- $newticketemail
CAT.NAME AS CATEGORY, -- $category
HD_STATUS.NAME AS STATUS,
(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 = 'Test Queue - Helpdesk Notifications') AS GROUPMAIL
FROM
HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
LEFT JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
Left Join USER U2 On (U2.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN MACHINE ON (MACHINE.ID = HD_TICKET.MACHINE_ID)
LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS
and HD_TICKET.HD_SERVICE_STATUS_ID
and HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID
LEFT JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
LEFT JOIN HD_CATEGORY CTEXT ON (CTEXT.ID = HD_TICKET.HD_CATEGORY_ID)
/* group email */
JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID
/* queue */
JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
HD_TICKET.HD_QUEUE_ID = '7' and C.DESCRIPTION LIKE concat("%Changed ticket Queue from%to%.%")
Comments:
-
I bet you need a "limit 1" somewhere.
Nice job by the way.. what did you change that solved it? I would at least comment out the group email section since you dont use it.
The only real change I see is you removed
LEFT JOIN HD_CATEGORY CTEXT ON (CTEXT.ID = HD_TICKET.HD_CATEGORY_ID)
Did that not work in your environment? - barchetta 2 years ago