Search for IP Location

[fjca]

Hello,

I've been trying to make a report in which I search for
IPLocation, not Group By, but with a Where (or similar)
statement.

Taking a example from a few posts back,I needed something
this, but with a clause like

Where tsysIPLocations.IPlocation Like '%AAA%'

But that doesn't work, and with my non-existant SQL
skills I could not make it work.

Does anybody have a ideia how to do it ?

Select Top 1000000 tsysIPLocations.IPLocation,
 tblAssetCustom.Manufacturer,
 tblAssetCustom.Model,
 Count(tblAssets.AssetID) As Count
From tblAssets
 Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
 Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
 tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tsysIPLocations.IPLocation,
 tblAssetCustom.Manufacturer,
 tblAssetCustom.Model

[Hemoco · Accepted solution]

A sample report that lists assets in a specific IP location can be seen below. Replace YourLocation with the name of the IP location.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tsysIPLocations.IPLocation = 'YourLocation' And tblAssetCustom.State = 1