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 LanSweeper Reports - merge
by  NDR88   Go to last post Go to first unread
Last post: 1/14/2021 7:41:25 PM(UTC)
Lansweeper Duplicate computers in Webcam query
by  RC62N  
Go to last post Go to first unread
Last post: 1/14/2021 4:36:50 PM(UTC)
Lansweeper Performance REPORT:AVERAGE CPU, RAM & DISK USAGE IN %
by  MassimoAfros   Go to last post Go to first unread
Last post: 1/13/2021 3:57:47 PM(UTC)
Lansweeper Virtual Guest machines report- Add guest OS
by  Andy.S  
Go to last post Go to first unread
Last post: 1/12/2021 9:22:54 AM(UTC)
Lansweeper Print Last OU instead of Full OU
by  RC62N   Go to last post Go to first unread
Last post: 1/11/2021 10:08:39 PM(UTC)
Lansweeper Help Desk with Root Cause
by  Daniel B  
Go to last post Go to first unread
Last post: 1/5/2021 5:57:53 PM(UTC)
Report Center Show newly discovered software
by  dpjhoward  
Go to last post Go to first unread
Last post: 12/16/2020 10:15:05 PM(UTC)