Email to submitter on ticket creation
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]
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]
0 Comments
[ + ] Show comments
Answers (5)
Please log in to answer
Posted by:
bmatore
14 years ago
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.
Posted by:
dchristian
14 years ago
Stubox,
I think this will work for you. Hope it helps
Here is the main select:
Also here is the text in the mail message.
This can be customized to use any of the fields being selected above.
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>
Posted by:
stubox
14 years ago
Thanks dchristian ! This works great!
I'm sure others will find this very useful too
Thanks again
StuBox
I'm sure others will find this very useful too
Thanks again
StuBox
Posted by:
stubox
14 years ago
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,
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,
Posted by:
dchristian
14 years ago
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".
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.