Email Queue Owners comments for all tickets in a queue
Does anyone have the sql for a custom ticket rule that emails all of the queue owners any comments that are submitted? We would want this rule fired on all incoming tickets so that any comments added to any ticket (regardless of the owner or if an owner has been assigned) are sent to a distribution list. We added a custom ticket rule to add a dl as a CC upon save but it seems a bit clunky and does not run on ticket creation (and subsequently misses comments added before the first save). Any help is appreciated!
Answers (4)
Much like I answered on this question
https://www.itninja.com/question/custom-ticket-rule-to-notify-team-of-any-ticket-change
You would first need to create an email alias for a mailing list containing all queue owners.
Once you have that it is straightforward, use the ticket change table and select any tickets that have new comments added in the last 15 minutes and run the rule every 15 minutes, then use the latest update $ variable to add in to the ticket rule.
Comments:
-
Yes, this will send out the new comments but the 15 minute delay is too long and will unfortunately create inefficiencies for an eager service desk team. Looking for an option that is real time or as close as possible. - mjtomk 1 year ago
-
Sorry, that's a KACE issue the 15mins is the minimum cycle that's available. If you added a tick box to say "Inform Team" you could then run it on ticket save and get it instantly? - Hobbsy 1 year ago
-
We were able to create a rule that fires upon hitting submit on a comment and did not have to create an email alias. We were able to use the QUEUE_OWNERS variable to send out the emails. - mjtomk 1 year ago
-
This rule which sends an email on a comment submittal could come in very handy. We are moving away from kace helpdesk but I would think it would be beneficial to others struggling if you could post this script. I know I could have used it awhile back but couldnt figure it out. - barchetta 1 year ago
I stole this from somewhere just like most rules I "create".
OLIST.EMAIL AS NEWTICKETEMAIL, -- $newticketemail
/* 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
Comments:
-
I re-read the question.. I think what you want is all comments on certain tickets to be sent to ALL OWNERS in the entire queue? Not sure if I got that right. You cant do that with the built in messaging setup.
So if you want that.. here is something close.
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
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
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_STATUS.NAME != 'Closed'
AND HD_STATUS.NAME not LIKE '%HOLD%'
AND HD_TICKET.HD_QUEUE_ID = 1
AND (C.DESCRIPTION LIKE '%CREATED%' OR C.DESCRIPTION LIKE concat("%Changed ticket Queue from%to%.%"))
NOTE: the above sends a notification to all owners in the queue when a ticket is created or moved into a queue.. MOVED being KEY for us.. youd also need to add whatever the kace entry is for a comment to the above last line in the where statement. Easy to find with a sql editor.. Actually, it may be C.Comment LIKE concat (%TEXT HERE YOU FIND FROM THE EDITOR%) - barchetta 1 year ago
We built the below sql where it fires upon on hitting submit (regardless of if an owner was assigned). It goes to all queue owners and is especially nice for managers so they can check on ticket communications without having to be within KACE. It displays all ticket comments with the most recent at the top. We formatted it as well (can adjust the font in that line but it does not line up with normal font sizing).
WITH
cte_email AS (
SELECT
HD_TICKET.ID AS EMAIL_ID,
concat(q.TICKET_PREFIX,LPAD(HD_TICKET.ID,4,0)) AS TICKSTRING,
-- queue owner emails
group_concat(distinct(u.email)) as QUEUE_OWNER_EMAIL
FROM HD_TICKET
-- queue
INNER JOIN HD_QUEUE q ON q.ID = HD_TICKET.HD_QUEUE_ID
-- queue owners
JOIN HD_QUEUE_OWNER_LABEL_JT qo on qo.HD_QUEUE_ID = HD_TICKET.HD_QUEUE_ID
JOIN USER_LABEL_JT ul ON ul.LABEL_ID = qo.LABEL_ID
JOIN USER u ON u.ID = ul.USER_ID
GROUP BY HD_TICKET.ID)
SELECT
TICKSTRING,
QUEUE_OWNER_EMAIL,
HD_TICKET.ID AS TICKNUM,
HD_TICKET.TITLE,
SUB.FULL_NAME AS TICKET_SUBMITTER_NAME,
CONCAT('https://yourkbox.domain/userui/ticket?ID=', HD_TICKET.ID) as TICK_URL,
(GROUP_CONCAT(distinct(CONCAT('<font face=Calibri><u><em><strong>',DATE_FORMAT(h.TIMESTAMP, '%Y-%m-%d %H:%i'),' by ',UPD.FULL_NAME,'</strong></em></u><br><br>', h.COMMENT,'<br><br></font>')) ORDER BY h.ID DESC SEPARATOR '<br>')) AS CHANGE_HISTORY
FROM HD_TICKET
JOIN cte_email ON EMAIL_ID = HD_TICKET.ID
JOIN HD_TICKET_CHANGE c ON c.HD_TICKET_ID = HD_TICKET.ID AND c.ID = <CHANGE_ID>
JOIN HD_TICKET_CHANGE h ON h.HD_TICKET_ID = HD_TICKET.ID AND LENGTH(h.`COMMENT`) > 0
JOIN USER UPD on UPD.ID = c.USER_ID
JOIN USER SUB on SUB.ID = HD_TICKET.SUBMITTER_ID
WHERE LENGTH(c.COMMENT) > 0
GROUP BY HD_TICKET.ID
HAVING 1=1
The subject (where we added the submitter's name as well):
[$tickstring] $ticket_submitter_name - $title
Column containing email addresses:
QUEUE_OWNER_EMAIL
The email message body:
$change_history
For complete details, see:
$tick_url
I could be misunderstanding but we have some of our queues configured like this and dont use a custom rule as the functionality is already present.
Go to:
Helpdesk or ServiceDesk > Configuration > Queues> Select Queue > Configure Queue Email Settings
Check the relevant boxes you want for Category CC under Email on Events and then add your DL to the Category CC List back in the Queue Customization page.