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. 

Answer Summary:
Cancel
4 Comments   [ + ] Show Comments

Comments

  • 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.
  • 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.
  • 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.
  • With having Gmail problems and then the email for some reason being in a continual loop this was very frustrating but now working. Thanks
Please log in to comment

Answers

0

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)

 

 

 

Answered 11/05/2013 by: Jbr32
Tenth Degree Black Belt

  • also, don't forget to enable it ;)
    • 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?
      • 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%'
Please log in to comment
0

Here is the other image that got cut out of the above answer.  

Answered 11/05/2013 by: Jbr32
Tenth Degree Black Belt

Please log in to comment
0

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.

Answered 11/08/2013 by: Wildwolfay
Red Belt

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

Share