Adding IP Location using SQL Syntax

[John_H]

Hi All,

I would like to add IP location to the report below. I found this article explaining how to do so

http://m.lansweeper.com/Forum/yaf_postst10743_Adding-IP-locations-to-a-report.aspx#post40242

However, I CANNOT right click on the link created between fields (no idea why not, I can right click and get the menu options for everything else).

Here is the current report I am using for Microsoft Software and I need some help with the SQL query syntax to add IP Location to the output

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey,
tsysOS.Image As icon
From tblAssets
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblSerialnumber.Product Like '%windows%' Or
tblSerialnumber.Product Like '%office%') And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique,
tblSerialnumber.Product

[Daniel_B · Accepted solution]

You could either try another browser or do it directly in the SQL code. Just change the "Inner Join" to tsysiplocations to "Left Join".

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysIPLocations.IPLocation,
  tsysOS.OSname,
  tblSerialnumber.Product,
  tblSerialnumber.ProductID,
  tblSerialnumber.ProductKey,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
Where (tblSerialnumber.Product Like '%windows%' Or
    tblSerialnumber.Product Like '%office%') And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique,
  tblSerialnumber.Product