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
Update Query
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?
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)
Please log in to answer
Posted by:
airwolf
14 years ago
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
14 years ago
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
14 years ago
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
14 years ago
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
14 years ago
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
14 years ago
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
14 years ago
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
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
14 years ago
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?
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
14 years ago
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
14 years ago
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.