Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
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,
SubQuery2.Buildnumber,
Case
When tsysOS.OSname = 'Win 2008' then '21507'
When (tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2') then '25984'
When (tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8') then '23736'
When (tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2') then '20402'
When tsysOS.OScode Like '10.0.10240' then '19325'

When (tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016') then '5192'

When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like
'%LTSC%' then '3046'
When tsysOS.OSname = 'Win 2019' Then '3046'

When tsysOS.OScode Like '10.0.19041' then '1766'
When tsysOS.OScode Like '10.0.19042' then '1766'
When tsysOS.OScode Like '10.0.19043' then '1766'
When tsysOS.OScode Like '10.0.19044' Then '1766'
When tsysOS.OSname = 'Win 2022' then '768'
When tsysOS.OScode Like '10.0.22000' Then '739'
Else 'EOL, update to a higher Windows version'
End As [PT Build number],
tblassets.version,
Case
When tsysOS.OSname = 'Win 2008' And SubQuery2.Buildnumber >= 21507 Then 'Up to date'
When (tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2') And SubQuery2.Buildnumber >= 25984 Then 'Up to date'
When (tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8') And SubQuery2.Buildnumber >= 23736 Then 'Up to date'
When (tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2') And SubQuery2.Buildnumber >= 20402 Then 'Up to date'
When tsysOS.OScode Like '10.0.10240' And SubQuery2.Buildnumber >= 19325 Then 'Up to date'

When (tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 5192 Then 'Up to date'

When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like
'%LTSC%' and SubQuery2.Buildnumber >= 3046 Then 'Up to date'

When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 3046 Then 'Up to date'

When tsysOS.OScode Like '10.0.19041' And SubQuery2.Buildnumber >= 1766 Then 'Up to date'
When tsysOS.OScode Like '10.0.19042' And SubQuery2.Buildnumber >= 1766 Then 'Up to date'
When tsysOS.OScode Like '10.0.19043' And SubQuery2.Buildnumber >= 1766 Then 'Up to date'
When tsysOS.OScode Like '10.0.19044' And SubQuery2.Buildnumber >= 1766 Then 'Up to date'
When tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 768 Then 'Up to date'
When tsysOS.OScode Like '10.0.22000' And SubQuery2.Buildnumber >= 739 Then 'Up to date'
Else 'Out of date/EOL'
End As [Patch status],
tblAssets.Lastseen,
tblAssets.Lasttried,
QuickFixLastScanned.QuickFixLastScanned,
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 tsysOS.OSname = 'Win 2008' And SubQuery2.Buildnumber >= 21507 Then '#d4f4be'
When (tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2') And SubQuery2.Buildnumber >= 25984 Then '#d4f4be'
When (tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8') And SubQuery2.Buildnumber >= 23736 Then '#d4f4be'
When (tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2') And SubQuery2.Buildnumber >= 20402 Then '#d4f4be'
When tsysOS.OScode Like '10.0.10240' And SubQuery2.Buildnumber >= 19325 Then '#d4f4be'

When (tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 5192 Then '#d4f4be'

When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like
'%LTSC%' and SubQuery2.Buildnumber >= 3046 Then 'Up to date'

When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 3046 Then '#d4f4be'

When tsysOS.OScode Like '10.0.19041' And SubQuery2.Buildnumber >= 1766 Then '#d4f4be'
When tsysOS.OScode Like '10.0.19042' And SubQuery2.Buildnumber >= 1766 Then '#d4f4be'
When tsysOS.OScode Like '10.0.19043' And SubQuery2.Buildnumber >= 1766 Then '#d4f4be'
When tsysOS.OScode Like '10.0.19044' And SubQuery2.Buildnumber >= 1766 Then '#d4f4be'
When tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 768 Then '#d4f4be'
When tsysOS.OScode Like '10.0.22000' And SubQuery2.Buildnumber >= 739 Then '#d4f4be'

Else '#ffadad'
End As backgroundcolor
From tblAssets
left Join (select tblassets.AssetID, convert(bigint,tblassets.BuildNumber) as Buildnumber from tblAssets) As SubQuery2 On tblAssets.AssetID =
SubQuery2.AssetID
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 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