cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Esben_D
Lansweeper Employee
Lansweeper Employee
Hi Everyone,

A new patch Tuesday has arrived, and with it a new report which checks if assets in your network are on the latest Windows monthly roll-up update. If you want more information about what is included in this update, feel free to visit the related blog post.

This report will check if your assets are on the latest released monthly roll-up KB update. It's color-coded to give you an easy and quick overview which assets are already on the latest Windows update (excluding anything older than Windows 7 SP1).

If you have any suggestions which might improve the report for future use, feel free to post your suggestion. You can find the report for last month here.

Instructions to add this report to your Lansweeper installation can be found here: https://www.lansweeper.com/forum/yaf_postst9882_How-to-run-a-report.aspx

Select Distinct Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
Case tblAssets.AssetID
When SubQuery1.AssetID Then 'Up to date'
Else 'Out of date'
End As [Patch status],
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
Case
When tsysOS.OScode Like '10.0.10240%' Then '1507'
When tsysOS.OScode Like '10.0.10586%' Then '1511'
When tsysOS.OScode Like '10.0.14393%' Then '1607'
When tsysOS.OScode Like '10.0.15063%' Then '1703'
When tsysOS.OScode Like '10.0.16299%' Then '1709'
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
End As Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
Case
When tsysOS.OSname = 'Win 2008' Then 'KB4467706 or KB4467700'
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4467107 or KB4467106'
When tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8' Then 'KB4467701 or KB4467678'
When tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2' Then 'KB4467697 or KB4467703'
When tsysOS.OScode Like '10.0.10240' Then 'KB4467680'
When tsysOS.OScode Like '10.0.10586' Then 'KB4093109'
When tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016' Then 'KB4467691'
When tsysOS.OScode Like '10.0.15063' Then 'KB4467696'
When tsysOS.OScode Like '10.0.16299' Then 'KB4467686'
When tsysOS.OScode Like '10.0.17134' Then 'KB4467702'
When tsysOS.OScode Like '10.0.17763' Then 'KB4467708'
End As [Install one of these updates],
Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) > 3 Then
'Windows update information may not be up to date. We recommend rescanning this machine.'
Else ''
End As Comment,
Case tblAssets.AssetID
When SubQuery1.AssetID Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4467706', 'KB4467107',
'KB4467701', 'KB4467697', 'KB4467680', 'KB4093109', 'KB4467691',
'KB4467696', 'KB4467686', 'KB4467702', 'KB4467708', 'KB4467700',
'KB4467703', 'KB4467678', 'KB4467106')) As SubQuery1 On
tblAssets.AssetID = SubQuery1.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblAssets On tblAssets.AssetID = TsysLastscan.AssetID
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Inner Join tblAssets On tblAssets.AssetID = tblErrors.AssetID
Group By tblAssets.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
From tblAssets Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tsysOS.OSname Like 'Win 7%' And tblAssets.SP = 0) And
tsysOS.OSname != 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
tblAssets.AssetName
22 Comments
doone128
Engaged Sweeper III
Great stuff - but would still love to see the following implemented:

"Add a column to show if it's server architecture or not. That way we could easily filter servers without the need for a separate report. ie, just show 'Server' or 'Workstation'.

Thanks for adding the below though! Much appreciated!

"1: Could you add into the report an exclusion for the Windows OS's that are no longer receiving updates?, ie 2003, XP etc. It would be nice if this were included as, unfortunately, some of us still have some of this legacy stuff around and it would be a pain to edit the report every month.

2. Could the colour coding also relate to a column that shows Patched/Not Patched or Up to date/Out of date or something similar? This would make it super easy to filter and find out how many devices left need patching."
Esben_D
Lansweeper Employee
Lansweeper Employee
doone128 wrote:
Great stuff - but would still love to see the following implemented:

"Add a column to show if it's server architecture or not. That way we could easily filter servers without the need for a separate report. ie, just show 'Server' or 'Workstation'.

Thanks for the suggestion. I'll make sure to add it to the report next month. The implementation would be similar to what is explained in this topic. Pretty much just a small case that takes a look at the domainrole of the asset.
jstrong71
Engaged Sweeper II
Just had a quick question on this (This is Awesome BTW) does this also include the patches released for Server 2008-2016 as well? Or is it just for Desktop OS?
Esben_D
Lansweeper Employee
Lansweeper Employee
jstrong71 wrote:
Just had a quick question on this (This is Awesome BTW) does this also include the patches released for Server 2008-2016 as well? Or is it just for Desktop OS?

