Automating the Automator (Part II)

At this point we have a script that does one thing – deletes a computer from LabTech.  But we still need to identify the computers that we want to remove, then call our delete script and aim it at the offender.  What do we want to remove?  In my case, it’s simple: anything that hasn’t reported in over 30 days will be wiped.  How can we identify those computers?

I'll be honest and say I cheated and leveraged something I knew was already in the system.  I’ve long noticed that most of the internal monitors in LabTech have some sort of time constraint in the ‘Additional Field’ section so I used SqlYOG and did a little experimenting with the bit of SQL the monitor provided.  After a little bit of trial-and-error, this is what I came up with:

SELECT computerid FROM computers WHERE lastcontact < DATE_SUB(CURDATE(), INTERVAL 30 DAY)

As I’m marginally proficient with SQL, I did a lot of testing on this simple string.  This includes inverting the comparator, modifying the days (30) and both while for every run, making absolutely sure the results returned matched my expectations.  Once I saw that happen no matter how I arranged the string, I was confident I had a working query.  Now to the next step; let’s build one more script.

New Script - If ‘True’ Then:

Line 1 Function: Script for Each SQL

• SQL: computerid as DeleteMe FROM computers WHERE lastcontact < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
• Script To Run: (point this to the first script we created)
• ODBC Connection: (leave empty)

Name it, uncheck ‘Computer Script’, save it.  Done.

Note the SQL line is slightly different from what we tested.  This is so our output table is named with a non-standard name.  I wanted to make it clear for this exercise exactly what is getting passed between these scripts and how and a custom name does just that.

We use the ‘Script for Each SQL’ function because we don’t want to make assumptions of how many computers will be detected.  With this logical loop, it doesn’t matter if none, one or one-hundred targets are identified.  In all cases, the next subsequent action will take place (deletion.)  This makes our script flexible and prone to working properly.

If you are curious as to how our scripts pass their parameters, take a look at the ‘Script For Each SQL’ function carefully.  Note the top where it reads: ‘Performs a SQL Query and runs  a script for each row returned setting all returned columns to internal variables like this %sqlCOLUMNNAME%’  Now look at the first script we made yesterday.  Note the query – call sp_DeleteComputer and run it on %SQLDeleteMe%.  We create the column of computers which haven’t reported in 30 days – assign the ‘DeleteMe’ column name to the column – and reference it the way the script notes instructed.  Make sense??

The last step is to schedule this script for execution.  Again, please don’t do this on a production server until you’ve seen this work on your test system!  Dashboard -> Management -> Scheduled Client Scripts.  Add the script we just built to the list for daily execution and you’ll never again see a computer in your LabTech system that hasn’t reported to your server within the last 30 days.

Safety First!  One last suggestion.  Edit your delete script to add one line in front of your delete line.  If SQLDeleteMe = 1 then Exit Script.  ComputerID #1 is most likely your LabTech Server and we probably don’t want to delete that entry.  No, I can’t think of a situation where your LabTech server wouldn’t report in for 30+ days and get deleted.  Then again, it’s free to add a simple line to the script that might save my butt….