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.

Help with a Report that can show percentage of devices on current IOS vs. Total Device Count

Chupacabra over 3 years ago

Hi,

I am a Network guy not a SQL-guy, so hopefully one of you guru's can help me out with creating a report, or preferably a chart that will display the percentage of devices running what we are considering the current or standard version of IOS vs the total number of devices. I have written 2 really basic queries based on some examples I have found on the board but thats about as far as I have got. I would like to combine these into a single query and then divide Nodes_On_Standard_IOS by All_Nodes to get the percentage.

Ideally I am looking to built a chart that could be displayed on a dash showing the current % of of complaint devices and show a trend line over time say we start at 30% and as upgrades are completed the chart would show a trend moving upwards as the percentage climbs. There will be many more models of switches and routers but to keep things simple I just included a couple in the example below.

Thanks in advance!

SELECT COUNT(NodeID) AS 'All_Nodes'

FROM Nodes

WHERE ( (Nodes.Vendor = 'Cisco' ))

SELECT COUNT(NodeID) AS 'Nodes_On_Standard_IOS'

FROM Nodes

WHERE ( (Nodes.Vendor = 'Cisco' ))

AND (MachineType LIKE 'Catalyst 3560X%' AND IOSVersion LIKE '15.2(4)E10%')
OR (MachineType LIKE 'Catalyst 37xx Stack' AND IOSVersion LIKE '15.2(4)E10%')

0 KMSigma.SWI over 3 years ago

Did you see that this month's SolarWinds Lab is specifically on custom reporting? It's this Wednesday (June 17th)
Cancel
Vote Up 0 Vote Down

Cancel
0 KMSigma.SWI over 3 years ago

I just did it with the new Modern Dashboards.(versions 2020.2 required)

My SWQL Queries are the following:

Good IOS Versions:

SELECT SUM( CASE WHEN ( ( MachineType LIKE 'Cisco Catalyst 3560X%' AND IOSVersion LIKE '15.2(4)E10%' )
OR ( MachineType = 'Cisco Catalyst 37xx Stack' AND IOSVersion LIKE '15.2(4)E10%' )
OR ( MachineType = 'Cisco Catalyst 38xx stack' AND IOSVersion LIKE '03.06.10.%' ) ) THEN 1
ELSE 0 END ) AS [Cisco with Good IOS]
FROM Orion.Nodes
WHERE Vendor = 'Cisco'

All IOS Versions:

SELECT COUNT( Caption) AS [All Cisco Nodes ]
FROM Orion.Nodes
WHERE Vendor = 'Cisco'

True, it's not a pie chart or a report, but putting it on a dashboard seemed a quick way to get it.
Cancel
Vote Up 0 Vote Down

Cancel
0 Chupacabra over 3 years ago in reply to KMSigma.SWI

Thanks for the quick response unfortunately we are not up to that version yet so I don't think that will work. I did sign up for the lab you mentioned.
Cancel
Vote Up 0 Vote Down

Cancel