Report - OS "Not Scanned or actual OS"

[Toberts_D]

Hi

I want to achieve this report below , unfortunately I can't find the sample
Please advise and share me exact query

The expectation, the OS should be the actual OS or Not Scanned the value

Sample :


AssetName , Domain , Enabled , Type , IP Address , Description , OS

-- --- --- --- ---- ---- Win 2003
-- --- --- --- ---- ---- Not Scanned
-- --- --- --- ---- ---- Not Scanned
-- --- --- --- ---- ---- Win 2012 R2

[Toberts_D]

Thanks for the prompt respond 🙂

[Esben_D · Accepted solution]

The built-in report "Assets: All columns from the Assets menu" actually comes close to this.

Then you remove/add the columns you want and create a small case to make the 'Not Scanned' text appear when no OS is scanned.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Domain,
  Case When
  Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion) is NULL then 'Not Scanned'
  else Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
  end as OS,
  tblAssets.IPAddress,
  tblADComputers.IsEnabled,
  tsysIPLocations.IPLocation,
  tblAssets.Description
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetTypes.AssetTypename IN ('Windows','Linux','Apple Mac')
Order By tblAssets.AssetName