Microsoft March 2021 Patch Tuesday Audit

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State, 
Case
When tsysOS.OSname = 'Win 2008' and PatchIDMax >= 5000844 then 'Up to date' 
When tsysOS.OSname = 'Win 7' and PatchIDMax >= 5000841 then 'Up to date'
When tsysOS.OSname = 'Win 7 RC' and PatchIDMax >= 5000841 then 'Up to date'
When tsysOS.OSname = 'Win 2008 R2' and PatchIDMax >= 5000841 then 'Up to date' 
When tsysOS.OSname = 'Win 2012' and PatchIDMax >= 5000840 then 'Up to date'
When tsysOS.OSname = 'Win 8' and PatchIDMax >= 5000840 then 'Up to date' 
When tsysOS.OSname = 'Win 8.1' and PatchIDMax >= 5000848 then 'Up to date'
When tsysOS.OSname = 'Win 2012 R2' and PatchIDMax >= 5000848 then 'Up to date' 
When tsysOS.OScode Like '10.0.10240' and PatchIDMax >= 5000807 then 'Up to date' 
When tsysOS.OScode Like '10.0.14393' and PatchIDMax >= 5000803 then 'Up to date' 
When tsysOS.OSname = 'Win 2016' and PatchIDMax >= 5000803 then 'Up to date' 
When tsysOS.OScode Like '10.0.17134' and PatchIDMax >= 5000809 then 'Up to date' 
When tsysOS.OScode Like '10.0.17763' and PatchIDMax >= 5000822 then 'Up to date' 
When tsysOS.OSname = 'Win 2019' and PatchIDMax >= 5000822 then 'Up to date' 
When tsysOS.OScode Like '10.0.18363' and PatchIDMax >= 5000808 then 'Up to date' 
When tsysOS.OScode Like '10.0.19041' and PatchIDMax >= 5000802 then 'Up to date' 
When tsysOS.OScode Like '10.0.19042' and PatchIDMax >= 5000802 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.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' 
When tsysOS.OScode Like '10.0.18363%' Then '1909' 
When tsysOS.OScode Like '10.0.19041%' Then '2004' 
When tsysOS.OScode Like '10.0.19042%' Then '20H2' 
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' and PatchIDMax >= 5000844 then '' 
When tsysOS.OSname = 'Win 7' and PatchIDMax >= 5000841 then ''
When tsysOS.OSname = 'Win 7 RC' and PatchIDMax >= 5000841 then ''
When tsysOS.OSname = 'Win 2008 R2' and PatchIDMax >= 5000841 then '' 
When tsysOS.OSname = 'Win 2012' and PatchIDMax >= 5000840 then ''
When tsysOS.OSname = 'Win 8' and PatchIDMax >= 5000840 then '' 
When tsysOS.OSname = 'Win 8.1' and PatchIDMax >= 5000848 then ''
When tsysOS.OSname = 'Win 2012 R2' and PatchIDMax >= 5000848 then '' 
When tsysOS.OScode Like '10.0.10240' and PatchIDMax >= 5000807 then '' 
When tsysOS.OScode Like '10.0.10586' Then 'EOL, update to a higher Windows version' 
When tsysOS.OScode Like '10.0.14393' and PatchIDMax >= 5000803 then '' 
When tsysOS.OSname = 'Win 2016' and PatchIDMax >= 5000803 then '' 
When tsysOS.OScode Like '10.0.15063' Then 'EOL, update to a higher Windows version' 
When tsysOS.OScode Like '10.0.16299' Then 'EOL, update to a higher Windows version' 
When tsysOS.OScode Like '10.0.17134' and PatchIDMax >= 5000809 then '' 
When tsysOS.OScode Like '10.0.17763' and PatchIDMax >= 5000822 then '' 
When tsysOS.OSname = 'Win 2019' and PatchIDMax >= 5000822 then '' 
When tsysOS.OScode Like '10.0.18362' Then 'EOL, update to a higher Windows version' 
When tsysOS.OScode Like '10.0.18363' and PatchIDMax >= 5000808 then '' 
When tsysOS.OScode Like '10.0.19041' and PatchIDMax >= 5000802 then '' 
When tsysOS.OScode Like '10.0.19042' and PatchIDMax >= 5000802 then ''
Else Case 
When tsysOS.OSname = 'Win 2008' Then 'KB5000844 or KB5000856' 
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or 
tsysOS.OSname = 'Win 2008 R2' Then 'KB5000851 or KB5000841' 
When tsysOS.OSname = 'Win 2012' Or 
tsysOS.OSname = 'Win 8' Then 'KB5000840 or KB5000847' 
When tsysOS.OSname = 'Win 8.1' Or 
tsysOS.OSname = 'Win 2012 R2' Then 'KB5000848 or KB5000853' 
When tsysOS.OScode Like '10.0.10240' Then 'KB5000807' 
When tsysOS.OScode Like '10.0.14393' Or 
tsysOS.OSname = 'Win 2016' Then 'KB5000803' 
When tsysOS.OScode Like '10.0.17134' Then 'KB5000809' 
When tsysOS.OScode Like '10.0.17763' Or 
tsysOS.OSname = 'Win 2019' Then 'KB5000822' 
When tsysOS.OScode Like '10.0.18363' Then 'KB5000808' 
When tsysOS.OScode Like '10.0.19041' Then 'KB5000802' 
When tsysOS.OScode Like '10.0.19042' Then 'KB5000802' 
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 
When tsysOS.OSname = 'Win 2008' and PatchIDMax >= 5000844 then '#d4f4be' 
When tsysOS.OSname = 'Win 7' and PatchIDMax >= 5000841 then '#d4f4be'
When tsysOS.OSname = 'Win 7 RC' and PatchIDMax >= 5000841 then '#d4f4be'
When tsysOS.OSname = 'Win 2008 R2' and PatchIDMax >= 5000841 then '#d4f4be' 
When tsysOS.OSname = 'Win 2012' and PatchIDMax >= 5000840 then '#d4f4be'
When tsysOS.OSname = 'Win 8' and PatchIDMax >= 5000840 then '#d4f4be' 
When tsysOS.OSname = 'Win 8.1' and PatchIDMax >= 5000848 then '#d4f4be'
When tsysOS.OSname = 'Win 2012 R2' and PatchIDMax >= 5000848 then '#d4f4be' 
When tsysOS.OScode Like '10.0.10240' and PatchIDMax >= 5000807 then '#d4f4be' 
When tsysOS.OScode Like '10.0.14393' and PatchIDMax >= 5000803 then '#d4f4be' 
When tsysOS.OSname = 'Win 2016' and PatchIDMax >= 5000803 then '#d4f4be' 
When tsysOS.OScode Like '10.0.17134' and PatchIDMax >= 5000809 then '#d4f4be' 
When tsysOS.OScode Like '10.0.17763' and PatchIDMax >= 5000822 then '#d4f4be' 
When tsysOS.OSname = 'Win 2019' and PatchIDMax >= 5000822 then '#d4f4be' 
When tsysOS.OScode Like '10.0.18363' and PatchIDMax >= 5000808 then '#d4f4be' 
When tsysOS.OScode Like '10.0.19041' and PatchIDMax >= 5000802 then '#d4f4be' 
When tsysOS.OScode Like '10.0.19042' and PatchIDMax >= 5000802 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,
Max(Cast(right(HotFixID,7) as bigint)) as PatchIDMax
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
where right(HotFixID,7) Not LIKE '%[^0-9]%' And HotFixID not in ('KB5001078','KB5001028')
group by tblQuickFixEngineering.AssetID) As SubQuery1 On tblAssets.AssetID = SubQuery1.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