/build/static/layout/Breadcrumb_cap_w.png

New Ticket Email Notification

I am using the following script to allow me to recieve an email notification when a new ticket is created.

Select Query
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
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,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'_helpdesk@uslegalsupport.com' as NEWTICKETEMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP)

from HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY,
HD_TICKET_CHANGE left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.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.HD_TICKET_ID= HD_TICKET.ID and
HD_TICKET_CHANGE.ID IN (
select max(id) from HD_TICKET_CHANGE WHERE HD_TICKET_CHANGE.HD_TICKET_ID=HD_TICKET.ID
and TIMESTAMP > DATE_SUB(NOW(),INTERVAL 6 HOUR) and OWNERS_ONLY <>1 GROUP BY HD_TICKET_ID
) and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'


Update Query
update HD_TICKET as T
set T.CC_LIST = '
where
(T.ID in (<TICKET_IDS>))


This script has been working great, the only problem is that some times I get duplicate emails, and in some instances 7 emails for one new created ticket. Can anyone tell me what I may have done wrong, or if there is something I can add to this script to correct me issue?

0 Comments   [ + ] Show comments

Answers (10)

Posted by: airwolf 13 years ago
Red Belt
0
Do you have the schedule for the rule set to "On Ticket Save"? If so, that's the problem. OTS rules run whenever ANY ticket is saved. You'll have to set the rule up for a 15 minute schedule and detect newly created tickets in the past 15 minutes.
Posted by: grico 13 years ago
Senior Yellow Belt
0
I have found that the main reason I get multiple emails is that when an enduser is creating a new ticket they hit "Save" multiple times for one ticket. Even though it is only creating one ticket we get multiple emails. Is there a way to remove or gray out the save button once they hit it so that they cant hit it again. The real reason they hit save multiple times is there is no real indication that a ticket was created and they are unsure so they hit save again, and again, and again, ect....
Posted by: airwolf 13 years ago
Red Belt
0
I have found that the main reason I get multiple emails is that when an enduser is creating a new ticket they hit "Save" multiple times for one ticket.
... because it's set to an OTS schedule.
Posted by: grico 13 years ago
Senior Yellow Belt
0
I understand that, but If i put it on a 15 min schedule then it can be 30 min before I am notified that I have a new ticket. I have told my endusers that I will assign tickets within 15 min so this will not work for me. Is there a way to only allow them to hit save once?
Posted by: airwolf 13 years ago
Red Belt
0
Is there a way to only allow them to hit save once?
Nope. OTS is not really meant for a rule like this, but you could develop a workaround by stamping the ticket in the Update Query with a comment that says something like "Email Notification of New Ticket Sent". Then just add a WHERE clause in the Select Query to exclude results that have that comment.
Posted by: grico 13 years ago
Senior Yellow Belt
0
ORIGINAL: airwolf
Then just add a WHERE clause in the Select Query to exclude results that have that comment.


I am not familuar with editing the script, however I can copy and paste. Can you please advise me on how I should edit this script to make these changes.

update HD_TICKET as T
set T.CC_LIST = '
where
(T.ID in (<TICKET_IDS>))
Posted by: GillySpy 13 years ago
7th Degree Black Belt
0
Are you perhaps using an old version of this faq?
http://www.kace.com/support/customer/faq/index.php?action=artikel&cat=8&id=613&artlang=en

The link i just sent uses version 5 syntax which will avoid the problem of multiple clicks on the save button
Posted by: airwolf 13 years ago
Red Belt
0
Wow. I'm surprised to see you suggesting the use of OTS for such a rule. On a heavily used KBOX, this rule is going to run quite often.

I'm curious; how does the new syntax in the article avoid multiple clicks? Is it the addition of the <CHANGE_ID> section of the WHERE clause?
Posted by: grico 13 years ago
Senior Yellow Belt
0
ORIGINAL: GillySpy

Are you perhaps using an old version of this faq?
http://www.kace.com/support/customer/faq/index.php?action=artikel&cat=8&id=613&artlang=en

The link i just sent uses version 5 syntax which will avoid the problem of multiple clicks on the save button



I was using an older version. This has seemed to resolve the issue.

Thanks
Posted by: GillySpy 13 years ago
7th Degree Black Belt
0
Wow. I'm surprised to see you suggesting the use of OTS for such a rule. On a heavily used KBOX, this rule is going to run quite often.

I'm curious; how does the new syntax in the article avoid multiple clicks? Is it the addition of the <CHANGE_ID> section of the WHERE clause?

Yes, it will run frequently, but should be evaluated quickly. Most customers do not have a lot of rules.

You are correct again, the new syntax avoid multiple clicks becaue only one of those clicks will actually cause a change thus <CHANGE_ID> will only have a value on one of those clicks.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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