K1000 - E-mail specific users when a ticket is created via e-mail.

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

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 3 years ago
Red Belt

Top Answer

Probably one of the most frequently viewed KB articles:

  • 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. - adrianK 3 years ago
    • Please post the SQL for your select statement and the options you selected for the rule. - chucksteel 3 years ago
      • I think I've narrowed this down a bit. When setting the submitter_email as the column --

        SUBJECT:[TICK:$ticknum] $title

        It works.

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

        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

        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. - adrianK 3 years ago
      • 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 ---

        -- 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
        -- 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
        /* 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
        /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
        /* this is necessary when using group by functions */
        HAVING 1=1 - adrianK 3 years ago
      • 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. - chucksteel 3 years ago
      • 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


        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 - adrianK 3 years ago
    • 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. - chucksteel 3 years ago
  • Version: 7.0.121306 just in case that makes a difference but the guide says it should work for this version. - adrianK 3 years 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


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