KACE Product Support

Thank you much for the speedy response.

57:33> mysql error: [1146: Table 'ORG1.HD_QUEUE_ID' doesn't exist]

is what I get.

If you have a mutli-organization KBOX, I believe you'll have to reference the tables differently in the rule by prefixing all of them with ORG1. "ORG1.HD_QUEUE_ID" isn't referenced anywhere in the code I posted.

This was my select query:

SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, 'my.email@asd20.org' /* <<<<<<<<put your email here */
AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
JOIN HD_QUEUE_ID Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='ASD20 Help Desk' /* <<<<< set queue name here */
WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 20 AND NOW() < DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'

So how should be modified for a K1200 box? I understand the concept of prefixing tables with ORG1 but does that mean this:

SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, 'my.email@asd20.org' /* <<<<<<<<put your email here */
AS EMAIL FROM ORG1.HD_TICKET T
JOIN ORG1.HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN ORG1.USER U ON (T.SUBMITTER_ID = U.ID)
JOIN ORG1.HD_QUEUE_ID Q ON ORG1.T.HD_QUEUE_ID=Q.ID and Q.NAME ='ASD20 Help Desk' /* <<<<< set queue name here */
WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 20 AND NOW() < DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'

I haven't dealt with a 1200 database, but your modifications seem correct from what I've read. It's just a select query, you can't break anything if you try it out. [;)] If it returns results in the MySQL Browser then it's good to go!

I got it working by changing a table called in the third JOIN line as "HD_QUEUE_ID" to ""HD_QUEUE" so here is the final result:

SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, 'my.email@asd20.org'
AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='ASD20 Help Desk'
WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 20 AND NOW() < DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'

Ah, sorry about that Rich. I just copied/pasted the code Gerald posted; I hadn't tested it. The reference to the table as HD_QUEUE_ID should've been HD_QUEUE, and I didn't notice it when I looked over the code.

This is working for me as far as not sending me emails, but now it seems that it is ignoring the 15min interval. For example I can get notified that a ticket has been sitting for more than 15 min when it was only entered 3 min ago. It is no longer working as Andy stated it would work, but it was working before.

ORIGINAL: airwolf

No, it will only show tickets that were created more than 15 minutes ago. Since the rule runs every 15 minutes (the lowest you can set it), your results may not show a ticket until about 30 minutes. So, your window of results is 15-30 minutes and beyond.


Here is what I am using, remember I only have 1 queue.

SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, '_helpdesk@uslegalsupport.com' AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 19 AND NOW() < DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'

Grico, sorry about that - another typo in Gerald's code that I copied/pasted. I used his example, because it's backward compatible with older KBOX models. This should work for you, there was a less-than sign where a greater-than should be!

SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, '_helpdesk@uslegalsupport.com' AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 19 AND NOW() > DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'


This doesn't know about weekends so if a ticket is left unassigned on a weekend during work hours an email will get sent, right?

ORIGINAL: RichB

If I want the same email to only be sent if a ticket has been unassigned for an hour the interval changes from 15 minutes to Hourly and the SQL Select changes from "INTERVAL 15 MINUTES" to INTERVAL Hourly", right?

INTERVAL 15 MINUTE changes to INTERVAL 1 HOUR

ORIGINAL: RichB

This doesn't know about weekends so if a ticket is left unassigned on a weekend during work hours an email will get sent, right?


SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, 'my.email@asd20.org'
AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='ASD20 Help Desk'
WHERE DAYNAME(DATE(NOW())) NOT IN ('Saturday','Sunday') AND HOUR(NOW()) > 6 AND HOUR(NOW()) < 20 AND NOW() > DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'


It's working great, thanks!

Create a custom rule that runs every 15 minutes. Use the following as the Select Query (which will select all non-closed tickets created over 15 minutes ago with no owner), and only check the box marked "Send Query Results to Someone" and enter your email address.


SELECT * FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
WHERE TIME_TO_SEC(TIMEDIFF(NOW(),T.CREATED)) > 900 AND T.OWNER_ID = 0 AND S.STATE != 'closed'


If you have more than one queue, you'll have to add " AND T.HD_QUEUE_ID = 1" without the quotes to the end of the WHERE clause - replacing 1 with the proper queue ID number.

If I set it to run every 15 min, what if a new ticket is entered 14 min into the 15 min. Will I then get a email 1 min after the ticket is created?

No, it will only show tickets that were created more than 15 minutes ago. Since the rule runs every 15 minutes (the lowest you can set it), your results may not show a ticket until about 30 minutes. So, your window of results is 15-30 minutes and beyond.

