Hi,
I am testing the following SQl quarry to grab total device count for Cisco devices and total Cisco devices running complaint IOS version in order to calculate the percentage and display it in a graph. I am not a SQL guy and have pieced this together but It would be nice to auto calculate the % of compliant devices and display that in a separate column. Anyone know how I might be able to achieve this?
SELECT ( SELECT COUNT(NodeID) FROM Nodes WHERE ( ( Nodes.Vendor = 'Cisco' ) ) ) AS 'All Cisco Devices', ( SELECT COUNT(NodeID) 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%') OR (MachineType LIKE 'Cisco Catalyst 36xx stack-able ethernet switch' AND IOSVersion LIKE '16.6.7, RELEASE SOFTWARE (fc2)') OR (MachineType LIKE 'Cisco Catalyst 36xx stack-able ethernet switch' AND IOSVersion LIKE '16.6.6, RELEASE SOFTWARE (fc1)') OR (MachineType LIKE 'Cisco Catalyst 3850-48P-E Switch' AND IOSVersion LIKE '16.6.7, RELEASE SOFTWARE (fc2)') OR (MachineType LIKE 'Cisco Catalyst 3850-48P-E Switch' AND IOSVersion LIKE '16.6.6, RELEASE SOFTWARE (fc1)') OR (MachineType LIKE 'Cisco Catalyst 38xx stack'AND IOSVersion LIKE '16.6.7, RELEASE SOFTWARE (fc2)') OR (MachineType LIKE 'Cisco Catalyst 38xx stack'AND IOSVersion LIKE '16.6.6, RELEASE SOFTWARE (fc1)') OR (MachineType LIKE 'Cisco 4%%1 ISR' AND IOSVersion LIKE '16.9.4, RELEASE SOFTWARE (fc2)') OR (MachineType LIKE 'Cisco 4%%1 ISR' AND IOSVersion LIKE '16.9.5, RELEASE SOFTWARE (fc1)') OR (MachineType LIKE 'Cisco ISR4451' AND IOSVersion LIKE '16.9.4, RELEASE SOFTWARE (fc2)') OR (MachineType LIKE 'Cisco ISR4451' AND IOSVersion LIKE '16.9.5, RELEASE SOFTWARE (fc1)') ) AS 'All Compliant Cisco Devices'