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: 494  
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: 494  
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 Rest API for Scanning
by  Brian Smith   Go to last post Go to first unread
Last post: Today at 7:35:27 PM(UTC)
Lansweeper Language translation not working anymore
by  Florian  
Go to last post Go to first unread
Last post: Today at 6:03:19 PM(UTC)
Lansweeper Use current user for scanning credentials?
by  pryan67   Go to last post Go to first unread
Last post: Today at 6:01:33 PM(UTC)
Lansweeper Bluetooth Info
by  FrankSc  
Go to last post Go to first unread
Last post: Today at 5:42:59 PM(UTC)
Lansweeper Question about Scanning Assets and Password Encryption
by  FrankSc   Go to last post Go to first unread
Last post: Today at 5:23:05 PM(UTC)
Lansweeper How to enable HTTPS in ver 6.0.150.60
by  Duncan.Miles  
Go to last post Go to first unread
Last post: Today at 1:11:19 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: Yesterday at 3:22:29 PM(UTC)
Lansweeper MS Edge Chromium LanSweeper Extension development
by  Slim D  
Go to last post Go to first unread
Last post: 1/15/2021 11:39:29 AM(UTC)