You can also find this in the SQL query itself, but it does include both server and desktop OS. The OS included are: Win 2008, Win 7, Win 7 RC, Win 2008 R2, Win 2012, Win 8, Win 8.1, Win 2012 R2, Win 2016 and all Win 10 versions.

Pretty much every Windows OS still supported.
Tuberider
Engaged Sweeper
Hi,

version 1511 seems build 10.0.10586 instead of 10.0.10525
https://support.microsoft.com/nl-be/help/4000824/windows-10-update-history-version-1511

Regards,
Hans Peeters
Tuberider
Engaged Sweeper
Why are the security only updates not included for:
Win 2008: 'KB4467700'
Win 7 / Win 2008 R2: 'KB4467106'
Win 8 / Win 2012: 'KB4467678'
Win 8.1 / Win 2012 R2: 'KB4467703'

for Win 8.1 / Win 2012 R2: see https://support.microsoft.com/nl-be/help/4009470/windows-8-1-windows-server-2012-r2-update-history
IT4
Engaged Sweeper II
Hi,
Please change the results in the column "Patch status" to "Up to date" / "Out of date".
With the current values you cannot filter for "Patched" as "Not patched" also includes the string "Patched" and therefore it returns everything.
Please call them so that you can filter by both values. (Easy to change in the query but I do not want to change it every time there is a new query)
Thanks!
Great report though...
doone128
Engaged Sweeper III
IT@BE wrote:
Hi,
Please change the results in the column "Patch status" to "Up to date" / "Out of date".
With the current values you cannot filter for "Patched" as "Not patched" also includes the string "Patched" and therefore it returns everything.
Please call them so that you can filter by both values. (Easy to change in the query but I do not want to change it every time there is a new query)
Thanks!
Great report though...


^^^^^^^^^^^^^^^^^^
What this guy said!
Esben_D
Lansweeper Employee
Lansweeper Employee
Tuberider wrote:
Hi,

version 1511 seems build 10.0.10586 instead of 10.0.10525
https://support.microsoft.com/nl-be/help/4000824/windows-10-update-history-version-1511

Regards,
Hans Peeters


Changed. Thank you.

Tuberider wrote:
Why are the security only updates not included for:
Win 2008: 'KB4467700'
Win 7 / Win 2008 R2: 'KB4467106'
Win 8 / Win 2012: 'KB4467678'
Win 8.1 / Win 2012 R2: 'KB4467703'

for Win 8.1 / Win 2012 R2: see https://support.microsoft.com/nl-be/help/4009470/windows-8-1-windows-server-2012-r2-update-history


I've added them to the report.

IT@BE wrote:
Hi,
Please change the results in the column "Patch status" to "Up to date" / "Out of date".
With the current values you cannot filter for "Patched" as "Not patched" also includes the string "Patched" and therefore it returns everything.
Please call them so that you can filter by both values. (Easy to change in the query but I do not want to change it every time there is a new query)
Thanks!
Great report though...


Thank for the feedback, I've quickly changed it in the report above.
IT4
Engaged Sweeper II
Hi Charles,
thanks for the response.
While you are at it please move the column patch status further up front as this is the most important column on the screen so that you do not have to scroll sideways.
Colors are nice for looks but to be actionable the filtering is key.
Thanks again.
Esben_D
Lansweeper Employee
Lansweeper Employee
IT@BE wrote:
Hi Charles,
thanks for the response.
While you are at it please move the column patch status further up front as this is the most important column on the screen so that you do not have to scroll sideways.
Colors are nice for looks but to be actionable the filtering is key.
Thanks again.


I've moved it so it's after asset state now.
Tuberider
Engaged Sweeper
Charles.X wrote:
Tuberider wrote:
Hi,

version 1511 seems build 10.0.10586 instead of 10.0.10525
https://support.microsoft.com/nl-be/help/4000824/windows-10-update-history-version-1511

Regards,
Hans Peeters


Changed. Thank you.

Tuberider wrote:
Why are the security only updates not included for:
Win 2008: 'KB4467700'
Win 7 / Win 2008 R2: 'KB4467106'
Win 8 / Win 2012: 'KB4467678'
Win 8.1 / Win 2012 R2: 'KB4467703'

for Win 8.1 / Win 2012 R2: see https://support.microsoft.com/nl-be/help/4009470/windows-8-1-windows-server-2012-r2-update-history


I've added them to the report. Right now it will only show those assets as up to date if they have the security update installed.

