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 >= 4601360 then 'Up to date' 
When tsysOS.OSname = 'Win 7' and PatchIDMax >= 4601347 then 'Up to date'
When tsysOS.OSname = 'Win 7 RC' and PatchIDMax >= 4601347 then 'Up to date'
When tsysOS.OSname = 'Win 2008 R2' and PatchIDMax >= 4601347 then 'Up to date' 
When tsysOS.OSname = 'Win 2012' and PatchIDMax >= 4601348 then 'Up to date'
When tsysOS.OSname = 'Win 8' and PatchIDMax >= 4601348 then 'Up to date' 
When tsysOS.OSname = 'Win 8.1' and PatchIDMax >= 4601357 then 'Up to date'
When tsysOS.OSname = 'Win 2012 R2' and PatchIDMax >= 4601357 then 'Up to date' 
When tsysOS.OScode Like '10.0.10240' and PatchIDMax >= 4601331 then 'Up to date' 
When tsysOS.OScode Like '10.0.14393' and PatchIDMax >= 4601318 then 'Up to date' 
When tsysOS.OSname = 'Win 2016' and PatchIDMax >= 4601318 then 'Up to date' 
When tsysOS.OScode Like '10.0.17134' and PatchIDMax >= 4601354 then 'Up to date' 
When tsysOS.OScode Like '10.0.17763' and PatchIDMax >= 4601345 then 'Up to date' 
When tsysOS.OSname = 'Win 2019' and PatchIDMax >= 4601345 then 'Up to date' 
When tsysOS.OScode Like '10.0.18363' and PatchIDMax >= 4601315 then 'Up to date' 
When tsysOS.OScode Like '10.0.19041' and PatchIDMax >= 4601319 then 'Up to date' 
When tsysOS.OScode Like '10.0.19042' and PatchIDMax >= 4601319 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 >= 4601360 then '' 
When tsysOS.OSname = 'Win 7' and PatchIDMax >= 4601347 then ''
When tsysOS.OSname = 'Win 7 RC' and PatchIDMax >= 4601347 then ''
When tsysOS.OSname = 'Win 2008 R2' and PatchIDMax >= 4601347 then '' 
When tsysOS.OSname = 'Win 2012' and PatchIDMax >= 4601348 then ''
When tsysOS.OSname = 'Win 8' and PatchIDMax >= 4601348 then '' 
When tsysOS.OSname = 'Win 8.1' and PatchIDMax >= 4601357 then ''
When tsysOS.OSname = 'Win 2012 R2' and PatchIDMax >= 4601357 then '' 
When tsysOS.OScode Like '10.0.10240' and PatchIDMax >= 4601331 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 >= 4601318 then '' 
When tsysOS.OSname = 'Win 2016' and PatchIDMax >= 4601318 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 >= 4601354 then '' 
When tsysOS.OScode Like '10.0.17763' and PatchIDMax >= 4601345 then '' 
When tsysOS.OSname = 'Win 2019' and PatchIDMax >= 4601345 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 >= 4601315 then '' 
When tsysOS.OScode Like '10.0.19041' and PatchIDMax >= 4601319 then '' 
When tsysOS.OScode Like '10.0.19042' and PatchIDMax >= 4601319 then ''
Else Case 
When tsysOS.OSname = 'Win 2008' Then 'KB4601360 or KB4601366' 
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or 
tsysOS.OSname = 'Win 2008 R2' Then 'KB4601347 or KB4601363' 
When tsysOS.OSname = 'Win 2012' Or 
tsysOS.OSname = 'Win 8' Then 'KB4601348 or KB4601357' 
When tsysOS.OSname = 'Win 8.1' Or 
tsysOS.OSname = 'Win 2012 R2' Then 'KB4601384 or KB4601349' 
When tsysOS.OScode Like '10.0.10240' Then 'KB4601331' 
When tsysOS.OScode Like '10.0.14393' Or 
tsysOS.OSname = 'Win 2016' Then 'KB4601318' 
When tsysOS.OScode Like '10.0.17134' Then 'KB4601354' 
When tsysOS.OScode Like '10.0.17763' Or 
tsysOS.OSname = 'Win 2019' Then 'KB4601345' 
When tsysOS.OScode Like '10.0.18363' Then 'KB4601315' 
When tsysOS.OScode Like '10.0.19041' Then 'KB4601319' 
When tsysOS.OScode Like '10.0.19042' Then 'KB4601319' 
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 >= 4601360 then '#d4f4be' 
When tsysOS.OSname = 'Win 7' and PatchIDMax >= 4601347 then '#d4f4be'
When tsysOS.OSname = 'Win 7 RC' and PatchIDMax >= 4601347 then '#d4f4be'
When tsysOS.OSname = 'Win 2008 R2' and PatchIDMax >= 4601347 then '#d4f4be' 
When tsysOS.OSname = 'Win 2012' and PatchIDMax >= 4601348 then '#d4f4be'
When tsysOS.OSname = 'Win 8' and PatchIDMax >= 4601348 then '#d4f4be' 
When tsysOS.OSname = 'Win 8.1' and PatchIDMax >= 4601357 then '#d4f4be'
When tsysOS.OSname = 'Win 2012 R2' and PatchIDMax >= 4601357 then '#d4f4be' 
When tsysOS.OScode Like '10.0.10240' and PatchIDMax >= 4601331 then '#d4f4be' 
When tsysOS.OScode Like '10.0.14393' and PatchIDMax >= 4601318 then '#d4f4be' 
When tsysOS.OSname = 'Win 2016' and PatchIDMax >= 4601318 then '#d4f4be' 
When tsysOS.OScode Like '10.0.17134' and PatchIDMax >= 4601354 then '#d4f4be' 
When tsysOS.OScode Like '10.0.17763' and PatchIDMax >= 4601345 then '#d4f4be' 
When tsysOS.OSname = 'Win 2019' and PatchIDMax >= 4601345 then '#d4f4be' 
When tsysOS.OScode Like '10.0.18363' and PatchIDMax >= 4601315 then '#d4f4be' 
When tsysOS.OScode Like '10.0.19041' and PatchIDMax >= 4601319 then '#d4f4be' 
When tsysOS.OScode Like '10.0.19042' and PatchIDMax >= 4601319 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]%'
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