/build/static/layout/Breadcrumb_cap_w.png

Syntax errors on rule for notifying IT on ticket submissions.

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]

Asked 6 years ago 1204 views
3 Comments   [ + ] Show 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. - Druis 6 years ago
  • Can you please post the entire query? - chucksteel 6 years ago
    • 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 - jessburd 6 years ago
      • 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 6 years ago
      • 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. - jessburd 6 years ago
    • 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> - chucksteel 6 years ago
      • Those are what I have been taught to call syntax wrappers. The greater than and the less than sign. - jessburd 6 years ago
      • Any ideas? - jessburd 6 years ago
      • Are you still getting the same error in the run log? - chucksteel 6 years ago
      • 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. - jessburd 6 years ago
      • And you verified that there are less than and greater than signs around the CHANGE_ID variable? - chucksteel 6 years ago
      • Yes. Those are less than and greater than signs. - jessburd 6 years ago
      • Any more information available? - jessburd 6 years ago
  • 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. - chucksteel 6 years ago
    • 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? - jessburd 6 years ago
      • 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. - chucksteel 6 years ago
    • 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. - jessburd 6 years ago

Answers (0)

Be the first to answer this question

 
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