IT@BE wrote:
Hi,
Please change the results in the column "Patch status" to "Up to date" / "Out of date".
With the current values you cannot filter for "Patched" as "Not patched" also includes the string "Patched" and therefore it returns everything.
Please call them so that you can filter by both values. (Easy to change in the query but I do not want to change it every time there is a new query)
Thanks!
Great report though...


Thank for the feedback, I've quickly changed it in the report above.


Hi Charles,

Thank you for the update.
Still 2 remarks:
1. There is still an entry with 10.0.10525 in your SQL command.
10.0.10525 was used 2 times in original SQL posted beginning this week.

2. Why did you remove the Monthly roll Ups for Win 2008, Win 7, Win 8 and Win 8.1?
Monthly Rollups:
'KB4467706'
'KB4467107'
'KB4467701'
'KB4467697'

Security only:
'KB4467700'
'KB4467106'
'KB4467678'
'KB4467703'

we use this where clause for November:
Where tblQuickFixEngineeringUni.HotFixID In ('KB4467706', 'KB4467107',
'KB4467701', 'KB4467697', 'KB4467680', 'KB4093109', 'KB4467691',
'KB4467696', 'KB4467686', 'KB4467702', 'KB4467708', 'KB4467700',
'KB4467106', 'KB4467678', 'KB4467703')

Regards
Esben_D
Lansweeper Employee
Lansweeper Employee
Tuberider wrote:


Hi Charles,

Thank you for the update.
Still 2 remarks:
1. There is still an entry with 10.0.10525 in your SQL command.
10.0.10525 was used 2 times in original SQL posted beginning this week.

2. Why did you remove the Monthly roll Ups for Win 2008, Win 7, Win 8 and Win 8.1?
Monthly Rollups:
'KB4467706'
'KB4467107'
'KB4467701'
'KB4467697'

Security only:
'KB4467700'
'KB4467106'
'KB4467678'
'KB4467703'

we use this where clause for November:
Where tblQuickFixEngineeringUni.HotFixID In ('KB4467706', 'KB4467107',
'KB4467701', 'KB4467697', 'KB4467680', 'KB4093109', 'KB4467691',
'KB4467696', 'KB4467686', 'KB4467702', 'KB4467708', 'KB4467700',
'KB4467106', 'KB4467678', 'KB4467703')

Regards


Seems like I did miss than one mention of the 1511 build.

You're right about the roll-up updates, they should be included in the where clause, they are back in. I also changed the wording for the suggested updates to include an or. So it's suggested to install the roll-up or the security update so it is in line with the report results.
joe_user
Engaged Sweeper III
Thank you, Charles. This is something I've wanted to see for years!

I am also grateful for how you've worked in a timely manner with the great Lansweeper community. Great job with that! This combines the community feel that I'd appreciated with Spiceworks with the many things I love about Lansweeper.

For something as routine as MS monthly patches, it would be great if we could pull these into our web UI directly. We'd want a warning such as "this report is being imported from Lansweeper's report portal..." as a disclaimer, but it would give visibility to new reports right where I need them. This might be a great promotion tool for my next idea...

It would be nice to see automatically updated reports for the frequent fliers on Vulnerability Airlines: such as readers, browsers, viewers, etc. I realize this might take a lot of resources at Lansweeper, but I think there might be a market for licensing a robustly maintained report channel that feeds directly into the Lansweeper UI.

Thanks for listening~
Esben_D
Lansweeper Employee
Lansweeper Employee
joe_user wrote:
Thank you, Charles. This is something I've wanted to see for years!

I am also grateful for how you've worked in a timely manner with the great Lansweeper community. Great job with that! This combines the community feel that I'd appreciated with Spiceworks with the many things I love about Lansweeper.

For something as routine as MS monthly patches, it would be great if we could pull these into our web UI directly. We'd want a warning such as "this report is being imported from Lansweeper's report portal..." as a disclaimer, but it would give visibility to new reports right where I need them. This might be a great promotion tool for my next idea...

It would be nice to see automatically updated reports for the frequent fliers on Vulnerability Airlines: such as readers, browsers, viewers, etc. I realize this might take a lot of resources at Lansweeper, but I think there might be a market for licensing a robustly maintained report channel that feeds directly into the Lansweeper UI.

Thanks for listening~


Thanks! Due to the success of this report I'm certainly looking for other, similar things I could release on a bi-weekly or monthly basis (for example a monthly browser update report?). I'm sure there are things you guys can think of which would make your life easier.

As for the automation part, automatically pulling in reports is something which we've been looking at, however, this idea is part of a long term project.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now