SNMP Switch Interface Report

[SebastianSelig]

Hello,

i want a report that looks like the summary page of a switch. I need the interface name, alias, vlan and if connected the asset name and also not used interfaces. Like the report from a windows pc.

Can someone help me to get this created via sql?

Thanks

[SebastianSelig]

That is not exactly what i need. I want the report that is located at the switch overview with all interfaces.

The sql i can define the assetid that i only get the on switch but it's not showing the "uplink" interfaces and the alias of the port.

[Nick_VDB]

You can use the following report to get the information that you are after. Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 aaa.AssetID,
  aaa.icon,
  aaa.AssetName,
  aaa.[If],
  aaa.Type,
  aaa.Admin,
  aaa.MTU,
  aaa.Speed,
  aaa.[IP Address],
  aaa.Mask,
  aaa.Portname,
  aaa.vlan,
  aaa.MacAddress,
  tsysAssetTypes_1.AssetTypeIcon16 As icon2,
  aaa.deviceassetid,
  tblAssets.AssetName As deviceassetname,
  aaa.AssetMacAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From (Select Top 1000000 tblAssets_1.AssetID,
    tsysAssetTypes.AssetTypeIcon16 As icon,
    tblAssets_1.AssetName,
    tblSNMPInfo.IfIndex As [If],
    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
    Inner Join tblAssets As tblAssets_1 On tblSNMPInfo.AssetID =
      tblAssets_1.AssetID
    Inner Join tsysAssetTypes On tblAssets_1.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 tblAssets_1.AssetID Is Not Null And tblAssets_1.Assettype = 6
  Order By tblAssets_1.AssetName,
    [If]) As aaa
  Left Outer Join tblAssets On aaa.deviceassetid = tblAssets.AssetID
  Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets.Assettype =
    tsysAssetTypes_1.AssetType
  Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Left Outer Join tsysIPLocations On tblAssets.LocationID =
    tsysIPLocations.LocationID