/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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