cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
oagtexas
Engaged Sweeper III
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.
1 ACCEPTED SOLUTION
oagtexas
Engaged Sweeper III
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

View solution in original post

1 REPLY 1
oagtexas
Engaged Sweeper III
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