Need help with a Report

[Robert1]

I'd like to create a report based on IP location. Very similar to what you get when you click on IP location overview once you have that setup.

Thing is I need several ranges of IPs and additional info. Specifically I'd like:

AssetName
IP
Domain
OS Name
Caption
SP

If someone can give me an example of something like this I can likely wing it from there.

thank you,

Robert

[Hemoco]

Not sure what you'd like, but this makes sense:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
tsysOS.Image As icon,
tblAssets.SP,
tsysIPLocations.IPLocation,
tsysAssetTypes.AssetTypename
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And (tblAssets.Assettype <> -1 Or
(tblAssets.Assettype = -1 And (tblComputersystem.Domainrole > 1 Or
tblComputersystem.Domainrole Is Null)))
Order By tsysAssetTypes.AssetType,
tblComputersystem.Domainrole

[Robert1]

Thank you sir that is almost exactly what I wanted, how would I exclude Desktops? I'm still interested in Switches, Servers, and other devices.

Either that or limit it to specific IP locations.

thanks again,

Robert

[Hemoco · Accepted solution]

Something like this?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
tsysOS.Image As icon,
tblAssets.SP,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1