Help with a query

[mcutting]

Hi. I have a report where I need to list all Windows Servers, plus pull the description field from AD. However, I also need to include all Linux servers. Using the query below works fine until I decide to add the AD information, meaning the Linux data is removed

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tblAssets.IPAddress,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease) As OS,
tblADComputers.Description
From tblComputersystem
Right Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where (tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1) Or
(tsysAssetTypes.AssetTypename = 'linux' And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

I think this is just an ordering issue in the relationship, but would like some pointers please !

Thanks

[JacobH]

I'd start from this very useful built-in report

Assets: All columns from the Assets menu


then filter by asset types of windows and linux...

then add the tbladcomputers.description (off the top of my head)...

remove any columns you don't want, SAVE AS .... and that should be good.