Filter by IP location Report

[JasloProductiuo]

First off... I admit, I am new to LanSweeper and im not huge guru on SQL. I have a report that I have created to view a specific version of software and would like to filter it by IP location. When I run the report it has all the items from that location in it and is filtering the proper version. I am running into a issue of LanSweeper stating PC's are in that IP location in the report, however when you click on the pc's the asset page says it is in a different IP location. I have tried using the = 'MYIPLOCATION' and i have tried Like 'MYIPLOC%'.It might be me not accurately filtering or may be me not knowing what I am doing. All criticism is welcome.

Here is my code:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftware.SoftwareID,
tblSoftware.softwareVersion,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID,
tsysIPLocations
Where tblSoftware.softwareVersion = '13.3.100.9' And
tsysIPLocations.IPLocation Like 'MYIPLOC%' And tblAssetCustom.State = 1

[JasloProductiuo]

Thank you very much. It wasn't a copy and paste like I hoped from your response. But was very helpful and I really appreciate it.

Here is the code that now works:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftware.SoftwareID,
tblSoftware.softwareVersion,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP,
tblAssets.IPNumeric
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblSoftware.softwareVersion = '13.3.100.9' And tsysIPLocations.StartIP =
199111222255 And tsysIPLocations.EndIP = 199111222112 And
tblAssetCustom.State = 1

[RCorbeil · Accepted solution]

You're not joining tblAssets to tsysIPLocations. You need that in order to identify what the location of the asset is. Replace

, tsysIPLocations

with

INNER JOIN tsysIPLocations ON tblAssets.IPNumeric >= tsysIPLocations.StartIP
    AND tblAssets.IPNumeric <= tsysIPLocations.EndIP

tblAssets.IPNumeric is the asset's IP address, padded out to three-digit numbers and with the periods dropped (e.g. 10.12.34.56 -> 010012034056). The StartIP and EndIP fields in tsysIPLocations are similarly formatted, allowing the equality comparison.