/build/static/layout/Breadcrumb_cap_w.png

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! 


0 Comments   [ + ] Show comments

Answers (4)

Posted by: Hobbsy 1 year ago
Red Belt
1

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
Posted by: barchetta 1 year ago
4th Degree Black Belt
1

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
Posted by: mjtomk 1 year ago
White Belt
1

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







Posted by: Jordan.Hessman@fda.hhs.gov 1 year ago
White Belt
0

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.


Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