This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Windows computers that do not have a specific update installed

Go to solution
Susan_A
Lansweeper Alumni

‎12-04-2014 12:07 PM

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
Go to solution
Nick_VDB
Champion Sweeper III

‎05-15-2017 09:52 AM

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
Go to solution
Stephen_Ellis
Engaged Sweeper

‎06-12-2018 09:53 AM

Hi All,

How do i swap from searching to match all missing patches to any?

at the moment i have;

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblOperatingsystem.Caption,
tblOperatingsystem.Version
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where
tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4103716', 'KB4103731',
'KB4074596', 'KB4093111', 'KB4074590', 'KB4093119', 'KB4103723',
'KB4074590')) And tblOperatingsystem.Version = '10.0.14393' And
tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
0 Kudos
Go to solution
Harryc
Engaged Sweeper

‎05-18-2017 06:40 AM

Susan.A wrote:
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



Thanks for your information. I have a question, so is this scan only trigger by active scan? Can lspush agent also collect these windows patch information if we don't have Windows active scanning?
0 Kudos
Go to solution
Bruce_B
Lansweeper Alumni
In response to Harryc

‎05-18-2017 10:52 AM

Harryc wrote:
Susan.A wrote:
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



Thanks for your information. I have a question, so is this scan only trigger by active scan? Can lspush agent also collect these windows patch information if we don't have Windows active scanning?


Windows updates are scanned by any form of Windows computer scanning, be it IP Range scanning, Active (Directory Domain) Scanning, Windows Computer Path scanning or LsPush scanning. Do note though that the item that covers these Windows updates, 'Quickfix' has a scanning interval of 7 days. This can be adjusted under Scanning\Scanned Item Interval.
0 Kudos
Go to solution
DeepKrip
Engaged Sweeper

‎05-17-2017 12:30 AM

How can I filter out Windows 10 and other OS that are not affected or where the patch does not apply?
In other words, how can I just focus on Win 7 machines?

I have added tsysOS.OSname to reflect OS in the report. I can then filter it in output file, but would like to do this in the query instead.

Also, I have narrow down the KB to what I need to report on, hence modified the query as below:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysOS.OSname,
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', 'KB4019216')) And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
0 Kudos
Go to solution
MrVal
Engaged Sweeper III

‎05-16-2017 03:51 PM

The query seems to be working well for me so far. But I did have to add

And tblAssetCustom.State = 1

To filter out the inactive machines.
0 Kudos
Go to solution
SystemsIT
Engaged Sweeper III

‎05-15-2017 08:28 PM

Just posted in as well:
https://www.lansweeper.com/forum/yaf_postsm50458_Assets-missing-Quickfix-data.aspx#post50458


Example server:
First seen: 08/02/2016 07:47:49
Last seen: 05/15/2017 12:23:35 (about 3 mins ago)


wmic path Win32_QuickFixEngineering >lansweeperwmi.txt

http://support.microsoft.com/?kbid=3210132 N**-DISCOVERY Update KB3210132 NT AUTHORITY\SYSTEM 1/6/2017
http://support.microsoft.com/?kbid=3210135 N**-DISCOVERY Update KB3210135 NT AUTHORITY\SYSTEM 1/23/2017
http://support.microsoft.com/?kbid=4014551 N**-DISCOVERY Update KB4014551 NT AUTHORITY\SYSTEM 4/29/2017
http://support.microsoft.com/?kbid=4014567 N**-DISCOVERY Update KB4014567 NT AUTHORITY\SYSTEM 4/29/2017
http://support.microsoft.com/?kbid=4015553 N**-DISCOVERY Update KB4015553 NT AUTHORITY\SYSTEM 4/29/2017



But, according to Windows:


So something is not being properly scanned or located.

Go to solution
SystemsIT
Engaged Sweeper III

‎05-15-2017 04:45 PM

Appreciated thank you.

I have sent a email into support though as I am getting false positives again. Systems that have the March and April and May Sec. release installed are reporting as vulnerable from the LanSweeper report.
0 Kudos
Go to solution
Nick_VDB
Champion Sweeper III

‎05-15-2017 11:54 AM

Thank you for the update. We have added KB4012598 to the list. We have also made a main post here.

0 Kudos
Go to solution
chrisarcher
Engaged Sweeper

‎05-15-2017 10:47 AM

Many thanks for this quick reply. It is appreciated
0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now