This is what I got in a test email.
Unassigned Ticket over 15 min

Unassigned Ticket over 15 min
# Id Title Hd Priority Id Hd Impact Id Modified Created Owner Id Submitter Id Hd Status Id Hd Queue Id Hd Category Id Cc List Escalated Custom Field Value0 Custom Field Value1 Custom Field Value2 Custom Field Value3 Custom Field Value4 Custom Field Value5 Custom Field Value6 Custom Field Value7 Custom Field Value8 Custom Field Value9 Custom Field Value10 Custom Field Value11 Custom Field Value12 Custom Field Value13 Custom Field Value14 Due Date Time Opened Time Closed Time Stalled Machine Id Satisfaction Rating Satisfaction Comment Resolution Asset Id Parent Id Is Parent Approver Id Approve State Approval Approval Note Id Hd Queue Id Name Ordinal State
1 4 TEST DO NOT DELETE 1 6 2010-03-17 12:09:05 2010-03-17 12:09:05 0 182 4 1 2 0000-00-00 00:00:00 3896 No Texas 2010-03-17 12:09:05 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 0 0 0 0 4 1 NEW 0 opened


Is is possible to get something as simple as


"Enduser (unduser@mycompany.com) has an unassigned ticket # 8557.

Please click here to review it online at
http://helpdesk.mycompany.com/adminui/ticket?ID=8557"

This is the code that I used to give me the requested info.
$submitter_fullname ($submitter_email) has an Unassigned ticket # $ticknum for more than 15 min.

Please click here to review it online at
http://helpdesk.uslegalsupport.com/adminui/ticket?ID=$ticknum

Simply uncheck the "Send Query Results Checkbox to Someone" and choose "Send an email for each result row". This will send an email to you for each ticket that fits the criteria. Keep in mind, you'll also have to change the Select Query to this (you'll need to put in a valid email address where I've got your@emailaddress.com):


SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, 'your@emailaddress.com' AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
WHERE TIME_TO_SEC(TIMEDIFF(NOW(),T.CREATED)) > 900 AND T.OWNER_ID = 0 AND S.STATE != 'closed'


And make sure you have "EMAIL" without the quotes in the Email Column field when setting up your rule. Create your subject line and body however you like, and remember that you can use any column as a variable by putting a $ in front of it (i.e. $title, $submitter_email, $submitter_fullname). Only columns given by the select query can be used as variables, so I've created TICKNUM, TITLE, SUBMITTER_EMAIL, and SUBMITTER_FULLNAME.

Everything Andy says is correct. However, for those with older kboxes that have mysql 4.x the function TIMEDIFF will not work. You could substitute

TIME_TO_SEC(TIMEDIFF(NOW(),T.CREATED)) > 900
with
NOW() < DATE_ADD(T.CREATED, INTERVAL 15 MINUTE)

The way this is written is that even if you have more than one queue this rule will notify you about all tickets that meet this condition in all queues. To limit it to just the queue that the rule exists in you need to add this to the end of the query:

and T.HD_QUEUE_ID=X X is the queue number for this queue. If you don't know the queue number then you can use the queue name like this:

SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, 'your@emailaddress.com' /* <<<<<<<<put your email here */
AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
JOIN HD_QUEUE_ID Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='Kbox Helpdesk' /* <<<<< set queue name here */
WHERE NOW() < DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'


This script has been working flawlessly and helping us make sure that all tickets are assigned in a timely manor. However it is working to well, and if someone puts in a ticket after hours I am getting an email every 15 min till I get here in the morning. I thought I saw a script that would allow me to shut off email notification during a certain time frame. Can someone point me to the right place to look?

I am unaware of any such script, Grico. Unfortunately, you can't accomplish this by changing the schedule on the rule either, because it doesn't allow for a "run window" or anything like that.

You CAN solve your problem, however, by modifying the SQL to only return results if NOW() is within a certain range.

SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, 'your@emailaddress.com' /* <<<<<<<<put your email here */
AS EMAIL FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
JOIN HD_QUEUE_ID Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='Kbox Helpdesk' /* <<<<< set queue name here */
WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 20 AND NOW() < DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND T.OWNER_ID = 0 AND S.STATE != 'closed'


The addition of HOUR(NOW()) > 6 AND HOUR(NOW()) < 20 will only allow the rule to return results after 6am and before 8pm, so we've just created a window of time for the rule!

If I want the same email to only be sent if a ticket has been unassigned for an hour the interval changes from 15 minutes to Hourly and the SQL Select changes from "INTERVAL 15 MINUTES" to INTERVAL Hourly", right?