5 Replies Latest reply on Oct 24, 2013 5:17 PM by goofyzig

    Datacenter Port Usage report

    jkeberle

      I am trying to get a single report that highlights the total # of switch ports per Type (Ethernet, Gig, Ten Gig) per device (in this case switches) that resides in the data center.  To further the complications within this request, we have all unused ports in an Admin Down State.  Additionally those specific ports have been given a description of "UNUSED, RESERVED, or FAULTY) depending upon their current status.  The end report (if possible) would basically have just high level numbers as defined below.

       

      Switch Port Totals

      Name                    Interface Type             Total         In Use      Unused      Reserved         Faulty

      Switch01               Ethernet                      388         260           18              9                     1

                                   Gigabit Ethernet          48           18             20              10                   0

      Switch02               Ethernet                      244         200           30              10                   4

                                   Gigabit Ethernet          48           22             16              9                     1

                                   Ten Gigabit Ethernet    12           6               4                2                     0

       

      Is this a report that is possible to create? And if so, how would I go about doing it?

        • Re: Datacenter Port Usage report
          jkeberle

          So far I was playing with an Advanced SQL query report that had the following **WHICH ISNT WORKING** :(

           

          select  InterfaceTypeDescription, count(*)  Total,

          sum (case when InterfaceAlias = 'UNUSED' then 1 else 0 end) as Unused,

          sum (case when InterfaceAlias = 'RESERVED' then 1 else 0 end) as Reserved,

          sum (case when InterfaceAlias = 'BAD' then 1 else 0 end) as Bad

          from Interfaces

          where InterfaceAlias in ('UNUSED', 'BAD', 'RESERVED')

          group by  InterfaceTypeDescription

          order by count(*) desc

          • Re: Datacenter Port Usage report
            jkeberle

            The answer to do this is a using the following SQL query

             

            SELECT
            Interfaces.InterfaceTypeDescription AS Interface_Type_Description,
            COUNT(Interfaces.InterfaceType) AS COUNT_of_Interface_Type,
            sum(case when Interfaces.InterfaceAlias = 'UNUSED' then 1 else 0 end) as Unused,
            sum(case when Interfaces.InterfaceAlias = 'RESERVED' then 1 else 0 end) as Reserved,
            sum(case when Interfaces.InterfaceAlias = 'BAD' then 1 else 0 end) as Bad
            FROM
            Interfaces

            GROUP BY Interfaces.InterfaceTypeDescription
            ORDER BY 2 DESC

            • Re: Datacenter Port Usage report
              jkeberle

              Actually, I went back to the drawing board to help actually identify only the Ethernet Ports and then identify each of them based upon their actual Interface Speed as the Interface Type Description lists all ethernet ports as "Ethernet" and dont break down the different types of Ethernet interfaces.

               

              Below is my FINAL (for now) working query for my report. 

               

              NOTE: all my devices within a selected datacenter start with the hostname of stl2

               

              SELECT
              Nodes.NodeID AS NodeID,
              Nodes.Caption AS NodeName,
              Interfaces.InterfaceSpeed AS Interface_Speed,
              COUNT(Interfaces.InterfaceSpeed) AS Total,
              sum(case when Interfaces.InterfaceAlias = 'UNUSED' then 1 else 0 end) as Unused,
              sum(case when Interfaces.InterfaceAlias = 'RESERVED' then 1 else 0 end) as Reserved,
              sum(case when Interfaces.InterfaceAlias = 'BAD' then 1 else 0 end) as Bad
              FROM
              Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
               WHERE 
              (
                (Nodes.Caption LIKE 'stl2%') AND
                (
                 (Interfaces.InterfaceSpeed = 100000000) OR
                 (Interfaces.InterfaceSpeed = 1000000000) OR
                 (Interfaces.InterfaceSpeed = 10000000000))
              )

              GROUP BY Nodes.NodeID,Nodes.Caption,Interfaces.InterfaceSpeed
              ORDER BY 2 ASC, 3 ASC

                • Re: Datacenter Port Usage report
                  goofyzig

                  Very nice post, jkeberle, only issue is that this does not seem to include interfaces that are not being monitored by Solarwinds.     Every switch is monitored by Solarwinds, but not every switch port, so I wonder how we can get just the elementary total number of switch port interfaces in the environment.   This would seem like a basic stat Solarwinds should have out of the box.