/build/static/layout/Breadcrumb_cap_w.png

Query Question

Hello!

Does anyone have a query written that will show when new comments are added to a ticket? I'm looking to have an email sent to the owner if the submitter adds new comments. I've ran into an issue that my end users are sometime going back into the ticket and adding more comments, but the tech doesn't know that if he's not paying attention to the comments. I know the KACE system has one built into it, but the 15 min waiting around to see if it’s going to work is too slow IMO.  BTW, those built in rules should be modifiable, just saying J!

Having a rule that will auto notify the owner of the ticket should resolve the issue for me.

Thanks,
Joey


0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: chucksteel 10 years ago
Red Belt
3

Here is the select statement for a rule which can be used to notify the user on comments:

 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, 
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, 
HD_PRIORITY, 
HD_STATUS, 
HD_IMPACT, 
HD_CATEGORY)
JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID 
 and TICKETCHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
 and TICKETINITIAL.ID=(select MIN(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

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
TICKETCHANGE.COMMENT != '' and
TICKETCHANGE.DESCRIPTION not like "Changed ticket Machine from%" and
TICKETCHANGE.DESCRIPTION not like "%Ticket Created%" and
HD_TICKET.HD_QUEUE_ID = 9 and
HD_STATUS.NAME != 'Closed'

The owner's email is returned as OWNER_EMAIL and the most recent comment is $comment for use in the email message.


Comments:
  • Chucksteel -

    This is getting me close. Just two problems. If there are multiple comments on the ticket it sends each comment as an email, not just the latest one. This results in multiple emails to the submitter. I just need the last comment sent from the ticket. I tried adding "GROUP BY HD_TICKET.ID" to the end of the query, but that doesn't show the last comment, in fact I'm sure how it picks the comments if you do the grouping.

    The last key piece of this is getting this line added into the query.
    TICKETCHANGE.DESCRIPTION LIKE '%Changed ticket Status from "%" to "Waiting On User"%'

    The end goal is when this status is set on the ticket the submitter will get an email with the latest comments on the ticket helping them know that they need to update the ticket with more information before we can move forward. - joeym304 10 years ago
    • Each comment is a change to the ticket, which triggers the rule (assuming it is set to run on save). You also originally asked about sending an email to the ticket owner and not the ticket submitter, so that would be a different rule. This one is similar, but like you said, not quite what you need.

      You could add the line you have above and it should limit it to changes in the status, but you could also check for that change in other ways. If you add that line then it should send the most recent comment. - chucksteel 10 years ago
  • You're right! I screwed up your query by chaning it to the submitter instead of keeping it to the owner. I need to study the query to see how I can do the same thing for the submitter. - joeym304 10 years ago
Posted by: nshah 10 years ago
Red Belt
2

You should only have to put a check box under the column for comment for Owner. It is found under Email on Events in the queue you are working in.

 

That should send an email to the owner of the ticket any time a comment is entered in the ticket. Even their own comment. 

Also check out

http://www.itninja.com/question/custom-ticket-rule-email-comment-1

http://www.itninja.com/question/trying-to-send-an-email-on-a-comment-in-a-ticket-not-working

 

Posted by: joeym304 10 years ago
Yellow Belt
1

 

Nshah,

Thanks for the response! I would use the built-in rules, but they only run on 15 min intervals and that doesn't always work out.

I've read the two links you sent, but they don't really seem to accomplish what I need to happen. What I'm trying to do is create a rule that will notice when "new" comments are added to a ticket and send them out to the owner. 

Writing the query to read the comments is something I can do, but I'm not sure how I can create a variable that will notice only when new comments are added to the ticket.  On ticket save, I want the query to look for any new comments added to the ticket.

Hopefully that makes more sense than the first post!


Comments:
  • What Nshah is referring to should work. When the ticket is saved any of the comments will be sent out. There should be no waiting of the 15 minutes. I think you are referring to escalation. - Timi 10 years ago
    • Hey Timi -

      Thanks for the post. I have tested what both you and Nshah have suggested and unless I'm missing something, that rule doesn't work "on ticket save". I'll get the email in a time frame between 1-15 mins later.

      Just to confrim what I have checked, under email on events, I have a check box under the "owner" section on the "Comment" box.

      Any suggestions? - joeym304 10 years ago
  • The only thing I can think that might be happening is (if you are using Exchange) there is a delay with e-mail from your Exchange server. Otherwise it should be happening and the fact you mention that it can take between 1-15 minutes leads me to the conclusion above. - Timi 10 years ago
    • Pretty sure this is the way "Email on Events" rules work. Maybe it's my setup, but it's not my exchange server. All my custom rules that I've writen come in and soon as the user hits save on the ticket button.

      Thank you and everyone else for helping me with this.

      If you're looking for a query that will only send out the newest comment made on a ticket, the rule above from chucksteel is prefect! - joeym304 10 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