Recently we got into a battle with a vendor's autoreponder and our service desk "New Ticket Via Email" configuration.  We accept email from unknown emails as our customers are students and faculty who tend to use gmail or other email accounts; our staff use the provided email address.  

A vendor sent out an email stating how wonderful their latest release is and it came into our support email address.  Our service desk responded to their Reply-To email address, which was the vendor's ticking system.  The vendor's ticketing system then sent a brand new "We got your email blah, blah, blah..." which our service desk responded to with a "We got your email blah, blah, blah."

When this first happend I just temporarily disabled the "New Ticket Via Email" button for the queue impacted.  This stoped the loop.  Then it happend a second time over the weekend, luckly I caught it after two hours but we still had to clean up a ton of emails.

So I set out to fix the "New Ticket Via Email" and only send a response out to domains of our choosing; in this case, company provided.  If someone sends in an email from a gmail account they will not get the auto response message but they will see our comments as we work with the ticket.  We are currently testing this out, but so far it seems to work.  We know that the "New Ticket Via Email" is sent out instantly whereas Custom Ticket Rules run every 3 minutes; we can live with this.    

 

To do this:

1. disable the "New Ticket Via Email"

2. Create a new Custom Ticket Rule where the frequency is "on Ticket Save"

3. Here is the SQL

Select
HD_TICKET.ID As ticket_number,
HD_TICKET.ID As TICKNUM,
'<SomeCommaSeperatedEmailsGoesHereWithoutTheBrackets>' As EMAILCC,
HD_TICKET.TITLE As 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,
HD_STATUS.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,
U3.FULL_NAME As UPDATERNAME,
Unix_Timestamp(TICKETCHANGE.TIMESTAMP),
TICKETCHANGE.COMMENT As COMMENT,
TICKETINITIAL.COMMENT As INITIAL_COMMENT,
TICKETCHANGE.DESCRIPTION As CHANGE_DESCRIPTION,
HD_CATEGORY.CC_LIST As CATEGORYCC,
HD_CATEGORY.NAME As CATEGORY_NAME,
U2.LOCATION As SUBMITTER_LOCATION,
U2.WORK_PHONE As SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME As TICKET_PRIORITY,
HD_QUEUE.NAME As QUEUE_NAME
From
HD_TICKET Join
HD_TICKET_CHANGE TICKETCHANGE On TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID Join
HD_TICKET_CHANGE TICKETINITIAL On TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
And TICKETINITIAL.ID = (Select
Min(HD_TICKET_CHANGE.ID)
From
HD_TICKET_CHANGE
Where
HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) 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 = TICKETCHANGE.USER_ID Left Join
HD_QUEUE On HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY
Where
HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And
HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And
HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And
HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And
(HD_STATUS.NAME Not Like '%Closed%' And
U2.EMAIL Like '%@someDomainGoesHere%' And
TICKETCHANGE.DESCRIPTION Like '%Ticket Created%' And
HD_TICKET.HD_QUEUE_ID = 9)

4. Select "Send an email for each result row"

5. For subject we used "Service Desk - [$ticket_number] $ticket_title"

6. For the Email Column use "SUBMITTER_EMAIL"

7. For the body use something like this:

Hello,

We wanted to let you know that we have received your email, "$ticket_title", and will be taking care of it as soon as possible.

There is no need to reply right now, this is automatically generated to let you know we received your email.

If you would like to follow up about this issue please make sure that "Service Desk - [$ticket_number] $ticket_title" is in the subject line of all the emails you send regarding this.

Note that system outages are posted here: <someWebSite>

For urgent issues, please call:
<someNumbers>
Thanks,
<someOne>
<someURL>

 


Let me know what you think!  

 

 

 

Updated on 7.9.2013, I found an error in my sql.