Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
As OS,
tblAssets.SP,
Case
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'Yes'
When tsysOS.OSname = 'Win 2012' Then 'Yes'
When tsysOS.OSname = 'Win 8.1' Or tsysOS.OSname = 'Win 2012 R2' Then 'Yes'
When tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016' Then 'Yes'
When tsysOS.OScode Like '10.0.16299' Then 'Yes'
When tsysOS.OScode Like '10.0.17134' Then 'Yes'
When tsysOS.OScode Like '10.0.17763' Then 'Yes'
When tsysOS.OSname = 'Win 2019' Then 'Yes'
When tsysOS.OScode Like '10.0.18362' Then 'Yes'
When tsysOS.OScode Like '10.0.18363' Then 'Yes'
When tsysOS.OScode Like '10.0.19041' Then 'Yes'
When tsysOS.OScode Like '10.0.19042' Then 'Yes'
When tsysOS.OScode Like '10.0.19043' Then 'Yes'
When tsysOS.OScode Like '10.0.20348%' Then 'Yes'
When tblMacOSInfo.SystemVersion Like '%10.13%' Then 'Yes'
When tblMacOSInfo.SystemVersion Like '%10.14%' Then 'Yes'
When tblMacOSInfo.SystemVersion Like '%10.15%' Then 'Yes'
When tblMacOSInfo.SystemVersion Like '%11%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Alpine%3.8%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Alpine%3.9%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Alpine%3.10%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Alpine%3.11%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Alpine%3.12%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Alpine%3.13%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%CentOS%7%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%CentOS%8%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Debian%9%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Debian%10%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Debian%11%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Fedora%29%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Fedora%30%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Fedora%31%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Fedora%32%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Fedora%33%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Fedora%34%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Red Hat%8%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Red Hat%7%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%SLES%15%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%SLES%12%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Ubuntu%21.04%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Ubuntu%20.10%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Ubuntu%20.04%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Ubuntu%19.10%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Ubuntu%19.04%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Ubuntu%18.04%' Then 'Yes'
When tblLinuxSystem.OSRelease Like '%Ubuntu%16.04%' Then 'Yes'
When tblLinuxSystem.OSRelease Is Null Then 'Rescan Asset'
When tblMacOSInfo.SystemVersion Is Null Then 'Rescan Asset'
When tsysOS.OScode Is Null Then 'Rescan Asset'
Else 'No'
End As [.Net Core 3.1/.NET Framework 4.8 supported],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then '#d4f4be'
When tsysOS.OSname = 'Win 2012' Then '#d4f4be'
When tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2' Then '#d4f4be'
When tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016' Then '#d4f4be'
When tsysOS.OScode Like '10.0.16299' Then '#d4f4be'
When tsysOS.OScode Like '10.0.17134' Then '#d4f4be'
When tsysOS.OScode Like '10.0.17763' Then '#d4f4be'
When tsysOS.OSname = 'Win 2019' Then '#d4f4be'
When tsysOS.OScode Like '10.0.18362' Then '#d4f4be'
When tsysOS.OScode Like '10.0.18363' Then '#d4f4be'
When tsysOS.OScode Like '10.0.19041' Then '#d4f4be'
When tsysOS.OScode Like '10.0.19042' Then '#d4f4be'
When tsysOS.OScode Like '10.0.19043' Then '#d4f4be'
When tsysOS.OScode Like '10.0.20348%' Then '#d4f4be'
When tblMacOSInfo.SystemVersion Like '%10.13%' Then '#d4f4be'
When tblMacOSInfo.SystemVersion Like '%10.14%' Then '#d4f4be'
When tblMacOSInfo.SystemVersion Like '%10.15%' Then '#d4f4be'
When tblMacOSInfo.SystemVersion Like '%11%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Alpine%3.8%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Alpine%3.9%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Alpine%3.10%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Alpine%3.11%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Alpine%3.12%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Alpine%3.13%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%CentOS%7%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%CentOS%8%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Debian%9%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Debian%10%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Debian%11%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Fedora%29%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Fedora%30%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Fedora%31%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Fedora%32%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Fedora%33%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Fedora%34%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Red Hat%8%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Red Hat%7%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%SLES%15%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%SLES%12%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Ubuntu%21.04%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Ubuntu%20.10%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Ubuntu%20.04%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Ubuntu%19.10%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Ubuntu%19.04%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Ubuntu%18.04%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Like '%Ubuntu%16.04%' Then '#d4f4be'
When tblLinuxSystem.OSRelease Is Null Then ''
When tblMacOSInfo.SystemVersion Is Null Then ''
When tsysOS.OScode Is Null Then ''
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
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 tblState.Statename = 'Active' And tsysAssetTypes.AssetTypename In
('Windows', 'Linux', 'Unix', 'Apple Mac')
Order By tblAssets.Domain,
tblAssets.AssetName