cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tangobravo
Engaged Sweeper
Im wanting a report that displays ALL assets that have a certain default gateway configured. Report below only returns back windows devices. Any advice?



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress As [Asset IPAddress],
tblNetwork.Description As Adapter,
tblNetwork.IPAddress As [NIC IPAddress],
tblNetwork.IPSubnet,
tblNetwork.DNSServerSearchOrder,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.DefaultIPGateway,
tsysOS.*
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.DefaultIPGateway = '192.168.21.%' And tblAssetCustom.State = 1
And tblNetwork.IPEnabled = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
Adapter
3 REPLIES 3
RCorbeil
Honored Sweeper II
My inventory is all-Windows, so I can't experiment with your query.

Were I you and I knew there should be more results, I would try changing the INNER JOINs to LEFT JOINs one by one to see if one of those was limiting the results. If not, start removing filters from the WHERE clause.

Basically it's down to eliminating any filters until you find what's blocking the data you expect to see, then re-apply the filters that should be in place to remove what you don't want in the result set.
tangobravo
Engaged Sweeper
Still only showing windows assets with

Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
RCorbeil
Honored Sweeper II
If you check the database documentation, you'll see that tblComputersystem only stores Windows computer data. By doing an INNER JOIN against that table, you're limiting your selection to Windows-only assets. Change it to a LEFT JOIN and see if that returns more results.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now