/bundles/itninjaweb/img/Breadcrumb_cap_w.png
I am working to get our HelpDesk email notifications working. Two weeks ago, it was working because I was testing something else and the notification emails were being sent. I disabled them (without editing the SQL in the rule itself) through the queue email settings. Now, for some reason when I am trying to actually get the email notifications sent, I am getting syntax errors. So I do not understand what would have changed in just a couple of weeks. I am the only person working on it at this time, so I know no one else has made any changes to the rule. I did change a few field labels in KACE in one queue but not in the other. Both queues are showing the same syntax errors. This was also written before I joined the team. I am still relatively new to KACE so I may just not know the proper names of the tables and columns.

We have KACE 8.0.318. I believe that when this was installed (before my hire) it was 8.0 so our system did not expereince the name change in teh database that caused issued for others when updated to 7.0. (https://support.quest.com/kb/218437) (https://support.quest.com/kace-systems-management-appliance/kb/223753) I have checked the manual and preformed several searches on the IT Ninja boards. I have yet to find anything that breaks through my lack of understanding the cause of the error.

Here are the lines of SQL in question:

     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID

     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID

     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID

     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID

     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID

     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID

     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID


I know for a fact that the first two are giving me the error message because I deleted the first line and ran it only to get a similar syntax error message on the second one. Instead of trying them all, I would like for some more experienced eyes to see if they can find what I am missing.


Here is the error message for the complete join history statement.

There were syntax errors in your query.

mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID' at line 41]

3 Comments   [ - ] Hide Comments

Comments

  • I know it's a pain in the ass but try removing the aliases. My own instance of the K1000 began having a similar problem after upgrading to version 7. I ended up having to remove the aliases to get the rules to work again.
  • Can you please post the entire query?
    • Sure.
      SELECT
      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
      H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://k1000/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
      ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
      -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
      -- about status
      S.NAME AS STATUS, -- $status
      -- about impact
      I.NAME AS IMPACT, -- $impact
      -- about category
      CAT.NAME AS CATEGORY, -- $category
      -- other fields
      -- -- example of static distribution list
      'email@gmail.com' AS NEWTICKETEMAIL -- $newticketemail
      FROM HD_TICKET
      /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
      AND C.ID=<CHANGE_ID>
      /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
      /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
      /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
      /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
      /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
      /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
      /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
      /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
      WHERE
      C.DESCRIPTION LIKE '%CREATED%'
      /* this is necessary when using group by functions */
      GROUP BY HD_TICKET.ID
      HAVING 1=1
      • Can you verify that the rule has the opening angle bracket and closing angle brackets, and not the HTML escape codes? Sometimes the formatting here makes it hard to tell.
      • chucksteel, I do not see any angle brackets in the Ticket Rule Detail page where I copied and pasted this from. In that box on that page, it is exactly as it appears here. I took screenshots but cannot share them here.
        I added angle brackets to the beginning and end and test it. I got a syntax error on line 1 because of the angle bracket.
    • Sorry, I wasn't very specific. I was referring to the angle brackets around the CHANGE_ID variable in this line:
      /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
      AND C.ID=<CHANGE_ID>
      • Those are what I have been taught to call syntax wrappers. The greater than and the less than sign.
      • Any ideas?
      • Are you still getting the same error in the run log?
      • Yes. I have made no changes to the syntax other than the temporary change for the test where I misunderstood the question. The error still persists.
      • And you verified that there are less than and greater than signs around the CHANGE_ID variable?
      • Yes. Those are less than and greater than signs.
      • Any more information available?
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • I can't tell what the problem might be. I copied your query into MySQL Workbench and replaced <CHANGE_ID> with the change ID of a recently created ticket and it works.
    • Based on what I have been learning from here and Quest support, I have a question about where CHANGE_ID is being declared. In a ticket rule, the needed ID is declared in the select statement where the Update statement tells how to use that variable. Since CHANGE_ID is being called in the select statement here, where is it being declared so that it knows what to use as the change_id?
      • You are confusing CHANGE_ID and TICKET_IDS. The CHANGE_ID variable is defined on ticket run and is only available for rules that run on ticket save, I forgot to ask if that is how your rule is configured, previously.

        The update statement can use the TICKET_IDS variable. It is populated by the values returned by the select statement in the column named ID.
    • Thank you, Chuck, for all the help that you have provided. I have been asking a lot of questions lately so I appreciate your time and attention to these matters.
Please log in to comment

There are no answers at this time
Answer this question or Comment on this question for clarity

Answers