cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cctech
Engaged Sweeper II
I would like to create a report to show which devices have both a wired and wireless connection to the network?

This way I can isolate the devices and turn off the wireless connection if it is not needed.
9 REPLIES 9
chenegh
Engaged Sweeper II
Did you ever figure this out?
RCorbeil
Honored Sweeper II
If you have devices with more than two connected NICs, you may want to change the "SELECT Top 1000000" to "SELECT Distinct" to reduce the result set.

Without the Distinct, you would see:
  • NIC1 (because it matches NIC2)
  • NIC1 (because it matches NIC3)
  • NIC2 (because it matches NIC1)
  • NIC2 (because it matches NIC3)
  • NIC3 (because it matches NIC1)
  • NIC3 (because it matches NIC2)
I can the possibility if, for example, you have a laptop with integrated NIC, wifi, and a docking station with its own separate NIC or if you have something like a Surface tablet with wifi and multiple different USB-connected wired NICs or docks.
tn206
Engaged Sweeper
Thank you so much for help. Your query/script works but unfortunately, the result is very limited to what I am tasked to accomplish. The result only showed about 30 devices out of 1000-2000 devices that are on the network. Maybe you can further help me out if I explain my task. I am an intern at a community college and the task at hand is to find all the college-owned devices that are wifi capable, including Desktops with wireless cards installed, Laptops, Chromebooks, Mobile phones, Wireless Access Points, and Tablets. To make a long story short, we don't have the access points reports for some reasons. Do you have any suggestions to achieve this task? Again, thank you so much for your time. I appreciate it.
RCorbeil
Honored Sweeper II
I don't have an installation of SQL CE to experiment with, but give this a try. From the quick Google results, I gather that CE has problems with subqueries that return distinct values -- the Count(*) in the code I initially offered -- but it should be OK with subqueries that return sets of results. If that's the case, try joining the base "show me the NICs" query against itself in a subquery but specifically make the link against not-the-current-NIC.

It's not elegant, but it's a starting point if it works.

Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.IPAddress,
tblNetwork.Description
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
And tblNetwork.IPAddress <> ''
And tblNetwork.IPAddress <> '0.0.0.0'
And tblNetwork.Description Not Like 'VMWare%'
And tblNetwork.Description Not Like '%virtua%'
And tblNetwork.Description Not Like '%Loopback%'
And tblNetwork.Description Not Like '%USB Remote NDIS Network Device%'
Inner Join ( SELECT
tblAssets.AssetID,
tblNetwork.IPAddress,
tblNetwork.Description
FROM
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
And tblNetwork.IPAddress <> ''
And tblNetwork.IPAddress <> '0.0.0.0'
And tblNetwork.Description Not Like 'VMWare%'
And tblNetwork.Description Not Like '%virtua%'
And tblNetwork.Description Not Like '%Loopback%'
And tblNetwork.Description Not Like '%USB Remote NDIS Network Device%'
WHERE
tblAssetCustom.State = 1
) AS SecondNIC ON SecondNIC.AssetID = tblAssets.AssetID
AND SecondNIC.Description <> tblNetwork.Description
AND SecondNIC.IPAddress <> tblNetwork.IPAddress
Where tblAssetCustom.State = 1
Order By
tblAssets.AssetName,
tblNetwork.Description
RCorbeil
Honored Sweeper II
The resulting line count doesn't matter: that's just the LANSweeper report editor reformatting the query.

Are you by any chance using SQL Server Compact Edition? Some very quick Googling suggests that CE has some issues with subqueries.
tn206
Engaged Sweeper
Thanks for the quick reply and yes we are using the compact version of SQL. Is that what caused the error? Is there a workaround?
tn206
Engaged Sweeper
Hi,
Thank you for the script. Unfortunately, I couldn't get it to run. I am getting "There was an error parsing the query. [ Token line number = 1,Token line offset = 813,Token in error = Select ]. I also that after pasting your 33 lines script, lansweeper truncated it to 26 lines: (see below)

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.IPAddress,
tblNetwork.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID And
tblNetwork.IPAddress <> '' And tblNetwork.IPAddress <> '0.0.0.0' And
tblNetwork.Description Not Like 'VMWare%' And
tblNetwork.Description Not Like '%virtua%' And
tblNetwork.Description Not Like '%Loopback%' And
tblNetwork.Description Not Like '%USB Remote NDIS Network Device%'
Where tblAssetCustom.State = 1 And (Select Count(*) From tblNetwork
Where tblNetwork.AssetID = tblAssets.AssetID And tblNetwork.IPAddress <>
'' And tblNetwork.IPAddress <> '0.0.0.0' And
tblNetwork.Description Not Like 'VMWare%' And
tblNetwork.Description Not Like '%virtua%' And
tblNetwork.Description Not Like '%Loopback%' And
tblNetwork.Description Not Like '%USB Remote NDIS Network Device%') > 1
Order By tblAssets.AssetName,
tblNetwork.Description

I'm using the latest version of Lansweeper v. 7.1.110.7. Not sure what I am doing wrong but any help would be appreciate it. Thank you.
RCorbeil
Honored Sweeper II
Minor correction. The LANSweeper report editor doesn't like it when output columns have the same name. The first instance of IPAddress was redundant anyway.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.IPAddress,
tblNetwork.Description
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
And tblNetwork.IPAddress <> ''
And tblNetwork.IPAddress <> '0.0.0.0'
And tblNetwork.Description Not Like 'VMWare%'
And tblNetwork.Description Not Like '%virtua%'
And tblNetwork.Description Not Like '%Loopback%'
And tblNetwork.Description Not Like '%USB Remote NDIS Network Device%'
Where tblAssetCustom.State = 1
And (Select Count(*)
From tblNetwork
Where tblNetwork.AssetID = tblAssets.AssetID
And tblNetwork.IPAddress <> ''
And tblNetwork.IPAddress <> '0.0.0.0'
And tblNetwork.Description Not Like 'VMWare%'
And tblNetwork.Description Not Like '%virtua%'
And tblNetwork.Description Not Like '%Loopback%'
And tblNetwork.Description Not Like '%USB Remote NDIS Network Device%') > 1
Order By
tblAssets.AssetName,
tblNetwork.Description
RCorbeil
Honored Sweeper II
Try this is a launch point:
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.IPAddress,
tblNetwork.Description
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
And tblNetwork.IPAddress <> ''
And tblNetwork.IPAddress <> '0.0.0.0'
And tblNetwork.Description Not Like 'VMWare%'
And tblNetwork.Description Not Like '%virtua%'
And tblNetwork.Description Not Like '%Loopback%'
And tblNetwork.Description Not Like '%USB Remote NDIS Network Device%'
Where
tblAssetCustom.State = 1
And (Select Count(*)
From tblNetwork
Where
tblNetwork.AssetID = tblAssets.AssetID
And tblNetwork.IPAddress <> ''
And tblNetwork.IPAddress <> '0.0.0.0'
And tblNetwork.Description Not Like 'VMWare%'
And tblNetwork.Description Not Like '%virtua%'
And tblNetwork.Description Not Like '%Loopback%'
And tblNetwork.Description Not Like '%USB Remote NDIS Network Device%') > 1
Order By
tblAssets.AssetName,
tblNetwork.Description

It won't distinguish wired from wireless, but it will list machines with multiple IP addresses and the NIC with which they're associated.