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….

Automating the Automator

The Control Center doesn't allow multi-selections so removing multiple computers from inventory seems impossible, right?  Here, I'll show you how to do this with a script but I hope you take away more than just one simple script. For with LabTech you can automate the LabTech server too - not just your remotes. There's lots of reasons to do this; this is just one example.

To setup for this, you'll need a test system with SqlYOG installed and a few vm's reporting into it. Use SqlYOG to open the database and look inside. Check the stored procedures. Is there a sp_DeleteComputer procedure? Open it for editing and you'll see it's looking for two properties: the ID of the computer to delete and a reason for deletion. Makes sense - when I use the GUI to delete a machine from LabTech, it knows the computer ID from my selection and the first thing it does is prompt me for a reason to delete. OK, now for the next step.

Next on the list is for us to confirm that stored procedure 1.) works as expected and 2.) is perfectly understood in exactly how it works.  Yes, I understand this is a simple stored procedure but before you rush to scripting before first testing, consider this: how much of a hassle will it be if you accidently delete the wrong computer from your production server?  What if you accidently delete more than just one?  The short of it is, you always need to test your automations before coding them.  Really.  A little effort here will pay in the long run.  Especially when those automations are running against your LabTech server.  OK, 'nuff said on that...

...so lets do this!  (Now is a good time to do a snapshot of your test environment.)  Open SqlYOG and the LabTech Control Center on your test server.  Identify the ComputerID of a vm we can delete (mine is lucky #7) and move over to SqlYOG.  Enter in this string:

call sp_DeleteComputer('7', 'Deleted thru SqlYOG')

...and press F9.  This will execute the line and, presumably delete computerID #7 (I really hope you substituted '7' for your run.)  Go back to the Control Center and refresh.  Check the navigation tree.  Is the computer you expected to be deleted, in fact, removed from the tree?  Good!  Now that we know this works as we assumed it should and we know exactly how to work it, we are ready to solidify it in script.

[aside: If you don't already have one, I suggest creating a 'Tests and Examples' script directory.  Unless you are the only person working in your LabTech server envrionment, there's always a chance someone may run one of your scripts as you are building it in a production environment... unless that script is in a directory named 'Tests and Examples!'  (Or, at least if they don't, you'll have plausable deniability for the ensuing chaos ;)  ]

New Script - If 'True' Then:
Line 1 Function: SQL Execute

• SQL Query: call sp_DeleteComputer('%SQLDeleteMe%', 'Deleted by script')
• ODBC Connection: (leave empty)

Name it, Uncheck 'Computer Script', check 'Function Script' and save it.  Done.

My editor is telling me this is a perfect opportunity for a cliffhanger so let's table this for a day.  Tomorrow, I'll show you how to schedule this script for daily operation and aim it at computers that haven't reported in over x days.  Until then, Happy LabTechIng!

Move: Done

A tough week of moving pains has paid off and we’ve just completed moving our corporate offices to a new location.  Accomplishing this while gearing up for Automation Nation -and- maintaining service levels was a challenge, but certainly worth the effort as we are now settled into our new – and much more spacious home. Go LabTech, v 5.0! 

I have to say, it’s absolutely amazing to have seen the transition of LabTech from a two-man back office to taking two floors of a sizable building.  Never mind the explosion of our user-base from 20ish to over 2000...  While words cannot fully express my awe and gratitude for our partner base and your overwhelming support for our product, I’d still like to extend my deepest thanks to each and every one of you who use our software to make IT problems go away and environments more stable.  We couldn’t have done this without you!  Whatever version of LabTech the future holds, you can be assured that each and every one of us here remain committed to delivering you the best RMM software experience available.

Later this week, I’ll bring pictures so you can see the new digs.  Until then, thanks again everyone and  -Happy LabTech’Ing-!