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


I've created a new report since multiple people have asked for a report similar to the Patch Tuesday reports but with more history.

The report has a column per month to indicate if the patch for that month was installed or not. If patches for the latest month have been installed, the asset should be highlighted green.

If you have suggestions or comments, feel free to let me know. This is only an initial version that I've tested on my local test machines and should be considered a work in progress.


Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
Case
When tblAssets.AssetID = One.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch August],
Case
When tblAssets.AssetID = Three.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch September],
Case
When tblAssets.AssetID = Two.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch October],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
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'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
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,
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
When tblAssets.AssetID = Two.AssetID Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4512491', 'KB4512476',
'KB4512486', 'KB4512506', 'KB4512482', 'KB4512518', 'KB4512489',
'KB4512488', 'KB4512497', 'KB4093109', 'KB4512517', 'KB4512507',
'KB4512516', 'KB4512501', 'KB4511553', 'KB4512508')) As One On
tblAssets.AssetID = One.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4520009', 'KB4520002',
'KB4520003', 'KB4519976', 'KB4519985', 'KB4520007', 'KB4519990',
'KB4520005', 'KB4520011', 'KB4093109', 'KB4519998', 'KB4520010',
'KB4520004', 'KB4520008', 'KB4519338', 'KB4517389')) As Two On
tblAssets.AssetID = Two.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4516051', 'KB4516026',
'KB4516033', 'KB4516065', 'KB4516062', 'KB4516055', 'KB4516064',
'KB4516067', 'KB4516070', 'KB4516044', 'KB4516068', 'KB4516066',
'KB4516058', 'KB4512578', 'KB4515384')) As Three On tblAssets.AssetID =
Three.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.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 tsysOS.OSname <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
tsysOS.OSname Not Like '%2003%' And (Not tsysOS.OSname Like 'Win 7%'
Or Not tblAssets.SP = 0) And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
tblAssets.AssetName
32 Comments
Heijx
Engaged Sweeper
Either I am doing something wrong or not, but trying this report brings my Lansweeper server down.
I am running SQL server Express 2015 on Windows 2012r2 and so far no problems with custom reports.
When I try to save this report, it keeps churning and finally chokes my machine by filling the tempdb completely up.
(My database is 4,5 GB, free space on disk is 36 GB before I run the report, but it is all gone after an hour or so).
Probably I need to set a limit somewhere for tempdb, but the point is, it has never done this before.
alanalan
Engaged Sweeper
Victor Heijmerikx wrote:
Either I am doing something wrong or not, but trying this report brings my Lansweeper server down.
I am running SQL server Express 2015 on Windows 2012r2 and so far no problems with custom reports.
When I try to save this report, it keeps churning and finally chokes my machine by filling the tempdb completely up.
(My database is 4,5 GB, free space on disk is 36 GB before I run the report, but it is all gone after an hour or so).
Probably I need to set a limit somewhere for tempdb, but the point is, it has never done this before.


I have the same issue. I run quite a few custom reports, but this one kills my lansweeper server every time. Win 2012 R2 / SQL Express 2012
Hendrik_VE
Champion Sweeper III
Same here. In SSMS it can take more then an hour to run this report, while I only have about 800 Windows assets.
Heijx
Engaged Sweeper
EDIT: I had the incomplete file, now it is complete.

