‎03-22-2016 02:50 PM
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')
Solved! Go to Solution.
‎04-01-2016 08:38 PM
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
‎04-01-2016 08:38 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now