Report to list systems with Manufacturer of Nutanix and also list NGT if present

[bcordy3]

I spun up a report that successfully lists all systems with a Manufacturer of Nutanix and installed software of Nutanix Guest Tools as well as the version of NGT. I am unable to find a way to list systems in this report that do not have NGT but are in Nutanix, can only list the ones that meet both search requirements. Basically need all systems in Nutanix listed and their NGT status, installed or not installed, and if installed version listed.

Current (messy) report query is:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
IsNull(tblSoftwareUni.softwareName, 'Nutanix Guest Tools not installed') As
softwareName,
tblSoftware.softwareVersion,
tsysOS.OSname,
tblAssets.IPAddress
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Left Join tblSoftwareUni On tblSoftware.SoftID = tblSoftwareUni.SoftID
Where tblAssetCustom.Manufacturer = 'Nutanix' And tblSoftwareUni.softwareName
Like '%Nutanix Guest Tools%'

[bcordy3]

Tried replacing the tsysOS.OSname line with the below but it threw an error (The multi-part identifier "tblLinuxSystem.OSRelease" could not be bound.) - found it in a report we have that lists Linux systems

Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease) As OS,

[bcordy3]

That works, thank you so much! Only thing it's missing is listing Linux systems, I'm not sure why listing OS's doesn't grab them all

[rom]

This should work:

Select 
  Distinct Top 1000000 tblAssets.AssetID, 
  tblAssets.AssetName, 
  tblAssetCustom.Manufacturer, 
  case when NutanixTools.AssetID is null then 'No' Else 'Yes' End as HasNutanixTools, 
  NutanixTools.softwareVersion, 
  tsysOS.OSname, 
  tblAssets.IPAddress 
From 
  tblAssets 
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID 
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode 
  Left Join (
    Select 
      Distinct tblSoftware.AssetID, 
      tblSoftware.softwareVersion 
    From 
      tblSoftware 
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.SoftID 
    Where 
      tblSoftwareUni.softwareName Like '%Nutanix Guest Tools%'
  ) As NutanixTools On tblAssets.AssetID = NutanixTools.AssetID 
Where 
  tblAssetCustom.Manufacturer = 'Nutanix'