Email to submitter on ticket creation

Question

Hi
 
 As per the Email-on-Events -  "New Ticket Via Email" ,  the Kbox will send a confirmation email to say a ticket has been created.  This is great.
 
 However if I log a ticket (not via email),  neither the Submitter or Owner get an email to say a ticket has been created.  If for example I've taken a phone call from a user and a different engineer is going to deal with the issue,  I'd need the user to receive an email so they know I've logged a ticket, and the engineer needs to know they have a new job to do.
 
 I've read this article (http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=613&artlang=en)  which allows for an email to be sent everytime a new ticket is created to a specific address, which is close to what I need (I just want the submitter and owner to receive the email).
 
 Can anyone give some guidance on this?
 
 Thanks
 
 StuBox
 
 My email-on-events settings:
 
 [url=http://d.imagehost.org/view/0748/Email-on-events][/url]

Answer

apparently KBox will email submitters when they create a ticket via email only.If they or you create a ticket via the web UI, they will not receive a confirmation ticket.

Answer

Stubox,

I think this will work for you. Hope it helps

Here is the main select:



 SELECT MAINER.*,

        EMAIL.EMAIL AS NEWTICKETEMAIL 

 FROM   (SELECT HD_TICKET.ID                                            AS TICKNUM, 

                HD_TICKET.TITLE, 

                Date_format(HD_TICKET.CREATED, '%b %d %Y %I:%i:%s %p')  AS CREATED, 

                Date_format(HD_TICKET.MODIFIED, '%b %d %Y %I:%i:%s %p') AS MODIFIED, 

                HD_STATUS.NAME                                          AS STATUS_NAME, 

                HD_STATUS.ORDINAL                                       AS STATUS_ORDINAL, 

                STATE, 

                Coalesce(OWNER.FULL_NAME, 'NO OWNER ASSIGNED')          AS OWNER_NAME, 

                Coalesce(OWNER.EMAIL, 'NO OWNER ASSIGNED')              AS OWNER_EMAIL, 

                OWNER.ID                                                AS OWNER_ID, 

                Coalesce(SUBMITTER.FULL_NAME, 'NO SUBMITTER')           AS SUBMITTER_NAME, 

                SUBMITTER.ID                                            AS SUBMITTER_ID, 

                SUBMITTER.EMAIL                                         AS SUBMITTER_EMAIL, 

                Coalesce(LAST_MOD.FULL_NAME, 'NO LAST MODDIFIED USER')  AS LAST_MODIFIED_NAME,

                Unix_timestamp(HD_TICKET_CHANGE.TIMESTAMP) 

         FROM   HD_PRIORITY, 

                HD_STATUS, 

                HD_IMPACT, 

                HD_CATEGORY, 

                HD_TICKET 

                JOIN HD_TICKET_CHANGE 

                  ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 

                     AND HD_TICKET_CHANGE.ID = <CHANGE_ID>

                LEFT JOIN USER OWNER

                  ON OWNER.ID = HD_TICKET.OWNER_ID 

                LEFT JOIN USER SUBMITTER 

                  ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID 

                LEFT JOIN USER LAST_MOD 

                  ON LAST_MOD.ID = HD_TICKET_CHANGE.USER_ID 

         WHERE  HD_PRIORITY.ID = HD_PRIORITY_ID 

                AND HD_STATUS.ID = HD_STATUS_ID 

                AND HD_IMPACT.ID = HD_IMPACT_ID 

                AND HD_CATEGORY.ID = HD_CATEGORY_ID 

                AND HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%') MAINER, 

        (SELECT ID, 

                FULL_NAME, 

                EMAIL 

         FROM   USER OWNER) EMAIL, 

        HD_TICKET 

 WHERE  ( MAINER.OWNER_ID = EMAIL.ID 

           OR MAINER.SUBMITTER_ID = EMAIL.ID ) 

        AND HD_TICKET.ID = MAINER.TICKNUM

Also here is the text in the mail message.

This can be customized to use any of the fields being selected above.

$submitter_name ($submitter_email) has opened ticket # $ticknum.

 The current owner of this ticket is: $owner_name ($owner_email)

 Please click here to reply to this email 

 <mailto:kboxhelpdesk@yourcompany.com?subject=[TICK:$ticknum]> 

 or review it online at 

 <http://kbox.yourcompany.com/adminui/ticket?ID=$ticknum>

Answer

Thanks dchristian !  This works great! 
 
 I'm sure others will find this very useful too
 
 Thanks again
 
 StuBox

Answer

Been playing with this script and noticed if you set the Email field to OWNER_EMAIL it emails the owner twice.  What I was trying to acheive was have an email sent to just the owner when a ticket is created by someone else and assigned to them.  e.g. one engineer raising a new ticket for another engineer.
 
 But when one engineer creates and assigns the ticket to another engineer, the script generates 2 identical emails sent to the owner.  But if the engineer creates a ticket and assigns it to himself he only receives one email.
 
 One the line below I've tried adding in DISTINCT before Owner.Email,  and also tried it before Owner_Email but it doesnt like the syntax.
 Coalesce(OWNER.EMAIL, 'NO OWNER ASSIGNED')              AS OWNER_EMAIL,

Answer

This is working as expected. 
 
 The two rows will be identical expect for the "NEWTICKETEMAIL" column. 
 
 Therefore the owner will be listed twice and get 2 emails if your trying to email on the "OWNER_ID" column.
 
 To only have the owner emailed when a new ticket is assigned to him try this select and leave the email column as "NEWTICKETEMAIL".
 SELECT MAINER.*,
        EMAIL.EMAIL AS NEWTICKETEMAIL 
 FROM   (SELECT HD_TICKET.ID                                            AS TICKNUM, 
                HD_TICKET.TITLE, 
                Date_format(HD_TICKET.CREATED, '%b %d %Y %I:%i:%s %p')  AS CREATED, 
                Date_format(HD_TICKET.MODIFIED, '%b %d %Y %I:%i:%s %p') AS MODIFIED, 
                HD_STATUS.NAME                                          AS STATUS_NAME, 
                HD_STATUS.ORDINAL                                       AS STATUS_ORDINAL, 
                STATE, 
                Coalesce(OWNER.FULL_NAME, 'NO OWNER ASSIGNED')          AS OWNER_NAME,
                Coalesce(OWNER.EMAIL, 'NO OWNER ASSIGNED')              AS OWNER_EMAIL, 
                OWNER.ID                                                AS OWNER_ID, 
                Coalesce(SUBMITTER.FULL_NAME, 'NO SUBMITTER')           AS SUBMITTER_NAME, 
                SUBMITTER.ID                                            AS SUBMITTER_ID, 
                SUBMITTER.EMAIL                                         AS SUBMITTER_EMAIL, 
                Coalesce(LAST_MOD.FULL_NAME, 'NO LAST MODDIFIED USER')  AS LAST_MODIFIED_NAME,
                Unix_timestamp(HD_TICKET_CHANGE.TIMESTAMP) 
         FROM   HD_PRIORITY, 
                HD_STATUS, 
                HD_IMPACT, 
                HD_CATEGORY, 
                HD_TICKET 
                JOIN HD_TICKET_CHANGE 
                  ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 
                     AND HD_TICKET_CHANGE.ID = <CHANGE_ID>
                LEFT JOIN USER OWNER
                  ON OWNER.ID = HD_TICKET.OWNER_ID 
                LEFT JOIN USER SUBMITTER 
                  ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID 
                LEFT JOIN USER LAST_MOD 
                  ON LAST_MOD.ID = HD_TICKET_CHANGE.USER_ID 
         WHERE  HD_PRIORITY.ID = HD_PRIORITY_ID
                AND HD_STATUS.ID = HD_STATUS_ID 
                AND HD_IMPACT.ID = HD_IMPACT_ID 
                AND HD_CATEGORY.ID = HD_CATEGORY_ID
                AND HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%') MAINER,
        (SELECT ID,
                FULL_NAME,
                EMAIL 
         FROM   USER OWNER) EMAIL, 
        HD_TICKET
 WHERE MAINER.OWNER_ID = EMAIL.ID
   AND HD_TICKET.ID = MAINER.TICKNUM

Email to submitter on ticket creation

Answers (5)

Posted by:

View more:

Related Questions

Related Links

Related Posts

KACE Product Support

Email to submitter on ticket creation

Answers (5)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share