cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
The report below list all Switches and their ports

The report will only list assets that meet all of the following criteria:
  • The asset state is set to "active".
  • The asset has been successfully scanned at least once.
  • The asset is a switch.

Select Top 1000000 Switch.AssetID,
Switch.icon,
Switch.AssetName,
Switch.[If],
Switch.ifName,
Switch.ifAlias,
Switch.IfDescription,
Switch.Type,
Switch.Admin,
Switch.MTU,
Switch.Speed,
Switch.[IP Address],
Switch.Mask,
Switch.Portname,
Switch.Vlan,
Switch.MacAddress,
tsysAssetTypes1.AssetTypeIcon16 As icon2,
Switch.deviceassetid,
tblAssets.AssetName As deviceassetname,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Switch.AssetMacAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From (Select Top 1000000 tblAssets1.AssetID,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.ifName,
tblSNMPInfo.ifAlias,
tblSNMPInfo.IfDescription,
tblSNMPIfTypes.IfTypename As Type,
Case When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down' Else 'Testing'
End As Admin,
tblSNMPInfo.IfMTU As MTU,
Case When (tblSNMPInfo.IfSpeed Is Null Or tblSNMPInfo.IfSpeed = 0) Then ''
When tblSNMPInfo.IfSpeed >
999999999 Then Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed /
1000000000 As float)) + 'Gbit'
When tblSNMPInfo.IfSpeed >
999999 Then Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed /
1000000 As float)) + 'Mbit'
Else Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000 As float)) +
'Kbit' End As Speed,
tblSNMPInfo.IfIPAddress As [IP Address],
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MacAddress,
tblSNMPAssetMac.AssetMacAddress,
tblSNMPInfo.Portname,
tblSNMPInfo.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Left Join tblAssets As tblAssets1 On tblSNMPInfo.AssetID =
tblAssets1.AssetID
Left Join tsysAssetTypes On tblAssets1.Assettype = tsysAssetTypes.AssetType
Left Outer Join tblSNMPIfTypes On tblSNMPInfo.IfType = tblSNMPIfTypes.IfType
Left Outer Join (tblAssetMacAddress
Right Outer Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress) On tblSNMPInfo.IfIndex =
tblSNMPAssetMac.IfIndex And tblSNMPInfo.AssetID = tblSNMPAssetMac.AssetID
Where tsysAssetTypes.AssetTypename = 'Switch'
Order By tblAssets1.AssetName,
[If]) As Switch
Left Outer Join tblAssets On Switch.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes1 On tblAssets.Assettype =
tsysAssetTypes1.AssetType
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
1 REPLY 1
Okan
Engaged Sweeper III
Lansweeper lists an asset multiple times if that asset was connected to the network at different switch ports at different times. When I pull up the built-in Switch: Ports report, I see an asset multiple times. But only one of those is the active one. Can you provide a modified version of this report, so it lists the assets that actually have a connection to a switch port?