→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
fjca
Champion Sweeper II
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


1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

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