KACE Product Support : K1000 Ticket created emails not sent to staff when comments are present

K1000 Ticket created emails not sent to staff when comments are present

We have an email notification setup where all category staff members are notified when a new ticket has been created in their queue. The rule works perfectly until a comment is added in the comment field – no email is sent when this happens. We are at a loss at what could cause this glitch.

5 Comments [ + ] Show comments

Please post your SQL for the rule and we can check it out. - chucksteel 11 years ago
The SQL query is:

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.COMMENT_LOC as CMT_L10N, -- $cmt_l10n

C.DESCRIPTION, -- $description

C.LOCALIZED_DESCRIPTION as DESC_L10N, -- $desc_110n

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') as HISTORY, -- $history

group_concat( concat('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

H.LOCALIZED_DESCRIPTION,'\n',H.COMMENT_LOC,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

order by H.ID desc separator '\n') as HIST_l10n, -- $hist_l10n

-- 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

OWNER.EMAIL 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> and <TICKET_JOIN>

/* initial change **/ left join HD_TICKET_CHANGE_FIELD F ON

F.HD_TICKET_CHANGE_ID = 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

F.ID is null /* initial entry for ticket */

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

group by HD_TICKET.ID

The email body is:

$submitter_fname has submitted a scenario.

The submission was:
From: $submitter_fname ($submitter_email)
Loan Type: $category
Loan Purpose: $impact
Priority: $priority

$comment - cgwillis 11 years ago
- I don't think I have ever seen <TICKET_JOIN> in a ticket rule before. Did the wizard add that for you? - chucksteel 11 years ago
I think that was Dell/Kace support's handy work. I am a bit lost on this as one of my tech's created this queue and he has since left the company. - cgwillis 11 years ago
This was a cut and paste from here: http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222 - cgwillis 11 years ago
- The join in the solution is different from the join you pasted. This should be the correct join to the HD_TICKET_CHANGE table:
  join HD_TICKET_CHANGE C on C.HD_TICKET_ID = HD_TICKET.ID
  and C.ID=<CHANGE_ID> - chucksteel 11 years ago
Made the change...no luck (changed it back). It looks like when we add comments the query isn't selecting a row to send. The logs supporting this are below:

Log without comments:

10/22/2013 11:52:36> Starting: 10/22/2013 11:52:36
10/22/2013 11:52:36> Executing Select Query...
10/22/2013 11:52:36> selected 1 rows
10/22/2013 11:52:36> Sending ticket notifications...
10/22/2013 11:52:36> sent mail to 1 of 1
10/22/2013 11:52:36> Ending: 10/22/2013 11:52:36

Log with comments:

10/22/2013 11:56:22> Starting: 10/22/2013 11:56:22
10/22/2013 11:56:22> Executing Select Query...
10/22/2013 11:56:22> selected 0 rows - cgwillis 11 years ago

Answers (1)

Posted by: cgwillis 11 years ago

This is the query that finally worked.

select

HD_TICKET.ID,

HD_TICKET.ID ticknum,

HD_TICKET.TITLE,

SUBMITTER.FULL_NAME submitter_fname,

SUBMITTER.EMAIL submitter_email,

CAT.NAME category,

IMPACT.NAME impact,

PRIORITY.NAME priority,

OWNER.EMAIL EMAILCOLUMN

from

HD_TICKET

join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID

and C.ID = <CHANGE_ID>

join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID

join USER OWNER on HD_TICKET.OWNER_ID = OWNER.ID

join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID

join HD_IMPACT IMPACT on HD_TICKET.HD_IMPACT_ID = IMPACT.ID

join HD_PRIORITY PRIORITY on HD_TICKET.HD_PRIORITY_ID = PRIORITY.ID where

C.DESCRIPTION like '%Ticket Created%'

Answer this question

Posted by:

View more:

KACE Product Support Questions

question