KACE Product Support

You need this: http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=613&artlang=en

Thanks GillySpy, I'm not getting email notifications for all new tickets.

Did you mean "NOW getting" instead of "NOT getting"? or is there something to troubleshoot?

Yes, I'm NOW getting the notifications. Thanks again.

I have followed instruction in the link but I am having two problems:

1. When I copy/paste the query i receive this:

09:29> Starting: Tue, 01 Feb 2011 13:09:29 -0600
09:29> Executing Select Query...
09:29> 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>

2. I cannot get it to actually send an email to the distribuition group email address i created for my support team. I changed this line to the email address I want to send to:

'itsupport@orionmarinegroup.com' as NEWTICKETEMAIL, /*<<<change your email here */

But no emails actually go out. Am I supposed to put a string or other syntax in the "email column" field?

Sharing the code you created [in a code block] might elicit more responses.

OK, as I said before all I did was copy/paste the code from the link provided but below is the query in full form.

select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'itsupport@orionmarinegroup.com' as NEWTICKETEMAIL, /*<<<change your email here */
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

This is the code for my subjest line in the "send email for each result row"
[TICK:$ticknum] NEW TICKET: $title

I dont have anything in the email column field

And this is the code for the email vody field
$submitter_fullname ($submitter_email) has opened ticket # $ticknum.

You can review this ticket online at
helpdesk.omgsupport.net/adminui/ticket?ID=$ticknum

Any help is appreciated

Difference I am seeing in your script and what I have that is working is that I removed this line /*<<<change your email here */
Also in the Email Column I have NEWTICKETEMAIL .

Anybody ever find a fix for this, I have the same SQL issue.

Ok I figured this out, perhaps it is due to a different software version on the KBOX?

At any rate here is what my code looks like, note that I had to put ' ' around the <CHANGE_ID> portion:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'groupname@mydomain.org' as NEWTICKETEMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID = '<CHANGE_ID>'
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

Hope this helps anyone else who may have been scratching their head. Perhaps the KACE team could add this to the FAQ link referred to above that this slight addition needs to be made.