Creating a Connectwise Automate Monitor to find machines missing software.

Like most IT people, I want to automate everything. Having things automated is about efficiency. In this particular case, our software team wanted to upgrade our win32 app from .net 4.8 to .net 6 and therefore the runtime needed to be installed on all those client machines. Doesn't sound that difficult, just run a script to install it. In fact since we use Chocolatey (www.chocolatey.org) that entire powershell script ends up being one line:

choco upgrade dotnet-6.0-desktopruntime -y

The problem is how do we determine what machines need it... Sure, I could just say install it on all machines, but some might be offline when I try to run that command, and others might fail the command and need it run again, and maybe I don't want it installed on all machines. The best way to solve this, since we already use Connectwise Automate (connectwise.com) (formerly called Labtech) is to use the monitor feature.

The problem is that the monitor feature doesn't natively support 'finding' something that is missing. It is possible, however, as long as you can customize the SQL the monitor uses.

To do this I suggest starting with the search feature of Automate. In the search we need to find computers that are not in the collection of computers that have .net 6 installed, which is a convoluted way of saying that we want to find the machine lacking .net 6, but it's helpful to phrase it that way as that's how the logic ends up flowing in our search.

Just for a more detailed view of what the 'applications' collection is, heres what it looks like when I click on it:

Once you've got it finding machines missing the software you want to force install, fill in any other criteria such as avoiding Server OSes, or whatever and then in the search window, click on the 'ShowSQL' button and then paste your SQL into a text editor. I'd also recommend saving your search in case you want to customize it further later, but that's optional.

Create your new monitor, I usually start by setting it to find everything and do nothing. Switch over to the configuration tab and in the 'Additional Condition' add your SQL. In my case the SQL is:

1 AND  Computers.LastContact > DATE_ADD(NOW(),INTERVAL -5 MINUTE) and computers.computerid in (SELECT 
   computers.computerid as `Computer Id`
FROM Computers 

 WHERE (NOT (SELECT COUNT(*) FROM Software WHERE Software.ComputerId = Computers.ComputerId  AND Instr(Software.Name,'Microsoft Windows Desktop Runtime - 6') > 0)>0))

Now before you say 'That doesn't look like a properly formatted SQL statement!' recall that this is going into a monitor page where most of the 'SQL' is hidden by the GUI. The Additional Conditions field effectively is part of a larger 'where' clause of an even bigger SQL statement.

The full configuration page looks something like this:

Once you've tested to make sure that monitor returns the proper subset of machines you actually want to install/upgrade .net on, then create your Autofix action and set it to perform that action over on the Alerting tab. But that part is the easy part.