A report showing all of the switches and their configured ports along with vlan data

[kosta]

Hi,

Today I can see the switches and export them.
I can also enter a switch and see the configured ports showing data such as: Name,interface number,alias,speed, mac address and asset name of the network device connected to the port, but I cannot export it.

We sometimes have electrical outage and for some reason Vlans may change to default ones, which in turn affects network printers, workstations etc..

It would be great to have an Exportable Report that will show the entire switches in the organization along with their configured ports with an added VLAN data column (today it doesn't show vlan data).

Even if I could export the data of a single switch, I would need to repeat the procedure about 20 times.

With a single exportable report containing the above, I would be able to export the data when needed and resolve network device issues quickly.

Thank you in advanced.

[kosta]

Thank you very much for the solutions.

Both of them work as expected.

[Hendrik_VE]

I use the following (SQL Server) report which also shows me the vendor of the connected device when the MAC address is detected (if you still have table tsysMacVendor) and is color-coded based on the port status. Is very usefull to detect 'rogue' devices.

+1 for VLAN data.

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblSNMPInfo.IfIndex As [If],
  tblSNMPInfo.IfDescription As Name,
  tblSNMPIfTypes.IfTypename As Type,
case
when tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
else 'Down' 
End As Admin,
  tblSNMPInfo.IfMTU As MTU,
  Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
  tblSNMPInfo.IfIPAddress As IP,
  tblSNMPInfo.IfMask As Mask,
  tblSNMPInfo.IfMacaddress As SwitchportMAC,
  tblSNMPAssetMac.AssetMacAddress As AssetMAC,
  Case
    When tblSNMPAssetMac.AssetMacAddress is Null Then 'No MAC detected'
    When tsysMacVendor.Vendor Is Null Then 'MAC detected but Vendor not found'
    Else tsysMacVendor.Vendor
  End As AssetVendor,
  tsysAssetTypes_1.AssetTypeIcon16 As icon2,
  tblAssets1.AssetName As deviceassetname,
  tblAssets1.Username,
  tblAssets1.Userdomain,
  tblSNMPInfo.Vlan,
Case
when tblSNMPInfo.IfAdminstatus = 1 
    Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
  Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
  Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
    tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
  Left Join tsysMacVendor On
    tsysMacVendor.MAC = SubString(tblSNMPAssetMac.AssetMacAddress, 1, 2) +
    SubString(tblSNMPAssetMac.AssetMacAddress, 4, 2) +
    SubString(tblSNMPAssetMac.AssetMacAddress, 7, 2)
  Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
    tblSNMPAssetMac.AssetMacAddress
  Left Join tblAssets tblAssets1 On
    tblAssets1.AssetID = tblAssetMacAddress.AssetID
  Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
    tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename =
    'switch' Or tsysAssetTypes.AssetTypename = 'router') 
 Order By tblAssets.AssetName,
  [If]

[JacobH]

I modified 'Switch: Ports' report:

Select Top 1000000 aaa.AssetID,
  aaa.icon,
  aaa.AssetName,
  aaa.IfDescription,
  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.IfDescription,
        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
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Left Outer Join tsysIPLocations On tblAssets.LocationID =
    tsysIPLocations.LocationID