I must be missing it. I feel like it's right in front of me and I'm not finding the way to configure this.

Here's the intended behavior:

1. User e-mails officesupport@mycompany.com
2. When ticket is created in K1000, send an e-mail to members of that Queue

This seems incredibly easy but has proved to be difficult. What is the best practice here? How should I be setting the queue up for this behavior?

I tried using the wizard, which you would assume should be simple for such a simple task, but it ends up spitting out all these unnecessary fields. I don't need a thousand blank fields, all I really need are th following:

* Ticket ID #
* Status
* Name
* Summary
* Time Created
* E-mail

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
Probably one of the most frequently viewed KB articles:

Answered 09/21/2017 by: chucksteel
Red Belt

  • You're right. It probably is one of the most frequently viewed KB articles and asked questions as it's fairly convoluted, not to mention, why it's not just an option as simple as ticking a box. The amount of information generated by using the SQL Wizard itself is far too much for a simple notification.

    I followed the guide but it's still not working. I've changed the variables and tested by submitting a ticket yet it still does not generate an e-mail to the intended distribution group.

    Not sure what the problem could be. The kbox address is correct, the distribution group was newly created.
    • Please post the SQL for your select statement and the options you selected for the rule.
      • I think I've narrowed this down a bit. When setting the submitter_email as the column --

        SUBJECT:[TICK:$ticknum] $title
        EMAIL COLUMN:SUBMITTER_EMAIL

        It works.

        However, when setting it as NEWTICKETEMAIL --
        SUBJECT:[TICK:$ticknum] NEW TICKET: $title
        EMAIL COLUMN:NEWTICKETEMAIL

        That variable does not work.

        This is a snippet from the SQL code --

        -- other fields
        -- -- example of static distribution list
        'OfficeSupport@mycompanyname.com' AS NEWTICKETEMAIL -- $newticketemail
        FROM HD_TICKET

        Obviously, I've changed the "mycompanyname.com to my distribution list. When the field 'column containing email addresses:' is set to SUBMITTER_EMAIL, it will send the notification to the submitter (obviously not what I want).

        When it is set to NEWTICKETEMAIL, nothing happens.
      • Here is the entire code minus the private info. Options selected:
        * Enabled
        * Email each recipient in query results
        * Frequency - on Ticket Save

        That's it.

        SQL ---

        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://helpdesk.mycompanyname.com/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
        'OfficeSupport@mycompanyname.com' 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 '%CREATED%'
        /* this is necessary when using group by functions */
        GROUP BY HD_TICKET.ID
        HAVING 1=1
      • Yeah, all of that should work. Try turning on the option to email the results. This will send you an email with a table showing the query results and helps for debugging.
      • OK, so the SQL all appears correct. The e-mail result returns an e-mail including the NEWTICKETEMAIL field with the correct e-mail address --

        ---
        Here's the e-mail I get. Sorry about the formatting but it appears to be working, just not sending an e-mail to my distribution list which I know for sure works
        ---

        Newticketemail
        OfficeSupport@mycompany.com

        New Office Support Ticket
        New Rule
        # Id Ticknum Title Created Modified Comment Description History Updater Uname Updater Fname Updater Email Updater Conditional Owner Uname Owner Fname Owner Email Owner User Submitter Uname Submitter Fname Submitter Email Priority Status Impact Category Newticketemail
        1 23698 23698 Test 2 3 4 Oct 05 2017 11:11:32 AM Oct 05 2017 11:11:32 AM Hello, please send the debugging e-mail if it works. Ticket Created Added attachment "winmail.dat" ----- Change by me@mycompany.com at 2017-10-05 11:11:32 ----- Ticket Created Added attachment "winmail.dat" Hello, please send the debugging e-mail if it works. Please see your ticket at http://helpdesk.mycompany.com/userui/ticket.php?ID=23698
        amartin Adrian Martin me@mycompany.com Adrian Martin Unassigned amartin Adrian Martin me@mycompany.com Medium- It can wait 24hrs New Affects work productivity for team Desk issue OfficeSupport@mycompany.com.com
    • Are you sure mail routing is working correctly? I'm assuming that your email account and the distribution list are on the same domain, but perhaps they aren't.
  • Version: 7.0.121306 just in case that makes a difference but the guide says it should work for this version.
    • This content is currently hidden from public view.
      Reason: Removed by member request
      For more information, visit our FAQ's.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity