/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Blog Posts tagged with K1000 Ticket Rules

Ask a question

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!
View comments (1)

KACE Custom Ticket Rule to query the most common items and add to email body

I saved this [Template] in my custom ticket rules and I duplicate it when I'm going to create a new rule.  Then I just add the criteria at the end and voila, you can create a very informative email.


select HD_TICKET.*, TC.COMMENT AS TCOMM, TC.TIMESTAMP AS TCOMMTIME,

                        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_CATEGORY.NAME as CATEGORY,

                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,

                        U1.EMAIL AS OWNER_EMAIL,

                        U1.FULL_NAME AS OWNER_FNAME,

                        U2.EMAIL AS SUBMITTER_EMAIL,

                        U2.FULL_NAME AS SUBMITTER_FNAME,

                        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_TICKET_CHANGE TC ON TC.HD_TICKET_ID = HD_TICKET.ID

                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_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.HD_QUEUE_ID = 4

"optional" Order by tcommtime ASC limit 1

 

In this example "HD_QUEUE_ID = 4" is my main queue, but you can change the value to accommodate

Also the optional "Order by tcommtime ASC limit 1” gives you only the 1st comment on that ticket

                                " Order by tcommtime DESC limit 1" gives you only the last comment on that ticket

 

Here is what it will query and feed into the email body for this ticket rule:

 

Ticket Number:                 $id

Prob Desc:                          $title

Summary:                           $summary

Custom Field 1:                 $custom_field_value0

Status:                                 $status_name

Category:                            $category

Submitter:                          $submitter_fname

Submitter Email:               $submitter_email

Owner:                                 $owner_fname

Owner Email:                     $owner_email

Queue:                                 $queue_name

Comment:                           $tcomm

Be the first to comment

Stop Kace Ticket Email Loops

Here's a way to stop tickets from looping for instance when an internal process creates an email into the service desk, but that process does not actually have a mailbox to receive the reply emails. Needs to act on ticket save. A little tricky using the internal variables and system added ticket where information. Note, could only get this to work using the Email results, not the email results to recipient in column. Can't change the initial select columns as using the kace internal variables to pass the correct information for the correct ticket on save. 

Comment Threshold Lock
Flatten email recipients on tickets suspected of looping between email systems. 

SELECT
HD_TICKET.ID, CHANGECOUNT, 
CONCAT('https://YOUR.KACE.URL/adminui/ticket?ID=',ID) as LINK
FROM
(SELECT
COUNT(C.ID) as CHANGECOUNT, 
HD_TICKET.ID as TKID
FROM HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
GROUP BY HD_TICKET.ID
    HAVING COUNT(C.ID) > 75) CC
JOIN HD_TICKET HD_TICKET ON HD_TICKET.ID = TKID
WHERE CC.CHANGECOUNT > 75

Check off email results, enter recipients.

Append owners only comment to ticket:
This ticket is locked due to excessive comments. If these comments are not in error, please reach out to the system administrator. As a loop precaution, the client and any CC's have been reset.

update HD_TICKET, USER as T6
    set HD_TICKET.CC_LIST = 'reporter@YOUR.KACE.URL', 
HD_TICKET.SUBMITTER_ID = T6.ID
  where T6.USER_NAME = 'reporter@YOUR.KACE.URL' and 
        (HD_TICKET.ID in (<TICKET_IDS>))

Of course in general, manual intervention is best, but this should assist in some automated catching. 

We also use a rule that looks for known offending email addresses that flattens any tickets generated from a known do-not-reply, internal devices etc email address.
Additionally we have a loop alert rule that looks for excessive ticket changes over time and sends a flag if it crosses the threshold of activity typical for the organization.

Loop Alert
This code checks for looping tickets by notifying staff if a tickets updates exceeds a set threshold per given time.

SELECT 'COMMA SEPARATED,EMAIL,RECIPIENTS' AS TLOOP, 
SUSPECT as SUSPECTT
FROM
(SELECT MAX(HD_TICKET_ID) AS SUSPECT
FROM 
(SELECT  
COUNT(TIMESTAMP) AS COUNTT, HD_TICKET_ID 
FROM HD_TICKET_CHANGE WHERE TIMESTAMP> DATE_SUB(NOW(), INTERVAL 15 MINUTE )
)XYT2
WHERE COUNTT>60)XYT3 /*This will be different for every organization and will take some playing*/
WHERE SUSPECT LIKE '%%'
;

Email each recipient in query results
Loop Alert, column TLOOP
✏ Please check into into possible email looping ticket https://YOUR.KACE.URL/adminui/ticket?ID=$suspectt
If looping change requester asap to reporter@YOUR.KACE.URL  
View comments (1)

John Verbosky - Good-bye Fellow Ninjas!

Hi everyone!

Well it's been an exciting ride, but it is with an extremely heavy heart that I have to announce my departure from KACE due to a workforce reduction by the new owners.  Although my focus for the past several years had been on internal technical training materials, my team was aggressively developing materials that were intended for our customers (you) when the company moved out of Dell and my job was eliminated.  I'm hopeful that my first implementation web-based training course will reach you eventually, although I won't be the one to finish it at this point.

Since all of my fellow ninjas have been so supportive of me and my work over the years, I'm sincerely hoping that you might be able to help me in my current situation - that is, assisting me in my search for a new technical position, where I can continue to help others by doing what I love.  So, if any of my blogs, KACE webinars or Dell Software User Forum training sessions have helped you (and if you wouldn't mind), I would sincerely appreciate it if you would reach out to me on LinkedIn and help me with a recommendation:


Thank you again for all of your support over the years, and I'll be sure to let you know where and when I land.

Have a good one!  ^_^ /

John C. Verbosky
jverbosk@gmail.com
View comments (7)
Showing 41 - 44 of 44 results

Top Contributors

Talk About Hardware