Report - All Assets with Specfic Default Gateway

[tangobravo]

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

[RCorbeil]

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]

Still only showing windows assets with

Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID

[RCorbeil]

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.