This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Datacenter Port Usage report

jkeberle over 12 years ago

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?

0 jkeberle over 12 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel
0 jkeberle over 12 years ago in reply to jkeberle

BY GEORGE I GOT IT!!!!!!!!!!!!
Cancel
Vote Up 0 Vote Down

Cancel
0 jkeberle over 12 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel
0 jkeberle over 12 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel
0 goofyzig over 10 years ago in reply to jkeberle

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.
Cancel
Vote Up 0 Vote Down

Cancel