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!

0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Member is not approved yet
    For more information, visit our FAQ's.
  • This content is currently hidden from public view.
    Reason: Member is not approved yet
    For more information, visit our FAQ's.
Please log in to comment

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).

Answered 09/11/2013 by: Wildwolfay
Red Belt

  • Retraction: That join statement will grab the change ID that the ticket is producing at that point in time* not "all of them"
  • 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.
    • 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 :(
  • 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?
    • 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
      • Oops, Silly mistake. I moved it up. Do I add HD_TICKET_CHANGE to the from statement as well?
      • 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.
      • Thanks so much! I'll give it a try and test the rule with a ticket right now.
      • 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?
      • 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.
Please log in to comment
Answer this question or Comment on this question for clarity