Notification

Icon
Error

Report - All Assets with Specfic Default Gateway

Posted: Tuesday, November 24, 2020 4:48:23 PM(UTC)
tangobravo

tangobravo

Member Original PosterPosts: 2
0
Like
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
RC62N
#1RC62N Member Posts: 511  
posted: 11/24/2020 9:13:40 PM(UTC)
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.
tangobravo
#2tangobravo Member Original PosterPosts: 2  
posted: 11/25/2020 4:00:33 PM(UTC)
Still only showing windows assets with

Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
RC62N
#3RC62N Member Posts: 511  
posted: 11/25/2020 4:18:08 PM(UTC)
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.

Active Discussions

Lansweeper LSAgent Rescan Asset
by  marck1024   Go to last post Go to first unread
Last post: Today at 4:11:53 PM(UTC)
Lansweeper Allowing a role to create asset groups
by  mh6543   Go to last post Go to first unread
Last post: 2/26/2021 10:00:40 PM(UTC)
Lansweeper Export LsAgent Assets
by  steveb  
Go to last post Go to first unread
Last post: 2/26/2021 7:26:32 PM(UTC)
Lansweeper Asset Uptime "Asset on total"
by  Andy.S   Go to last post Go to first unread
Last post: 2/26/2021 3:07:35 PM(UTC)
Lansweeper Scanning for Static Routes?
by  ChrisParr  
Go to last post Go to first unread
Last post: 2/26/2021 12:43:28 PM(UTC)
Lansweeper lansweeperuser sql database permissions
by  David K.   Go to last post Go to first unread
Last post: 2/25/2021 11:54:38 PM(UTC)
Lansweeper Scanning certificates
by  Vincent Pollock  
Go to last post Go to first unread
Last post: 2/25/2021 8:35:11 PM(UTC)