→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
paultech
Engaged Sweeper
Hi all,

I have put together a quick query to find the DNS server search order, which works ok generally speaking however each PC in the results has 9 additional duplicates

I was wondering if anyone knows how I can remove those duplicate rows with no values in
My report looks like this:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblOperatingsystem.Caption,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Username,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.DNSServerSearchOrder,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblAssetCustom.State = 1
Order By tblADComputers.OU
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
First thought: if you're specifically interested in the DNS search order, filter out any network interfaces that don't have any. Add to the WHERE clause
  AND tblNetwork.DNSServerSearchOrder <> ''

If you're still seeing too many interfaces, add more details from tblNetwork to your report to see if there's anything else you can filter on.

View solution in original post

2 REPLIES 2
RCorbeil
Honored Sweeper II
First thought: if you're specifically interested in the DNS search order, filter out any network interfaces that don't have any. Add to the WHERE clause
  AND tblNetwork.DNSServerSearchOrder <> ''

If you're still seeing too many interfaces, add more details from tblNetwork to your report to see if there's anything else you can filter on.
Thank you very much RC62N, that has worked perfectly