/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (5)

Posted by: barchetta 3 months ago
2nd Degree Black Belt
0

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


Posted by: jjayko 3 months ago
Senior Yellow Belt
0

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 3 months ago
    • I ran from workbench after moving ticket and got 0 row(s) returned moved it again and got same result - jjayko 3 months 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 3 months ago
Posted by: jjayko 3 months ago
Senior Yellow Belt
0

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 3 months ago
Posted by: jjayko 3 months ago
Senior Yellow Belt
0

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 3 months ago
Posted by: barchetta 3 months ago
2nd Degree Black Belt
0

`By the way, is anyone else astonished that kace sma has no built-in notification for a ticket moved?  I mean how in the heck is anyone gonna know?


Comments:
  • Always remember....it's a Quest ;o) - Hobbsy 2 months ago

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