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

Share