cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Susan_A
Lansweeper Alumni
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
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
The report below will give back the machines that do not have the hotfixes installed. We have updated it with a list of the different hotfixes needed to mitigate MS17-010. These hotfixes are scanned from the Win32_QuickFixEngineering WMI class. There is an interval of 7 days for scanning the Win32_QuickFixEngineering WMI class, this can be modified by going to Scanning\Scanned Item Interval and setting it to 0. You can then do a full rescan of your machines so that the quickfixengineering table is updated with any new updates. Once the rescans have been done you can then run this report. In the report it is also required that the assets be set to the 'Active' state. If

Recap:
  • Go to Scanning\Scanned Item Interval
  • Change the interval time for the 'QUICKFIX' item to 0
  • Rescan all your assets to update the quickfixengineering tables with the new updates
  • Run the report

The hotfix must be found in Win32_QuickFixEngineering for Lansweeper to be able to scan it. The following command lists all the Hotfixes that are found in the Win32_QuickFixEngineering table.

wmic path Win32_QuickFixEngineering

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', 'KB4015551', 'KB4019216',
'KB4015550', 'KB4019215', 'KB4013429', 'KB4019472', 'KB4015217', 'KB4015438', 'KB4016635', 'KB4012598')) And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

33 REPLIES 33
SystemsIT
Engaged Sweeper III
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
Engaged Sweeper III
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
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
Engaged Sweeper
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
Engaged Sweeper II
Great Thanks
Hout
Engaged Sweeper II
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
Lansweeper Alumni
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
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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