/build/static/layout/Breadcrumb_cap_w.png

E-mail every member of a queue on new ticket creation

Hi everyone, I am trying to create a custom rule that will e-mail every member of a queue when a new ticket is created (whether it's assigned or not) so that if someone happens to be out of the office, the other members know that a ticket was assigned to that person so they can work the ticket if available.

We have a group e-mail that includes all members so I would e-mail to networksupport@mycompany.com when a ticket is opened for a specific queue.

I have been reading some other posts and looks like I might need to know the queue number since I have multiple queues but I am not sure how to find that information.


Thanks in advance

Asked 9 years ago 3066 views
0 Comments   [ + ] Show comments

Answers (4)

Posted by: h2opolo25 9 years ago
Red Belt
1
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 http://KBOXWEBSITE/userui/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
      'DISTRIBUTION EMAIL' AS NEWTICKETEMAIL -- $newticketemail
    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
    WHERE
     C.DESCRIPTION LIKE 'TICKET CREATED%'
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1




Then Email each recipient in query results with...

Subject: [TICK:$ticknum] NEW TICKET: $title
Column containing email addresses: NEWTICKETEMAIL
Message:
$submitter_fname has opened a ticket.  
The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Opening Comment: $comment

For more details go to:
http://KBOXWEBSITE/userui/ticket?ID=$id
Comments:
  • I have tried this and received an error.... Please forgive my ignorance as I do not know SQL so I am kind of browsing through the query trying to figure out everything it does. Do I just need to change the url for my k1000 appliance or is there anything else I am supposed to change?

    Here is the error I am seeing when using your SQL above:
    02/24/2015 16:00:02> Starting: 02/24/2015 16:00:02 02/24/2015 16:00:02> Executing Select Query... 02/24/2015 16:00:02> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=<CHANGE_ID> /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_' at line 81] in EXECUTE("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 .... - raul102801 9 years ago
    • Change all the things that are in bold in the query. That would be the k1000 website and the distributionemail. Make sure you don't include the bottom part of the text starting with "Then Email each ..." That is a separate config you need to do. - h2opolo25 9 years ago
      • Thanks for the response... I did only change the bold kboxwebsite for the url of my k1000 and distribution list with the e-mail address that covers the whole group of people and made sure that I only copied and pasted the parts from the beginning to "Having 1=1" and I still get an error on the same line:

        /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>

        I am thinking that is the line based on the error message:
        02/26/2015 10:43:22> Starting: 02/26/2015 10:43:22 02/26/2015 10:43:22> Executing Select Query... 02/26/2015 10:43:22> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=<CHANGE_ID> /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_' at line 79] in EXECUTE("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',

        Since the script worked for hutcha4113 I am guessing I might be doing something wrong but the instructions you gave are pretty straight forward so I don't know what I could be doing wrong. I just copied your script and pasted into notepad ++ replaced kboxwebsite with k1000@mycompany.com and replaced Distribution Email with networksupport@mycompany.com and then used the second part of your script to create the Message to be sent to the techs that are part of the group.
        Any ideas as to why this might be failing? - raul102801 9 years ago
  • Thanks. This script is exactly what I was looking for. Works like a charm. - hutcha4113 9 years ago
    • hutcha4113 did you have to do anything special or just copy and paste and modify the kbox url and the distribution e-mail? I am having issues with it but I don't know if it is something I am doing or if this has to do with my version of the k1000 of what..... I am using 6.2.109330 - raul102801 9 years ago
      • Just cut/paste, edit the bold fields. When the rule runs, you should see it in the log of the rule - it should tell you there why it is failing.

        So in the configuration of the rule you should have:

        1) In the first box where it says SELECT SQL everything from the Select statement to the end where is says Having 1=1.

        2) Checkmark in "Email each recipient in query results"

        3) In "Subject" box [TICK:$ticknum] NEW TICKET: $title.

        4) In "Column containing email address" NEWTICKETEMAIL (which is part of the Select statement code above.

        5) In "Message" - whatever you want...but this is what I have. $submitter_fname has opened a ticket.
        The submission was:
        Ticket: $ticknum
        From: $submitter_fname ($submitter_email)
        Category: $category
        Priority: $priority
        Status: $status
        Severity: $impact
        Opening Comment: $comment

        For more details go to:
        http://MYKBOX/adminui/ticket?ID=$id

        (I am going to the AdminUI, rather than UserUI as that is where the staff work).

        I am running version 6.3 - so what you mentioned is possible. I don't know SQL code so cannot answer that one.

        Everytime the rule runs, there is a log "Last Run Log". That should tell you what is happening, both good and bad.

        5 - hutcha4113 9 years ago
    • Thanks for the detailed explanation hutcha4113... This looks exactly like what I have done in every step. I did look at the log which is where the error message I pasted above is coming from but I don't know SQL either so I am not really sure what it is that is saying is wrong on the mentioned line.... I'll keep trying to figure it out. Thanks again - raul102801 9 years ago
      • Oups..sorry...just saw that you posted the error. If I knew SQL I would help - but anything I would post is not useful....lol. I generally involve my SQL guy when I run into issues on this. Even though he is MS SQL, and this is MY SQL the general principals are the same, so he fixes it for me.

        You may be correct on the version. I did not try this script until the date posted - which is after we had upgraded to 6.3 - hutcha4113 9 years ago
Posted by: Chris.Burgess 9 years ago
Orange Belt
0
If you click on Support Desk -> Configuration -> Support Desk Queues you will see the list of all queues.  DO NOT CLICK on the queue, just hover your mouse over the name.  At the bottom of the screen (just above your task bar) you should see what the link points to and that will include the queue number.  It will look something like this:

http://<kaceurl>/adminui/queue.php?ID=3  

This means that the queue you are looking at is number 3.
Posted by: Chris.Burgess 9 years ago
Orange Belt
0
Also, I'm not sure if this will work, but you can try making a custom ticket rule in each queue similar to this:

SELECT HD_Ticket.*,
               HD_Status.Name as STATUS_NAME
FROM     HD_Ticket
WHERE   STATUS_NAME = "New"

select the "Email Results" check box and enter the email address for the distribution list you said you created for each queue and set the frequency to on ticket save.  This should email everyone when a ticket gets created because all "created" tickets are automatically placed in the NEW status unless you have a rule that changes it of course.  If that is the case, then just set the priority of this rule to a lower number than the rule that changes the status so it runs first.
Posted by: raul102801 9 years ago
Orange Senior Belt
0

Thanks for the suggestions Chris. I copied and pasted your statements and this is what I get when I choose the run now option:

02/23/2015 11:09:12> Starting: 02/23/2015 11:09:12 02/23/2015 11:09:12> Executing Select Query... 02/23/2015 11:09:12> mysql error: [1146: Table 'ORG1.HD_Ticket' doesn't exist] in EXECUTE("SELECT HD_Ticket.*, HD_Status.Name as STATUS_NAME FROM HD_Ticket WHERE STATUS_NAME = "New"")


Would I have to add a line somewhere specifying the queue ID I want to run this in?


Thanks

 
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