Report showing both LAN connections active

[manderson]

We're having issues where our laptops are staying connected to wireless when they sit in their docking stations, causing networking problems. I've gotten a report together that shows when a wireless or wired connection is active, but I can't seem to show ONLY machines that have both the wireless and wired connection active. Could someone walk me through this? The query is posted below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssets.Username,
tblAssets.Lastseen,
tblAssets.IPAddress,
tblNetworkAdapter.NetConnectionID,
tblNetworkAdapter.Name,
tblNetworkAdapter.Manufacturer,
tblNetworkAdapter.Speed,
tblNetworkAdapter.NetEnabled,
TsysChassisTypes.ChassisName
From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Inner Join tblSystemEnclosure On dbo.tblAssets.AssetID =
tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Where (tsysIPLocations.IPLocation = 'Parkway VPN Employee Users') Or
(tsysIPLocations.IPLocation = 'Tarrytown VPN Employee Users' And
tblNetworkAdapter.NetConnectionID Not In ('Local Area Connection',
'Local Area Connection 2', 'Local Area Connection 3',
'Local Area Connection 4', 'Local Area Connection 5', 'LAN-Wired',
'Wireless Network Connection', 'Wireless Network Connection 7',
'Wireless Network Connection 4', 'Wireless Network Connection 2') And
tblNetworkAdapter.NetEnabled = 'True')

Thanks for any help.

[esr · Accepted solution]

We went with a simplified approach to the report, looking at how many connections and filtering it to those with a count greater than 1. This gave us a quick scale of the problem machines. In our case we learned the vast majority were Dell laptops.

To help resolve the issue we're using Lansweeper to deploy a configuration update to our Dell laptops using the automatic "After Scanning" schedule and a target SQL report that filters to the specific models where we can make corrections. The deployment checks for, and if needed installs, the Dell CCTK BIOS utility and then updates the BIOS to prevent simultaneous LAN and WiFi connections by default. In cases where there is a specific need this can be overridden.

Select Top 1000000 tblAssets.AssetUnique,
  tblAssets.AssetID,
  tblNetwork.DNSHostname As [Computer Name],
  tblAssets.Username,
  Count(tblNetwork.IPAddress) As [Active IP Addresses],
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblADComputers.OU
From tblAssets
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblNetwork.DHCPenabled = 1 And tblNetwork.IPAddress Not Like '192.168' And
  tblAssetCustom.State = 1
Group By tblAssets.AssetUnique,
  tblAssets.AssetID,
  tblNetwork.DNSHostname,
  tblAssets.Username,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblADComputers.OU
Having Count(tblNetwork.IPAddress) > 1
Order By tblAssets.AssetID