Good Day Everyone,

   How is everyone doing? The issue at hand is dealing with the Kace K1000's Service Desk Email notification. I am currently trying to create a custom ticket rule that emails a group of people whne a new ticket is generated within a queue.

 

A Dell Kace tech point me in the direction of this URL - 

http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222

 

This URL contains the SQL code below that is either full of errors or does not work. Can someone assist me the proper SQL code that generates an email to let my IT Department of when a user opens a new ticket via the Kace web interface.

SQL Code Does Not Work:

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