Workaround:
As I remained very curious about my patch status, I took the July 2019 report from the Patch Tuesday Reports section which does work and amended it to reflect the august KB numbers.
For Windows 10/2016 it checks if the patch tuesday KB number OR the refresh KB number from a week to two weeks later is installed.
Oh and I removed Windows build 1511 which we haven't used in two years.


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],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
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.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'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
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 tblAssets.AssetID = SubQuery1.AssetID Then ''
Else Case
When tsysOS.OSname = 'Win 2008' Then 'KB4512476 or KB4512491'
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4512506 or KB4512486'
When tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8' Then 'KB4512518 or KB4512482'
When tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2' Then 'KB4512488 or KB4512489'
When tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016' Then 'KB4512517 or KB4512495'
When tsysOS.OScode Like '10.0.15063' Then 'KB4512507 or KB4512474'
When tsysOS.OScode Like '10.0.16299' Then 'KB4512516 or KB4512494'
When tsysOS.OScode Like '10.0.17134' Then 'KB4512501 or KB4512509'
When tsysOS.OScode Like '10.0.17763' Or
tsysOS.OSname = 'Win 2019' Then 'KB4511553 or KB4512534'
When tsysOS.OScode Like '10.0.18362' Then 'KB4512508 or KB4512941'
End
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 ('KB4512476', 'KB4512491', 'KB4512506',
'KB4512486', 'KB4512518', 'KB4512482', 'KB4512488', 'KB4512489', 'KB4512517',
'KB4512495', 'KB4512507', 'KB4512474', 'KB4512516', 'KB4512494', 'KB4512501',
'KB4512509', 'KB4511553', 'KB4512534', 'KB4512508', 'KB4512941')) 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
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
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
tsysOS.OSname Not Like '%2003%' And tsysAssetTypes.AssetTypename Like
'Windows%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
JacobH
Champion Sweeper III
I have a sql guru looking at this to see if he can make it more efficient - hopefully will reply soon with an update
Hendrik_VE
Champion Sweeper III
I just found out that when you replace the "Select (distinct) Top 1000000" with a more realistic value (eg. 5000), the report runs properly
To be sure you don't miss any information I think it's best to do a 'select count(*)' on all subqueries so you know which limit to configure.

Furthermore, I believe you can replace Select Top 1000000 tblQuickFixEngineering.AssetID with Select distinct Top 5000 tblQuickFixEngineering.AssetID. Is that right Esben?
Esben_D
Lansweeper Employee
Lansweeper Employee
Hendrik.VE wrote:
I just found out that when you replace the "Select (distinct) Top 1000000" with a more realistic value (eg. 5000), the report runs properly
To be sure you don't miss any information I think it's best to do a 'select count(*)' on all subqueries so you know which limit to configure.

Furthermore, I believe you can replace Select Top 1000000 tblQuickFixEngineering.AssetID with Select distinct Top 5000 tblQuickFixEngineering.AssetID. Is that right Esben?


It should still work, yes. I'm not sure how limiting the results would make it run faster (if you have below 5k assets anyway) since the results will always be limited to the number of assets you have. Having the limit set higher should not affect anything (unless I'm misunderstanding it).

Then again, if it works, it works.
I wonder what happens if you just remove the "Top 1000000", since that is not a requirement for an SQL query.
alanalan
Engaged Sweeper
Hendrik.VE wrote:
I just found out that when you replace the "Select (distinct) Top 1000000" with a more realistic value (eg. 5000), the report runs properly
To be sure you don't miss any information I think it's best to do a 'select count(*)' on all subqueries so you know which limit to configure.

Furthermore, I believe you can replace Select Top 1000000 tblQuickFixEngineering.AssetID with Select distinct Top 5000 tblQuickFixEngineering.AssetID. Is that right Esben?


Interesting. I haven't tried running it with this change, but those lines are in lansweeper reports by default when you first create them. I think every other report I run starts with "Select (distinct) Top 1000000" and they all run fine.
Esben_D
Lansweeper Employee
Lansweeper Employee
Updated for September
Esben_D
Lansweeper Employee
Lansweeper Employee
Thanks to JacobH for sending me some performance suggestions, I've updated the original report. Hopefully it helps.
alanalan
Engaged Sweeper
I just tested again and I'm still having the same issues.
Esben_D
Lansweeper Employee
Lansweeper Employee
Updated the report for October.
JacobH
Champion Sweeper III
Please add KB4519979 to October

thanks!

Hendrik_VE
Champion Sweeper III
JacobH wrote:
Please add KB4519979 to October

thanks!



I don't think that's a security update, Jacob.
See https://support.microsoft.com/en-us/help/4519979/windows-10-update-kb4519979
JacobH
Champion Sweeper III
https://www.catalog.update.microsoft.com/Search.aspx?q=KB4519979


2019-10 Cumulative Update for windows 10, windows 2016

New to Lansweeper?

Try Lansweeper For Free

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

Try Now