cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

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

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 Kudos
3 Replies
Community Manager
Community Manager

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

KMSigma_0-1592242042991.png

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.

"Shoot for the stars to reach the moon"
0 Kudos

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. 

0 Kudos
Community Manager
Community Manager

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

"Shoot for the stars to reach the moon"
0 Kudos