IP Locations

[Malbanese]

My organization is trying to retire an old domain and i need to find the machines that have static DNS entries pointing to those servers and what location they are in.

I can get the network information but when i try to add the IP Location data i get no data in that field.

Here is the query::::::::::::::::::::::::::::::::::::::::::::::::::::

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblNetwork.IPAddress,
tblNetwork.DNSServerSearchOrder,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric = tsysIPLocations.StartIP And
tblAssets.IPNumeric = tsysIPLocations.EndIP
Where (tblNetwork.IPAddress <> '0.0.0.0' And tblNetwork.IPAddress <> '') And
tblNetwork.IPEnabled = 1 And tblNetwork.DHCPenabled = 0 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Malbanese]

That worked perfectly!

Thank you

[Hemoco · Accepted solution]

Please use the query below to add the IP location to the report.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblNetwork.IPAddress,
tblNetwork.DNSServerSearchOrder,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where (tblNetwork.IPAddress <> '0.0.0.0' And tblNetwork.IPAddress <> '') And
tblNetwork.IPEnabled = 1 And tblNetwork.DHCPenabled = 0 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName