Custom report for systems with multiple IP addresses

[oagtexas]

Ok, I was able to piece together a report that is 99% of what I need, but I have the occasional duplicate entry when the coalesced field returns different values (single IP vs. multiple IPs).

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Custom5 As [System/Env],
  Coalesce(tblNetwork.IPAddress, tblLinuxNetworkDetection.Ipv4,
  tblAssets.IPAddress) As [All IP Addresses],
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Left Join tblLinuxNetworkDetection On tblAssets.AssetID =
    tblLinuxNetworkDetection.AssetID
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Where (tblAssets.IPAddress Is Not Null And Coalesce(tblNetwork.IPAddress,
  tblLinuxNetworkDetection.Ipv4, tblAssets.IPAddress) Like '%5.5.%'
  And tblAssetCustom.State = 1) Or
  (tblAssets.IPAddress Is Not Null And Coalesce(tblNetwork.IPAddress,
  tblLinuxNetworkDetection.Ipv4, tblAssets.IPAddress) Is Null And
  tblAssetCustom.State = 1)

In the results I see 2 entries for the exact same system. One would have the coalesced field containing a single IP address (5.5.1.2). The second one would have the coalesced field containing the (correct) multiple assigned IPs (5.5.1.2, 5.5.1.9, 5.5.1.12, 5.5.1.21)

Obviously for a report like this I ONLY want to see the entry that correctly reports the multiples.

Any thoughts on this? I feel like I'm only one or two lines away from nailing it.

[oagtexas · Accepted solution]

My Solution:

Select Top 1000000 TableResults.AssetID,
  TableResults.AssetName,
  TableResults.AssetTypename,
  TableResults.[System/Env],
  TableResults.IPLocation,
  Max(TableResults.[All IP Addresses]) As [All IP Addresses],
  TableResults.icon
From (Select tblAssets.AssetID,
    tblAssets.AssetName,
    tblAssets.IPAddress,
    tsysIPLocations.IPLocation,
    tblAssetCustom.Custom5 As [System/Env],
    Coalesce(tblNetwork.IPAddress, tblLinuxNetworkDetection.Ipv4,
    tblAssets.IPAddress) As [All IP Addresses],
    tsysAssetTypes.AssetTypename,
    tsysAssetTypes.AssetTypeIcon10 As icon
  From tblAssets
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
    Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
    Left Join tblLinuxNetworkDetection On tblAssets.AssetID =
      tblLinuxNetworkDetection.AssetID
    Left Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Where (tblAssets.IPAddress Is Not Null And Coalesce(tblNetwork.IPAddress,
    tblLinuxNetworkDetection.Ipv4, tblAssets.IPAddress) Like '%5.5.%' And
    tblAssetCustom.State = 1) Or
    (tblAssets.IPAddress Is Not Null And Coalesce(tblNetwork.IPAddress,
    tblLinuxNetworkDetection.Ipv4, tblAssets.IPAddress) Is Null And
    tblAssetCustom.State = 1)) As TableResults
Group By TableResults.AssetID,
  TableResults.AssetName,
  TableResults.AssetTypename,
  TableResults.[System/Env],
  TableResults.IPLocation,
  TableResults.icon,
  TableResults.IPAddress