cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Brendan440
Engaged Sweeper II
Is there a way to get the Computer: IP List report to show ALL the IP addresses associated with a system? Just trying to track down users running wireless and wired connections.
1 ACCEPTED SOLUTION
Jeremy_D
Champion Sweeper
The report you are referring to only lists the most recently scanned IP address of each machine, not the IP addresses of all your network adapters. For a complete list of all of a Windows computer's network adapters you will need to include the tblNetwork database table. We have included a sample report below.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tsysIPLocations.IPLocation,
tblAssets.AssetID,
tblAssets.Lastseen,
tblNetwork.IPEnabled,
tblNetwork.IPAddress [IP Address Network Adapter],
tblNetwork.Description As Adapter
From tblAssets
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPEnabled = 1 And tblAssetCustom.State = 1
Order By tblAssets.IPNumeric

View solution in original post

2 REPLIES 2
grandfinalemike
Engaged Sweeper
Just wondering if this worked for anyone. for my query, i continued to see assets that had only 1 ip address enabled, so, i still saw all entries instead of just the ones with 2 ip addresses.
I was able to modify the query and came up with this one that seemed to work

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblNetwork.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.Description
From tblNetwork As tblA
Right Join tblAssets On tblAssets.AssetID = tblA.AssetID
Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblNetwork.IPEnabled = 1 And Not tblNetwork.IPAddress Is Null
Group By tblAssets.AssetID,
tblAssets.AssetUnique,
tblNetwork.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.Description,
tsysAssetTypes.AssetTypename,
tblAssetCustom.State,
tsysAssetTypes.AssetTypeIcon10,
tsysAssetTypes.AssetTypeIcon16,
tblAssets.AssetName
Having Count(tblA.IPAddress) > 1
Order By tblAssets.AssetName
Jeremy_D
Champion Sweeper
The report you are referring to only lists the most recently scanned IP address of each machine, not the IP addresses of all your network adapters. For a complete list of all of a Windows computer's network adapters you will need to include the tblNetwork database table. We have included a sample report below.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tsysIPLocations.IPLocation,
tblAssets.AssetID,
tblAssets.Lastseen,
tblNetwork.IPEnabled,
tblNetwork.IPAddress [IP Address Network Adapter],
tblNetwork.Description As Adapter
From tblAssets
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPEnabled = 1 And tblAssetCustom.State = 1
Order By tblAssets.IPNumeric