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]
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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.
Answered 11/09/2010 by: bmatore
Orange Senior Belt

Please log in to comment
0
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>


Answered 11/10/2010 by: dchristian
Red Belt

Please log in to comment
0
Thanks dchristian ! This works great!

I'm sure others will find this very useful too

Thanks again

StuBox
Answered 11/11/2010 by: stubox
Blue Belt

Please log in to comment
0
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,
Answered 11/12/2010 by: stubox
Blue Belt

Please log in to comment
0
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
Answered 11/12/2010 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity