Ticket Rule to send New Ticket Creation to Submitter
We don't allow users to submit tickets via email. The submitter either has to call the TSC or go to kbox and open their own ticket.
The submitters want a new ticket created email if they call the TSC or if the go to kbox and input themselves.
I have tried several different suggestions via articles on this site and on Kace's site and keep getting SQL errors.
Version 5.4 is what we are using.
-
This works great and sends the new ticket email creation right away but when the owner makes a comment in the ticket to the submitter instead of sending the ticket comment it sends this new ticket creation email again. I only want this to go to the submitter once and that is when the ticket is created. After that I don't want it to be sent again. - scarpent 11 years ago
-
I am testing by creating new tickets. The email is not sending at all now to anyone, not sure but it may be a gmail problem. When it does send an email to the submitter and the submitter replies to that email it sends this email again to the submitter. It just keeps sending over and over again. I only want this email to go to the submitter under the following circumstances: The submitter inputs their own ticket, the submitter calls the Tech Support Center and they put a ticket in for the submitter under their name. It would have been nice if Kace would have created an option to send a new ticket email when clients called a Help Desk and had them put the ticket in for them instead of only allowing the default email to work if the client emails a ticket. Clients do not always have access to the internet to put in their own ticket they can only call in to the Help Desk. We don't do emails because we find the clients put in so little information we are spending all our time calling them back requesting more info. If they call the Tech Support Center they can ask more questions and if the client puts in the ticket themselves there are required fields. I will keep working at testing this to see if I can get it to work. - scarpent 11 years ago
-
OK here is what is happening. I turned off this rule and went back to using the generic built in incident change email and I only have for submitter the comment and resolution email on events check marked. When the user makes a comment via email it goes into the ticket and I get an email letting me know what that update is. When an owner makes a comment in the ticket for the submitter all they are getting in their email is the generic incident change email. They are not getting the comment the owner made. I need for the user to get the comment made so that they can respond back in the email instead of always having to go into their ticket. - scarpent 11 years ago
-
With having Gmail problems and then the email for some reason being in a continual loop this was very frustrating but now working. Thanks - scarpent 11 years ago
Answers (3)
See if this will work for you:
Note: make sure you create this custom ticket rule with the queue you want it to work in and change the Queue ID numerb.
Here is the SQL:
Select
HD_TICKET.ID As ticket_number,
HD_TICKET.ID As TICKNUM,
'somedefaultEmails@myCompany.com' 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 '%@myCompany%' And
TICKETCHANGE.DESCRIPTION Like '%Ticket Created%' And
HD_TICKET.HD_QUEUE_ID = MyQueueChangeThisNumber)
Comments:
-
also, don't forget to enable it ;) - Jbr32 11 years ago
-
What are you using the cc field for and do you have an update query also running so that this only goes out to the submitter when they create the ticket? - scarpent 11 years ago
-
I'm sending the email to SUBMITTER_EMAIL, not using the CC and I have a "Run update query" to update the MAILED_TIMESTAMP
The email is only sent once and it does not break other mailing functionality as the column MAILED is used by other process and not the time-stamp.
Here is the WHERE clause added to the select of the rule:
And
TICKETCHANGE.MAILED_TIMESTAMP = '0000-00-00 00:00:00'
The "Run update query" is:
UPDATE ORG1.HD_TICKET_CHANGE SET MAILED_TIMESTAMP = NOW() WHERE MAILED = 0 AND DESCRIPTION Like '%Ticket Created%' - sirbadchild 10 years ago
You are probably getting errors when hitting the run now button, or trying to test it because the ticket_change_id is a field that must be checked against. if your using <CHANGE_ID> then the rule will not work unless you test it by actually creating a ticket or add in a manual CHANGE_ID number (which will be in your HD_TICKET_CHANGE table.)
The KACE provided rule from the FAQ works, just not when you sit there and press the button, because when you do that, there is no ticket-change-id being made so the rule has nothing to work off of.
Test the original FAQ rule by making a ticket, then go check the rule and see what it's run log says.
