Report Help SQL with Cores

[chads]

We have this report but I need to add IP Location how do I do that.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblSqlServers.displayVersion As Edition,
tblSqlServers.skuName As License,
tblSqlServers.spLevel As SP,
tblSqlServers.serviceName As Service,
tblAssets.NrProcessors As Processors,
tblPROCESSOR.NumberOfCores * tblAssets.NrProcessors As TotalCores,
tblSqlServers.lastChanged,
tsysOS.Image As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Inner Join tblPROCESSOR On tblAssets.AssetID = tblPROCESSOR.AssetID
Where tblSqlServers.skuName Not Like '%xpress%' And
tblSqlServers.skuName Not Like '%Windows internal%' And tblAssetCustom.State =
1
Order By tblAssets.AssetName,
Edition

[Daniel_B · Accepted solution]

In order to display the IP location of an asset, add tsysIPLocations to your report. You'll need to join it directly in the SQL code to tblAssets.IPNumeric with a line like

Left Join tsysIPlocations
on tblAssets.IPNumeric Between tsysIPLocations.StartIP and tsysIPLocations.EndIP

Please find the full modified report below:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysIPLocations.IPLocation,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As SP,
  tblSqlServers.serviceName As Service,
  tblAssets.NrProcessors As Processors,
  tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
  tblSqlServers.lastChanged,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
Where tblSqlServers.skuName Not Like '%xpress%' And
  tblSqlServers.skuName Not Like '%Windows internal%' And tblAssetCustom.State =
  1
Order By tblAssets.AssetName,
  Edition