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
jomartinez
Engaged Sweeper
Great report, thank you for providing it. I have a few questions, first I have my assets scanned several times during the day but unless I manually re-scan an asset or from the report I re-scan all the assets it does not update correctly. Now I was trying to compare with the previous month report and now that one shows almost all of them as Not Patched, I would think that it should still shows on the last report if that was patched or not. And last a suggestion, just show the Active devices.

Thank you.
MT_Keith
Engaged Sweeper
jomartinez@partsbase.com wrote:
Great report, thank you for providing it. I have a few questions, first I have my assets scanned several times during the day but unless I manually re-scan an asset or from the report I re-scan all the assets it does not update correctly. Now I was trying to compare with the previous month report and now that one shows almost all of them as Not Patched, I would think that it should still shows on the last report if that was patched or not. And last a suggestion, just show the Active devices.

Thank you.


I Totally Agree with the active device only request please as i'd like to use this as an at a glance dashboard report for the numbers.

I'm also seeing doubled up entry returns for the UP TO DATE ENTRIES ONLY since i loaded the latest revision of the script - Is anyone else seeing this?
27 Nov 2018
Esben_D
Lansweeper Employee
Lansweeper Employee
jomartinez@partsbase.com wrote:
Great report, thank you for providing it. I have a few questions, first I have my assets scanned several times during the day but unless I manually re-scan an asset or from the report I re-scan all the assets it does not update correctly. Now I was trying to compare with the previous month report and now that one shows almost all of them as Not Patched, I would think that it should still shows on the last report if that was patched or not. And last a suggestion, just show the Active devices.

Thank you.


Just for clarification Windows updates are by default scanned every week. So if you want the report to be accurate, it is always best to do a manual rescan using the report (a manual rescan will do a full rescan and ignore default scan times).

You can more information of how Lansweeper scans Windows items here: https://www.lansweeper.com/knowledgebase/managing-how-often-specific-data-is-scanned/

As for active assets, I'll add it in the next report.

Lastly, in regards to previous month reports, I agree it would be nice to have these also display updated assets as up to date. However, this would require me to update every patch report every month which is just too much manual work unfortunately. Especially since most people will just use the latest one anyway. If this is something you need, it's you can always just modify it yourself 😉
Notonyourradar
Engaged Sweeper III
MT_Keith wrote:
jomartinez@partsbase.com wrote:
Great report, thank you for providing it. I have a few questions, first I have my assets scanned several times during the day but unless I manually re-scan an asset or from the report I re-scan all the assets it does not update correctly. Now I was trying to compare with the previous month report and now that one shows almost all of them as Not Patched, I would think that it should still shows on the last report if that was patched or not. And last a suggestion, just show the Active devices.

Thank you.


I Totally Agree with the active device only request please as i'd like to use this as an at a glance dashboard report for the numbers.

I'm also seeing doubled up entry returns for the UP TO DATE ENTRIES ONLY since i loaded the latest revision of the script - Is anyone else seeing this?
27 Nov 2018


Same
Esben_D
Lansweeper Employee
Lansweeper Employee
Notonyourradar wrote:
MT_Keith wrote:
jomartinez@partsbase.com wrote:
Great report, thank you for providing it. I have a few questions, first I have my assets scanned several times during the day but unless I manually re-scan an asset or from the report I re-scan all the assets it does not update correctly. Now I was trying to compare with the previous month report and now that one shows almost all of them as Not Patched, I would think that it should still shows on the last report if that was patched or not. And last a suggestion, just show the Active devices.

Thank you.


I Totally Agree with the active device only request please as i'd like to use this as an at a glance dashboard report for the numbers.

I'm also seeing doubled up entry returns for the UP TO DATE ENTRIES ONLY since i loaded the latest revision of the script - Is anyone else seeing this?
27 Nov 2018


Same


I checked but could not find any duplicates in my results. If you are getting duplicate results, you can try adding a distinct to the beginning of the report which would make it look like:
Select Distinct Top 1000000 Coalesce...


If that doesn't solve it,it's best to contact our support team. They can look through the results to find the issue.
Notonyourradar
Engaged Sweeper III
Charles.X wrote:
Notonyourradar wrote:
MT_Keith wrote:
jomartinez@partsbase.com wrote:
Great report, thank you for providing it. I have a few questions, first I have my assets scanned several times during the day but unless I manually re-scan an asset or from the report I re-scan all the assets it does not update correctly. Now I was trying to compare with the previous month report and now that one shows almost all of them as Not Patched, I would think that it should still shows on the last report if that was patched or not. And last a suggestion, just show the Active devices.

Thank you.


I Totally Agree with the active device only request please as i'd like to use this as an at a glance dashboard report for the numbers.

I'm also seeing doubled up entry returns for the UP TO DATE ENTRIES ONLY since i loaded the latest revision of the script - Is anyone else seeing this?
27 Nov 2018


Same


I checked but could not find any duplicates in my results. If you are getting duplicate results, you can try adding a distinct to the beginning of the report which would make it look like:
Select Distinct Top 1000000 Coalesce...


If that doesn't solve it,it's best to contact our support team. They can look through the results to find the issue.


I think I see what is happening - I think these machines are getting both the Security Only and Security Monthly Quality updates so they are getting an entry for each in the report. There wouldn't happen to be a way on the report to make it an entry if either one is installed would there?
Esben_D
Lansweeper Employee
Lansweeper Employee
Notonyourradar wrote:
I think I see what is happening - I think these machines are getting both the Security Only and Security Monthly Quality updates so they are getting an entry for each in the report. There wouldn't happen to be a way on the report to make it an entry if either one is installed would there?


You're right, I did was able to reproduce it now. The Distinct should remove the duplicates. Did you try that?

I've added the distinct to the OP since it can't do any harm.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now