Total number of Switch ports and Total of Free ports/used ports

[Argon0]

One of our metrics, reported at a regular management meeting, is switchport capacity across the enterprise.

At the moment this is a labour intensive exercise, taking counts from each switch/stack and using Excel.

So I'd like a report showing all the switches (or stacks) and routers, showing how many ports are in use, and how many not, and totals of each...

Then I'd like to see the same info (totals only maybe) as a chart....

Any ideas?

Cheers

Argon0

[Hendrik_VE]

Actually, if you want to KIS, you could also use this report and save it as "Chart: Switch Ports" to show a pie diagram on the Dashboard:

Select Top 1000000 Case
    When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
    When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
    When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
    When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
    When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
    When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
    When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
    Else 'Other'
  End As 'Interface Status',
  count(*) As 'Number of ports'
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 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') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
Group By Case
    When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
    When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
    When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
    When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
    When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
    When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
    When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
    Else 'Other'
  End

[Hendrik_VE]

I think you should come pretty close with this report. Just export it to Excel and create your own filters and graphs (or even better, export it to PowerBI).

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblSNMPInfo.ifAlias,
  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'
    When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
    When tblSNMPInfo.IfAdminstatus = 3 Then 'Testing'
    Else 'Other'
  End As 'Admin Status',
  Case
    When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
    When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
    When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
    When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
    When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
    When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
    When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
    Else 'Other'
  End As 'Operational Status',
  tblSNMPAssetMac.LastSeen As 'Last Detected',
  tblSNMPAssetMac.Lastchanged As 'Last Changed',
  tblAssets1.Firstseen As 'Asset First Seen',
  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.IfOperstatus = 1 And tblSNMPInfo.IfAdminstatus = 1 Then
      '#d4f4be'
    When tblSNMPInfo.IfOperstatus = 2 And tblSNMPInfo.IfAdminstatus = 1 Then
      '#ffff00'
    When tblSNMPInfo.IfOperstatus = 2 And tblSNMPInfo.IfAdminstatus = 2 Then
      '#ffadad'
    Else '#00ffff'
  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]

Example what it could look like in Power BI (just a quick 5 minutes sketch):

https://imgur.com/UPwkhJ3

[cscherrey]

Did you ever figure out how to accomplish this with Lansweeper?