I have a customer asking me how he can get a list of all machines that are missing a particular patch. I know I can create a view on it but there are ten different versions of this patch. I just want to list all machines that have this particular version of this particular patch.
In order to get the list, we will need access to SQL because we are going to run a SQL command. If you are a SaaS customer or a non-admin account without SQL access, you will not be able to run the SQL commands.
First, we will need to specific patch version. Go to Patch Management > Approval by Patch (or Approval by Policy) and look up the exact patch that you want to find what machines apply to it.
Click on the patch and a new window will pop-up. On this new window, you will need to look at the URL. At the end of the URL, you should see 'patchDataID=XXX'. This is the number that you will need to identify this particular patch version.
Now that you have the number, you will need to go to SQL management studio and open up a new query window against the ksubscribers database. Copy and paste the following query into the window and replace the patchDataID with the one you want and then execute it.
select machname, groupname, displayName
where agentguid NOT in (select distinct(agentguid)
where patchDataID = XXX and patchState =1)
This will get you the exact list of machines that are missing a specific patch version.