/build/static/layout/Breadcrumb_cap_w.png
09/26/2018 242 views
I upgraded to 9.0.270 and my ticket rule that notified the ticket owner a new ticket has been assigned is no longer working. This used to notify the default user assigned to that queue they had a new ticket.  I can't figure out what changed but the error seems to be on the line in the SQL highlighted

The SQL is this:
SELECT
      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://support.csuniv.edu/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
   'helpdesk@mycompany.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)    --I changed the ( ) to brackets
     /* 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 DESC
    HAVING 1=1

The error in the last run log is 

09/26/2018 08:36:08> Starting: 09/26/2018 08:36:08 09/26/2018 08:36:08> Executing Select Query... 09/26/2018 08:36:08> 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_TICKE' at line 40] in EXECUTE("SELECT 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://support.csuniv.edu/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 'helpdesk@mycompany.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 = /* 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 DESC HAVING 1=1 and (HD_TICKET.ID = 24579) ")
Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • OK this may be a stupid question but where do you put this script?

    I thought this option to email a ticket owner on new ticket was a default option and in the past a person did get notified that a new ticket was assigned to them but since the 9.0.270 update I am told that they do not get the emails anymore.
  • All my owners stopped getting email notifications as well.

Answer Chosen by the Author

2
You seem to be missing a character. I use the same script. Mine reads 

AND C.ID=<CHANGE_ID>

Your syntax is missing the underscore
Answered 09/26/2018 by: Druis
Second Degree Green Belt

  • Thanks, that was it. I wonder what happened to cause that _ to go missing?