Windows computers that do not have a specific update installed

[Susan_A]

The report below lists Windows computers that are missing a specific hotfix (Windows update). Replace YourHotfix with the hotfix ID you would like to report on, e.g. KB2079403.

The report will only list assets that meet all of the following criteria:

• The asset is a Windows computer.
  
• The computer's state is set to "active".
  
• The computer has been successfully scanned at least once.
  
• The computer does not have the specified hotfix/update installed.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID Like '%YourHotfix%') And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[SystemsIT]

To check if it is me I Ran:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineeringUni.HotFixID = 'KB4012216') And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

Yet this report is also showing me systems which DO have KB4012216 installed instead of only systems that DO NOT have it installed.

Am i doing something wrong?

[SystemsIT]

Great report, however the report for me is showing systems that do in fact have said updates installed:

I have RDC into some of the server it reported and confirmed the KB article IS installed, so not sure why it would be reporting it isn't installed.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineeringUni.HotFixID In ('KB4012216','KB4012215',
    'KB4012217','KB4012212','KB4012204','KB4012213')) And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[Lockstar]

SystemsIT wrote:
Great report, however the report for me is showing systems that do in fact have said updates installed:

I have RDC into some of the server it reported and confirmed the KB article IS installed, so not sure why it would be reporting it isn't installed.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineeringUni.HotFixID In ('KB4012216','KB4012215',
    'KB4012217','KB4012212','KB4012204','KB4012213')) And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

Looking at this report, its not taking into account the superseeded updates. For exampled KB4012213 looks to be superseeded by KB4019213 / KB4019215 . Still trying to confirm that. I found machines with 'failed' updates in their history for the KB4012213, but had later KB4019213 and showing as fully up to date.

[sankyou]

This worked like a champ - thank you! I made a slight modification to scan only Windows 7 machines and it was exactly what I needed.

[Hout]

Great Thanks

[Hout]

Very helpful,

But how can I search for installed KB update?

Microsoft enrolled an update witch I want to uninstall, but first I need to find the assets that have this update installed.

[Susan_A]

Hout wrote:
Very helpful,

But how can I search for installed KB update?

Microsoft enrolled an update witch I want to uninstall, but first I need to find the assets that have this update installed.

Change the "Not In" in our original query to "In" (without the quotes) and insert your hotfix ID into the highlighted part of the query.

[chads]

This is very helpful indeed but a lot of coding to go against looking for certain criteria. This is why we still use Config Mgr for patching and reporting. Although this is helpful Microsoft Patches are very hard to track down because of all the criteria involved.

LS should put on their wishlist for future dev. The mighty Microsoft Patcher checker robot 5000. 🙂

[novasam]

How about checking if the software OS or version is installed for each patch. MS is pretty tricky on some of its releases, so this report can cause a lot of false positives.

Basically in the same report


If OS = Windows 2003 R2 SP2 and .Net4.5 is installed but does not have KB2698292

Or

If OS = Windows 2008 and .Net4.5 is installed but does not have KB2698293


It gets even crazier with SQL updates, because I can't figure out where to look up the actual SQL Server version in Lansweeper.

[Susan_A]

novasam wrote:
Basically in the same report


If OS = Windows 2003 R2 SP2 and .Net4.5 is installed but does not have KB2698292

Or

If OS = Windows 2008 and .Net4.5 is installed but does not have KB2698293

You'd need something like this:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.SP,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
  (tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineeringUni.HotFixID Like '%KB2698292%') And
  tsysOS.OSname = 'Win 2003 R2' And tblAssets.SP = 2 And
  tblSoftwareUni.softwareName Like '%.net framework 4.5%' And
  tblAssetCustom.State = 1) Or
  (tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineeringUni.HotFixID Like '%KB2698293%') And
  tsysOS.OSname = 'win 2008' And tblSoftwareUni.softwareName Like
  '%.net framework 4.5%' And tblAssetCustom.State = 1)
Order By tblAssets.Domain,
  tblAssets.AssetName