Custom SWQL Query for Temperature

Question

Hi Guys, 
 I have created the following SWQL, but really would like to Colour code the STATUS Columns, but im not familiar with the CASE commands, can anyone help?

SELECT DISTINCT n.Caption AS [Node Name], n.DetailsUrl AS [_LinkFor_Node Name], '/Orion/images/StatusIcons/Small-' + n.StatusIcon as [_IconFor_Node Name], Model, Name AS [Sensor Name], OriginalStatus AS [Status], Value FROM Orion.HardwareHealth.HardwareItem hi JOIN Orion.Nodes AS n ON n.nodeID = hi.nodeID JOIN Orion.HardwareHealth.HardwareInfoBase AS hib ON hib.ParentObjectID = hi.NodeID JOIN Orion.HardwareHealth.HardwareCategoryStatus AS hcs ON hcs.HardwareInfoID = hi.HardwareInfoID WHERE n.vendor='Cisco' AND hi.Status > '1' AND hi.HardwareCategoryID='4' AND hcs.HardwareCategoryName='Temperature' AND NOT hi.UniqueName LIKE 'HardwareSensor%' ORDER BY Value DESC 
 Please let me know asap as this has me stumpled....

sum_giais · Accepted Answer

Hi there greg.remer , here's an example of using the CASE statement within this query (note I made a few minor adjustments to the syntax of the query though). I *think* this CASE should give you an icon for the status... that is if I did this correctly! 
 Hope this helps! 
 SELECT DISTINCT
 n.Caption AS [Node Name]
	,n.DetailsUrl AS [_LinkFor_Node Name]
	,CONCAT( '/Orion/images/StatusIcons/Small-', n.StatusIcon) AS [_IconFor_Node Name]
	,hib.Model
	,hi.Name AS [Sensor Name]
	,hi.OriginalStatus AS [Status]
	,CASE WHEN n.Status = 1 THEN '/Orion/images/StatusIcons/Small-Up.gif'
 WHEN n.Status = 2 THEN '/Orion/images/StatusIcons/Small-Down.gif'
 WHEN n.Status = 3 THEN '/Orion/images/StatusIcons/Small-Warning.gif'
 WHEN n.Status = 14 THEN '/Orion/images/StatusIcons/Small-Critical.gif'
 WHEN n.Status = 9 THEN '/Orion/images/StatusIcons/Small-Unmanaged.gif'
 ELSE '/Orion/images/StatusIcons/Small-Unknown.gif'
 END AS [_IconFor_Status]
	,hi.Value
FROM Orion.HardwareHealth.HardwareItem AS hi
INNER JOIN Orion.Nodes AS n ON n.nodeID = hi.nodeID
INNER JOIN Orion.HardwareHealth.HardwareInfoBase AS hib ON hib.ParentObjectID = hi.NodeID
INNER JOIN Orion.HardwareHealth.HardwareCategoryStatus AS hcs ON hcs.HardwareInfoID = hi.HardwareInfoID
WHERE ( n.vendor='Cisco'
	AND hi.Status > 1
	AND hi.HardwareCategoryID = 4
	AND hcs.HardwareCategoryName = 'Temperature'
	AND hi.UniqueName NOT LIKE 'HardwareSensor%' )
ORDER BY hi.Value 
 Additionally you can query all the status used within SolarWinds Orion with SWQL with this query: 
 SELECT
 si.StatusID
 ,si.StatusName
 ,si.IconPostfix
FROM Orion.StatusInfo AS si

Custom SWQL Query for Temperature

Top Replies