cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
chads
Lansweeper Alumni
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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