Custom SWQL Formatting?

shyde

I don't have a lot of expertise around SWQL and not sure if what I would like is possible.

I use the query below which reports back devices of a specific machine type that match the target software version and those that do not. What I am wanting to do now is make it easier to see the counts of upgraded versus not upgraded. I would like to use a green background or red background or font colour etc. to differentiate the two counts. 

Additionally, I would love to see the counts compared in a pie graph or bar chart or other visual representation to showcase where we are when in the midst of a large upgrade process being managed by Solarwinds.

Is this possible with the custom SWQL widget?

Any recommendations on how to achieve this would be greatly appreciated. My only limitation is I can't open up any security risks.

SELECT dd.MachineType AS [Device Type]
	,'/NetPerfMon/images/Vendors/' + dd.Icon AS [_IconFor_Device Type]
	,dd.IOSVersion
	,COUNT(dd.IOSVersion) AS Total
FROM (
	SELECT n.Caption AS Device
		,n.Icon
		,REPLACE(CASE 
				WHEN n.HardwareHealthInfos.Model = ''
					THEN n.MachineType
				ELSE ISNULL(n.HardwareHealthInfos.Model, n.MachineType)
				END, 'Cisco Catalyst ', '') AS Model
		,n.MachineType
		,CASE 
			WHEN n.IOSVersion NOT IN ('target software version')
				THEN 'Not Upgraded'
			ELSE n.IOSVersion
			END AS IOSVersion
	FROM Orion.Nodes n
	WHERE Status != '9'
		AND n.MachineType IN (
			'Cisco IE-4000-4GS8GP4G-E Industrial Ethernet Switch'
			,'Cisco IE-4010-16S12P Industrial Ethernet Switch'
			,'Cisco IE-4010-4S24P Industrial Ethernet Switch'
			,'IE-4000-4T4P4G-E'
			)
	) dd
GROUP BY dd.MachineType
	,dd.Icon
	,dd.IOSVersion
ORDER BY dd.IOSVersion

KMSigma.SWI

This is actually an ideal use case for a Modern Dashboard Proportional Widget.  The only thing I'd (personally) add would be color to indicate good or bad.

SELECT [DeviceDetails].IOSVersion
     , [DeviceDetails].Color 
     , COUNT([DeviceDetails].IOSVersion) AS Total
FROM (
     SELECT [Nodes].Caption AS Device
          , [Nodes].Icon
          , REPLACE(CASE 
                    WHEN [Nodes].HardwareHealthInfos.Model = ''
                         THEN [Nodes].MachineType
                    ELSE ISNULL([Nodes].HardwareHealthInfos.Model, [Nodes].MachineType)
                    END, 'Cisco Catalyst ', '') AS Model
          , [Nodes].MachineType
          , CASE 
               WHEN [Nodes].IOSVersion NOT IN ('target software version')
                    THEN 'Not Upgraded'
               ELSE [Nodes].IOSVersion
               END AS IOSVersion
          , CASE 
               WHEN [Nodes].IOSVersion NOT IN ('target software version')
                    THEN '#dc3938'
               ELSE '#7cc364'
               END AS Color
     FROM Orion.Nodes AS [Nodes]
     WHERE [Nodes].Status != '9'
          AND [Nodes].MachineType IN (
               'Cisco IE-4000-4GS8GP4G-E Industrial Ethernet Switch'
               , 'Cisco IE-4010-16S12P Industrial Ethernet Switch'
               , 'Cisco IE-4010-4S24P Industrial Ethernet Switch'
               , 'IE-4000-4T4P4G-E'
               )
     ) AS [DeviceDetails]
GROUP BY [DeviceDetails].IOSVersion
       , [DeviceDetails].Color 
ORDER BY [DeviceDetails].IOSVersion

I can't give you a step-by-step because I don't have any Cisco devices in my lab, but the above query should work as a jumping off point.