IP Range Location add to current report

[rathm]

I need to be able to add the IP location to this report so I know which city the device is located in. I had it in version 4.x but version 5.x is kicking my butt. Here is the code I currently have,

Select Top 1000000 tblAssets.Username As Username,
tblAssets.AssetName As PC_Name,
tblAssetCustom.Model As Model,
tblOperatingsystem.Caption As OS,
Cast(tblAssets.Memory / 1024 As numeric) As [GB of RAM],
tblAssets.IPAddress
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tblAssetCustom.Model = 'Latitude E6400') Or
(tblAssetCustom.Model = 'Latitude E4200') Or
(tblAssetCustom.Model = 'Latitude E4300') Or
(tblAssetCustom.Model = 'Latitude E6500') Or
(tblAssetCustom.Model = 'Latitude E6410') Or
(tblAssetCustom.Model = 'HP Compaq dc5750 Small Form Factor') Or
(tblAssetCustom.Model = 'HP Compaq 2710p') Or
(tblAssetCustom.Model = 'HP Compaq 2710p (#ABA)')

Thanks in advance.

-rathm

[rathm]

Thanks again for your help. That is exactly what I needed.

[Hemoco · Accepted solution]

Please try the report below.

Select Top 1000000 tblAssets.Username As Username,
tblAssets.AssetName As PC_Name,
tblAssetCustom.Model As Model,
tblOperatingsystem.Caption As OS,
Cast(tblAssets.Memory / 1024 As numeric) As [GB of RAM],
tblAssets.IPAddress,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where (tblAssetCustom.Model = 'Latitude E6400') Or
(tblAssetCustom.Model = 'Latitude E4200') Or
(tblAssetCustom.Model = 'Latitude E4300') Or
(tblAssetCustom.Model = 'Latitude E6500') Or
(tblAssetCustom.Model = 'Latitude E6410') Or
(tblAssetCustom.Model = 'HP Compaq dc5750 Small Form Factor') Or
(tblAssetCustom.Model = 'HP Compaq 2710p') Or
(tblAssetCustom.Model = 'HP Compaq 2710p (#ABA)')