Using Ticket Rules to automate non-ticket emails

I hadn't seen anything like this around before, so I thought I'd share.

My environment consists almost entirely of laptops, and most of those are out of the office, so we have to rely on users connecting their computers to the Internet in order for them to talk back to our K1.  This doesn't always happen, so I had created reports which would list computers that had not checked in for 30 days or were missing a certain amount of available patches, and scheduled those to run once a week.  Then, I'd take the information from those reports, and email the users who haven't checked in, telling them to connect their computer to the Internet.
This was a cumbersome process, even with the report collecting the username and email address of the person using the computer, so I started thinking of a way to automate this process, like how we do with... some... ticket... rules.

As I learned in KACE Boot Kamp, a Ticket Rule is really just an open query and update, and isn't limited to the Service Desk module.

Note that if you are in any way uncomfortable with this process, you probably shouldn't attempt it.  A bad Update statement could really do some damage to your database.  And you'll need to find someone else to help you with your statements, because I'm not an expert in mySQL.

In this case, we're not updating anything, so what's the worst that could happen?  Some stray emails, maybe a few spam tickets.

So I started out with a report, in this case, based off of http://www.itninja.com/question/mia-machine. I added a join to the User table to get the computer's user's full name and email address (which is something you'll definitely need).  I created a Name and a Description for my rule, then basically pasted the SQL of the report into the Select SQL field.

Here, I did two things:  
1. I used an IF statement to catch computers without a user, or users without an email, and send the email to me instead (ie, meeting room computers, &C.).
2. Since I don't have a dev environment (playing with fire), I added a line for testing, where it only send an email to me.
You can see these below.  Depending on if I'm testing or using it, I will comment out one line of the other.

  IF(USER.EMAIL IS NULL or USER = '', 'my.email@domain.com', USER.EMAIL) as user_email,
--  'my.email@domain.com' as user_email, -- (for testing)

I put my own email in the Email Results field, so I get a complete list of who this is being sent to.  "Append comment to ticket" is left unchecked, because we're not working on a ticket.

Next comes "Email each recipient in query results," which the main part of our automated email process.  Give it a Subject, and point it to the "Column containing email addresses," which you can see in my case above, is USER_EMAIL, then craft your message.  As it mentions in the blue help text on that page, "Variables will be replaced in the subject and body of the email. Strings like $title and $due_date (lowercase with a dollar sign on the front) will be replaced by the values in the columns names TITLE and DUE_DATE respectively. Any column returned by the select statement can be replaced in that way."  This is your opportunity to personalize the email which the user will receive.  For example, I have columns USER_FULL_NAME, COMPUTER_NAME, SERVICE_TAG, and LAST_INVENTORY, in addition to USER_EMAIL, so my first line reads: 
$user_full_name, your computer ($computer_name, service tag $service_tag) has not checked into the KACE server since $last_inventory.

What the email the user gets says:
John Doe, your computer (DoeJ1, service tag 1234567) has not checked into the KACE server since 2015-12-31 13:33:51.  

After that, you have the opportunity to Run Update Query, which is where you'd place an Update statement, if you were daring enough.  We're just talking about sending out email right now, but I suppose I could have an Update that appended some text to the Notes section of the computer's Inventory that said "Sent update request email at 2016-02-09 14:38:41."

Leave Recalculate Due Dates unchecked, since again, we're not working on a ticket, then choose a schedule for this to run.  In this case, Weekly was a good choice.  Then hit Save, and relax knowing you won't have to send out those emails anymore.

Now, with that in place, you will want to have other Ticket Rules in place to handle bouncebacks and the like, in case someone's email is wrong in KACE, or their account has been deleted.  Use these KACE KB articles to help you craft them:  https://support.software.dell.com/k1000-systems-management-appliance/kb/116088 and https://support.software.dell.com/k1000-systems-management-appliance/kb/116089.  With these in place, I still get tickets created from bouncebacks, but before I set this up, those tickets would be updated 700 times a minute with undeliverable messages, bringing the K1 to a crawl.

Hope this helps save someone some time!


  • Here's another tip: When doing this, set the Priority to something other than the default, which is 100. I initially had it at 100, but sometimes got duplicate emails, which I assume to be happening because the process is being interrupted and restarted. When I changed it to 9, that issue went away. - ondrar 8 years ago
This post is locked
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