K1000: Adding a comment to the query results of Pending Ticket notification
I have a custom ticket rule that sends query results to myself and my director when a ticket status is changed to pending. This is for the director to make sure that the ticket circumstances legitimately call for a pending status (i.e. a necessary piece of equipment is in the shipment process). The query results contain the date created, ticket ID, ticket title, owner name and submitter name, but my director would like to see the last comment on the ticket in the query results so he does not have to login to KBOX every time a ticket is set to "Pending". Here is the SQL for the rule:
select DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE as TITLE,
U1.FULL_NAME as OWNER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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_STATUS.NAME = 'Pending') and HD_TICKET.HD_QUEUE_ID = 1 )
I would like to add the last comment from the HD_TICKET_CHANGE table to the query results, and need to join the HD_TICKET_CHANGE table to the HD_TICKET table. I have tried a few different joins including:
LEFT JOIN HD_TICKET_CHANGE C on C.ID = HD_TICKET.ID
But when I try to add C.COMMENT to the Select statement on MySQL Workbench, I receive an error that says "syntax error, unexpected COMMENT_SYM, expecting END_OF_INPUT or ';'".
It would be a great help if anyone could identify the correct join or why this error occurs.
Thank you!
Answers (1)
So I'm going to use the FAQ from KACE as a reference here:
http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222
(Sending an email upon ticket creation)
You are 'right' that you want to join that table, but you need to make sure your joining it on the change ID.
So in your select field you want COMMENT (or C.COMMENT if you so desire).'
then your JOIN statement should be:
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
Your MYSQL is NOT going to like the CHANGE_ID part. The CHANGE_ID is a temp table made by the KACE html processes and the like, but this will grab all the change ID's that are related to the ticket. Just because it doesn't work in MYSQL, doesn't mean it doesn't work inside the KACE application, would requrie some 'real time' testing (but always take care testing rules in a PRODUCTION environment, as I've ran many-a-queries that have queried all of ORG1 on accident, thus, no bueno).
Comments:
-
Retraction: That join statement will grab the change ID that the ticket is producing at that point in time* not "all of them" - Wildwolfay 11 years ago
-
That is the correct join statement, but keep in mind that you'll also have to select the comment in order to include it in the email. The comment would be HD_TICKET_CHANGE.COMMENT. - chucksteel 11 years ago
-
AYE! I didn't alias HD_TICKET_CHANGE as C in my example, so the select part would be HD_TICKET_CHANGE.COMMENT, not COMMENT.
I will turn in my keyboard and mouse and wander off into the sunset, hanging my head low :( - Wildwolfay 11 years ago
-
Wait, don't wander off yet! Thanks for the quick reply. I added the join statement and HD_TICKET_CHANGE.COMMENT to the Select statement, so it looks like this:
select DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE as TITLE,
U1.FULL_NAME as OWNER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
HD_TICKET_CHANGE.COMMENT
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.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID where HD_PRIORITY.ID = HD_PRIORITY_ID
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( HD_STATUS.NAME = 'Pending') and HD_TICKET.HD_QUEUE_ID = 1 )
When I put it into the KBOX select query and click "View Ticket Search Results", I receive this syntax error:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HD_TICKET_CHANGE.COMMENT LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_I' at line 2] in EXECUTE("SELECT COUNT(*) FROM (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY) HD_TICKET_CHANGE.COMMENT 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.APPROVER_ID LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID where HD_PRIORITY.ID = HD_PRIORITY_ID JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID and HD_TICKET_CHANGE.ID= and HD_STATUS.ID = HD_STATUS_ID and HD_IMPACT.ID = HD_IMPACT_ID and HD_CATEGORY.ID = HD_CATEGORY_ID and (( HD_STATUS.NAME = 'Pending') and HD_TICKET.HD_QUEUE_ID = 1 ) and HD_TICKET.HD_QUEUE_ID = '1' LIMIT 1")
So it seems as though there is still a Join issue. I feel like it is so close, any idea of what else I need to change to make this statement execute? - Nhall15 11 years ago-
HD_TICKET_CHANGE.COMMENT is in the wrong place, it's after the FROM Statement.
Move it up to where it should be :) (and don't forget the comma after whatever select field you add it to)
HD_TICKET.TITLE as TITLE,
U1.FULL_NAME as OWNER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
HD_TICKET_CHANGE.COMMENT
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
HD_TICKET_CHANGE.COMMENT - Wildwolfay 11 years ago-
Oops, Silly mistake. I moved it up. Do I add HD_TICKET_CHANGE to the from statement as well? - Nhall15 11 years ago
-
You shouldn't need to, since you are joining HD_TICKET_CHANGE below in the join statement. I would move that select field up to where it should be and give it a try, and (I think I mentioned this) you have to manually enter a number in the <CHANGE_ID> OR test the rule by making a ticket do what follows the rule, as running the rule stand alone will produce nothing since no change ID was generated, since no ticket was changed. - Wildwolfay 11 years ago
-
Thanks so much! I'll give it a try and test the rule with a ticket right now. - Nhall15 11 years ago
-
It did not send any email notification when I tested the rule. I retested the old rule to make sure it wasn't due to any other factors, and it sent the notification without the comment, as it is supposed to. Changing the ticket to a status of pending should have produced a new change ID, right? - Nhall15 11 years ago
-
Did you duplicate the old rule to make the new one? If so then the new rule might not be active and set to run on save. - chucksteel 11 years ago
