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

View solution in original post

3 REPLIES 3
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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