Switch: Ports (Built-in)

[Daniel_B]

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

[Okan]

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?