IP Range and Location REport

[Malbanese]

I recently upgraded to version 5.0.0.52 and I am trying to edit a custom report to show the Location of my assets.

I had IP Locations set up in the previous version and it looks like they are now called Asset Groups.

I need to add the location to this report but i don't get any data in the Location field.


Select Top 1000000 upgrade_tblComputers.Computername,
upgrade_tblComputers.Username,
upgrade_tblComputers.Computer,
tblADComputers.Location,
upgrade_tblNetwork.DNSServerSearchOrder,
upgrade_tblNetwork.IPAddress
From upgrade_tblComputers
Inner Join upgrade_tblNetwork On upgrade_tblComputers.Computername =
upgrade_tblNetwork.Computername
Inner Join tblADComputers On upgrade_tblComputers.Computername =
tblADComputers.AdcomputerID
Where (upgrade_tblNetwork.IPAddress <> '0.0.0.0' And
upgrade_tblNetwork.IPAddress <> '') And upgrade_tblNetwork.IPEnabled = 1 And
upgrade_tblNetwork.DHCPenabled = 0
Order By upgrade_tblComputers.Computer

[rathm]

I too am on version 5 and trying to rebuild my PC make, model, IP and location report. I am having problems figuring out hot to inner join my tsysIPLocations table. Here is my SQL code that I have.


Select Top 1000000 tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress,
tsysIPLocations.IPLocation
From tblAssetCustom
Inner Join tblAssets On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID,
tsysIPLocations

I can see everything but not where the IP is located from my IP Locations table.

I wonder why our SQL code is different. Yours has upgrade_ in front of all your tables?

Good Luck,

Mike

[Hemoco · Accepted solution]

rathm wrote:
I wonder why our SQL code is different. Yours has upgrade_ in front of all your tables?

"Upgrade" tables are used to convert old 4.2 reports, but not used for new queries. A sample IP location report can be seen below.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Order By tblAssets.IPNumeric