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
saulob
Engaged Sweeper II
There's a CASE error on Line 19, it's missing the Case command

You need to change this:

#
Else 'Out of date'
End As [Patch February],
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

To this

#
Else 'Out of date'
End As [Patch February],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

🙂


Esben_D
Lansweeper Employee
Lansweeper Employee
Saulo Benigno wrote:
There's a CASE error on Line 19, it's missing the Case command

You need to change this:

#
Else 'Out of date'
End As [Patch February],
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

To this

#
Else 'Out of date'
End As [Patch February],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

🙂




Thanks!

Added the March updates to the report.
jmje
Engaged Sweeper III
Looking forward to the April update to this report.
Is there a way to make it run faster? It takes a couple minutes to run in our environment, where every other report is done in seconds.

Thanks!
Tenacious
Engaged Sweeper II
I'm curious about three things in this report.

1) AFAIK, updates are cumulative (other than delta updates) so, why would something be 'not up to date' if it is missing the January update but has the March Update. This would be pretty likely scenario for a computer deployed in late February. It seems to me as though January and February would still be up-to-date even if only march was installed. Maybe I misunderstand the intent.

2) I haven't dived in too deeply to your SQL but it seems like there are 5 sections with "Where tblQuickFixEngineeringUni.HotFixID" statements and a block of KB ids.
The first and second blocks are mostly February KBs.
The third and fourth blocks are mostly January KBs.
The fifth block is March KBs.
So, question 2 is, why do the January and February blocks repeat but not the March block?

3) Each of the five blocks also contains KB4093109, which appears to be the April update for Windows 10 1511. This seems odd to me. Is it intentional?

Thanks!
joe_user
Engaged Sweeper III
To your point, half of our computers that show "up to date" for April are reported "out of date" for March. I feel that the "date" wording is problematic as it seems that the criteria in the report only verify that a single month's updates are installed. A more apt description would seem to be "March 2019 OS updates installed" or "Some or all March 2019 OS updates NOT installed."

[EDIT to add "OS" because application updates (Sharepoint, Exchange, SQL) are NOT in the criteria, e.g. KB4487563 https://support.microsoft.com/en-us/help/4487563/description-of-the-security-update-for-microsoft-exchange-server]
Esben_D
Lansweeper Employee
Lansweeper Employee
jmje wrote:
Looking forward to the April update to this report.
Is there a way to make it run faster? It takes a couple minutes to run in our environment, where every other report is done in seconds.

Thanks!

The reason why it takes so long is because is because it just has to do a lot since it's 3 already complex reports rolled into 1. The quickest way would be to run it directly in SSMS.


Tenacious wrote:
I'm curious about three things in this report.

1) AFAIK, updates are cumulative (other than delta updates) so, why would something be 'not up to date' if it is missing the January update but has the March Update. This would be pretty likely scenario for a computer deployed in late February. It seems to me as though January and February would still be up-to-date even if only march was installed. Maybe I misunderstand the intent.

You're probably right and it was just me misunderstanding things. I'll have to remake it then.

Tenacious wrote:

2) I haven't dived in too deeply to your SQL but it seems like there are 5 sections with "Where tblQuickFixEngineeringUni.HotFixID" statements and a block of KB ids.
The first and second blocks are mostly February KBs.
The third and fourth blocks are mostly January KBs.
The fifth block is March KBs.
So, question 2 is, why do the January and February blocks repeat but not the March block?

Think this is related to the delta update mindset, where you have 3 subqueries, month 1, month 1+2 and month 1+2+3.
Tenacious wrote:

3) Each of the five blocks also contains KB4093109, which appears to be the April update for Windows 10 1511. This seems odd to me. Is it intentional?

Thanks!

Microsoft has not released new security updates for this specific build for some time. Which is why it has remained the same.

joe_user wrote:
To your point, half of our computers that show "up to date" for April are reported "out of date" for March. I feel that the "date" wording is problematic as it seems that the criteria in the report only verify that a single month's updates are installed. A more apt description would seem to be "March 2019 updates installed" or "Some or all March 2019 updates NOT installed."


In addition to the comments above about the updates being cumulative I'll add this. The new report will basically just show whether that month's updates have been installed or not.

I'll post again when I've update the report with all these changes.

Esben_D
Lansweeper Employee
Lansweeper Employee
Updated the report.

Feel free to continue with the feedback. This was more of a small quick fix.
Tenacious
Engaged Sweeper II
Tenacious wrote:
I'm curious about three things in this report...

Esben.D wrote:
You're probably right and it was just me misunderstanding things. I'll have to remake it then...



Tenacious wrote:
2) I haven't dived in too deeply to your SQL...

Esben.D wrote:
Think this is related to the delta update mindset...



Tenacious wrote:

3) Each of the five blocks also contains KB4093109...

Esben.D wrote:
Microsoft has not released new security updates for this specific build...



Thanks for the update. This report could shape up to be pretty valuable to me. Thanks!
Esben_D
Lansweeper Employee
Lansweeper Employee
Updated the report for May
Viper
Engaged Sweeper II
Am I missing something or did you stop updating the report?
I do find this valuable.

Thanks
Esben_D
Lansweeper Employee
Lansweeper Employee
I updated it so it now includes May, June and July

Seems like I forgot for June as I was so busy
Viper
Engaged Sweeper II
Esben.D wrote:
I updated it so it now includes May, June and July

Seems like I forgot for June as I was so busy


No worries. I was fairly busy last month too.
Thanks for the update..!
Esben_D
Lansweeper Employee
Lansweeper Employee
Updated for August
dshu
Engaged Sweeper III
Hey,

Line 58 appears to be incorrect. When the August patch is installed, clients remain highlighted in red and not green. I believe it should read :

'When tblAssets.AssetID = One.AssetID Then '#d4f4be'

instead of :

'When tblAssets.AssetID = Three.AssetID Then '#d4f4be'
Esben_D
Lansweeper Employee
Lansweeper Employee
dshu wrote:
Hey,

Line 58 appears to be incorrect. When the August patch is installed, clients remain highlighted in red and not green. I believe it should read :

'When tblAssets.AssetID = One.AssetID Then '#d4f4be'

instead of :

'When tblAssets.AssetID = Three.AssetID Then '#d4f4be'


You are correct! Seems I missed that. Fixed it.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now