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

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
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.
Answered 04/13/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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....
Answered 04/13/2010 by: grico
Senior Yellow Belt

Please log in to comment
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.
Answered 04/13/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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?
Answered 04/13/2010 by: grico
Senior Yellow Belt

Please log in to comment
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.
Answered 04/13/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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>))
Answered 04/13/2010 by: grico
Senior Yellow Belt

Please log in to comment
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
Answered 04/13/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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?
Answered 04/13/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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
Answered 04/13/2010 by: grico
Senior Yellow Belt

Please log in to comment
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.
Answered 04/13/2010 by: GillySpy
Seventh Degree Black Belt

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