I found this link on how to setup an email to me emailed when i new ticket is created:  http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222&action=artikel&cat=8&id=613&artlang=en

 

But when i input the sql and run it, KACE give me an error of :

 

04/04/2014 08:47:51> Starting: 04/04/2014 08:47:51
04/04/2014 08:47:51> Executing Select Query...
04/04/2014 08:47:51> 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 '=<CHANGE_ID>

 

Any idea what i need to change?

6 Comments   [ + ] Show Comments

Comments

  • Please post your select statement so we can see if there are other errors in it.
  • Please post your select
  • 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://kbox/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>

    /* 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 'TICKET CREATED%'

    /* this is necessary when using group by functions */

    GROUP BY HD_TICKET.ID

    HAVING 1=1
  • I assume the table name has changed in 5.5, which is the version i'm on.
    • No, the table names are still the same so I'm not sure what the problem is.
  • I"m still very new to SQL. Does this part look correct? It is the section that errors.

    /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

    AND C.ID=<CHANGE_ID>
    • I don't have the aliases for the tables but that's the same as my join statement for getting the most recent comment.
  • Hmm.. should i open a ticket with KACE Support on this one?
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity