Devices connected to wired and wifi

[cctech]

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.

[chenegh]

Did you ever figure this out?

[RCorbeil]

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]

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]

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]

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]

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]

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]

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]

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.