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,
PatchIDMax as [Highest KB Patch found],
SubQuery2.Buildnumber,
Case
When tsysOS.OSname = 'Win 2008' then '21163'
When (tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2') then '25661'
When (tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8') then '23409'
When (tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2') then '20069'
When tsysOS.OScode Like '10.0.10240' then '19003'

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


When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like
'%LTSC%' then '2061'

When tsysOS.OScode Like '10.0.18363%' then '1679'
When tsysOS.OScode Like '10.0.19041%' then '1110'
When tsysOS.OScode Like '10.0.19042%' then '1110'
When tsysOS.OScode Like '10.0.19043%' then '1110'
Else 'EOL, update to a higher Windows version'
End As [PT Build number],
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'
When tsysOS.OScode Like '10.0.19043%' Then '21H1'
End As Version,
Case
When tsysOS.OSname = 'Win 2008' And SubQuery2.Buildnumber >= 21163 Then 'Up to date'
When (tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2') And SubQuery2.Buildnumber >= 25661 Then 'Up to date'
When (tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8') And SubQuery2.Buildnumber >= 23409 Then 'Up to date'
When (tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2') And SubQuery2.Buildnumber >= 20069 Then 'Up to date'
When tsysOS.OScode Like '10.0.10240' And SubQuery2.Buildnumber >= 19003 Then 'Up to date'
When tsysOS.OScode Like '10.0.10586' Then 'EOL, update to a higher Windows version'
When (tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 4530 Then 'Up to date'
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' Then 'EOL, update to a higher Windows version'
When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not
Like '%LTSC%' Then 'EOL, update to a higher Windows version'
When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like
'%LTSC%' and SubQuery2.Buildnumber >= 2061 Then 'Up to date'
When tsysOS.OScode Like '10.0.18362%' Then 'EOL, update to a higher Windows version'
When tsysOS.OScode Like '10.0.18363%' And SubQuery2.Buildnumber >= 1679 Then 'Up to date'
When tsysOS.OScode Like '10.0.19041%' And SubQuery2.Buildnumber >= 1110 Then 'Up to date'
When tsysOS.OScode Like '10.0.19042%' And SubQuery2.Buildnumber >= 1110 Then 'Up to date'
When tsysOS.OScode Like '10.0.19043%' And SubQuery2.Buildnumber >= 1110 Then 'Up to date'
Else 'Out of date'
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 >= 21163 Then '#d4f4be'
When (tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2') And SubQuery2.Buildnumber >= 25661 Then '#d4f4be'
When (tsysOS.OSname = 'Win 2012' Or
tsysOS.OSname = 'Win 8') And SubQuery2.Buildnumber >= 23409 Then '#d4f4be'
When (tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2') And SubQuery2.Buildnumber >= 20069 Then '#d4f4be'
When tsysOS.OScode Like '10.0.10240' And SubQuery2.Buildnumber >= 19003 Then '#d4f4be'
When tsysOS.OScode Like '10.0.10586' Then 'EOL, update to a higher Windows version'
When (tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 4530 Then '#d4f4be'
When tsysOS.OScode Like '10.0.15063' Then '#ffadad'
When tsysOS.OScode Like '10.0.16299' Then '#ffadad'
When tsysOS.OScode Like '10.0.17134' Then '#ffadad'
When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Not
Like '%LTSC%' Then '#ffadad'
When tsysOS.OScode Like '10.0.17763' And tblOperatingsystem.Caption Like
'%LTSC%' and SubQuery2.Buildnumber >= 2061 Then '#d4f4be'
When tsysOS.OScode Like '10.0.18362%' Then '#ffadad'
When tsysOS.OScode Like '10.0.18363%' And SubQuery2.Buildnumber >= 1679 Then '#d4f4be'
When tsysOS.OScode Like '10.0.19041%' And SubQuery2.Buildnumber >= 1110 Then '#d4f4be'
When tsysOS.OScode Like '10.0.19042%' And SubQuery2.Buildnumber >= 1110 Then '#d4f4be'
When tsysOS.OScode Like '10.0.19043%' And SubQuery2.Buildnumber >= 1110 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
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID,
Max(Cast(Right(tblQuickFixEngineeringUni.HotFixID, 7) As bigint)) As
PatchIDMax
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where Right(tblQuickFixEngineeringUni.HotFixID, 7) Not Like '%[^0-9]%'
Group By tblQuickFixEngineering.AssetID) As SubQuery1 On tblAssets.AssetID =
SubQuery1.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,
tblAssets.AssetName