KACE Product Support

Have you tried checking the box to email the query results to an email address and have them sent to you? That might show what the query is returning.

I have several custom ticket rules with emails setup in this post:

http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned

The Unassigned Tickets or Waiting On Approval examples should help you get what you want working.  As chucksteel said, make sure to check the "Send an email for each result row" option and to specify & alias an email address in the Select query (the alias is referenced in the Email Column field.

If you get stuck, let us know.

John

Since that blog is down due to technical issues, here's the custom ticket rules I was referring to.  Just one note (and I'm going to update this in the blog once I have a chance to test it), the Waiting on Approval ticket rule references HD_STATUS.NAME, which hasn't been changing like when this was originally setup.  So I'm planning on referencing the HD_TICKET.APPROVAL field instead, since this is what actually gets updated when the approver replies to the email using the included link (and prevents the rule from running over and over).  I've picked up a few MySQL tricks since posting this - I'm sure you can understand finding ways to improve old solutions when you've learned a few more things.  Anyways, hope that helps!

John

________________

Title:
Unassigned Tickets
************************************
I believe I borrowed this from an AppDeploy post, modified for my own use.
************************************

Order:
11

Notes:
Sends an email when a ticket has sat in queue longer than specified period (1 hour).
Create this ticket rule in Queue 1 (IT Helpdesk).

Frequency:
15 minutes

Select Query:
Select 'itdept@company.com' as SUPPORT, T.ID as ID, T.TITLE as Issue, S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)
AND T.OWNER_ID = 0
ORDER BY T.ID ASC

X Send an email for each result row

Subject:
ATTENTION - Unassigned Ticket in KBOX Helpdesk Queue

Email Column:
SUPPORT

Email Body:
Unassiged ticket in KBOX Helpdesk queue, please review.

Created:    $created
Ticket ID:    $id
Issue:        $issue
Submitter:    $submitter
________________

Title:
Waiting On Approval
************************************
I actually have two of these ticket rules in place, depending on whether a user needs remote access or not.  As I mentioned earlier, our CEO has mandated that anyone requiring remote access first have his explicit approval before said access is granted.  So in the actual tickets, there are two things that need set (1) Remote Access - Yes or No, and (2) Approver - CEO or IT Director.  When set correctly (i.e. Remote Access - yes, Approver - CEO ~ or vice versa), this ticket rule fires off on ticket save and an email is sent to the appropriate person, who just needs to click on the link in the email to update the approval field from None to Approved or Rejected.  Inbound KBOX email routing needs to be working for this to go through.  If you want the opposite rule (i.e. Remote Access - No), just change the valued of HD_TICKET.CUSTOM_FIELD_VALUE13 in the last line of the select query from "Yes" to "No".
************************************

Order:
12

Notes:
Sends an email to CEO when a ticket's Status is Waiting On Approval and the Remote Access (VPN, Citrix) is Yes.

Frequency:
on Ticket Save

X Send an email for each result row

Subject:
[TICK:$id] NEW TICKET: $status_name

Email Column:
APPROVER

Email Body:
A ticket in the HR Helpdesk queue needs your approval, please review.

Ticket ID:                    $id
Created:                      $created
Issue:                        $issue
Category:                     $category
Status:                       $status_name
Remote Access (VPN/Citrix):   $remote

User:                         $fullname
Date of Hire:                 $dateofhire
Job Title:                    $jobtitle
Department:                   $department
Location:                     $location
Manager:                      $manager
___________________________________________________________________

To APPROVE opening this ticket, please click here:
<mailto:hr@company.com?subject=[TICK:$id]&body=@approval%20=%20Approved>

To REJECT opening this ticket, please click here:
<mailto:hr@company.com?subject=[TICK:$id]&body=@approval%20=%20Rejected>
___________________________________________________________________

Thanks,

Company IT

Select Query:

select 'ceo@company.com' as APPROVER, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE6 as DATEOFHIRE, HD_TICKET.CUSTOM_FIELD_VALUE5 as MANAGER, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT, HD_TICKET.CUSTOM_FIELD_VALUE4 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE13 as REMOTE, HD_TICKET.TITLE as Issue, HD_CATEGORY.NAME as CATEGORY, HD_TICKET.*, 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,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        STATE,
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE)
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME                       
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        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.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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_STATUS.NAME = 'Waiting On Approval') AND HD_TICKET.CUSTOM_FIELD_VALUE13 = 'Yes') and HD_TICKET.HD_QUEUE_ID = 2 )
________________