Custom SWQL Query for Temperature

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.... Disappointed

Parents Reply Children
  • Unfortunately it's not been tested with latest version.. not going to risk it.
    Also when an update hits, this would get overwritten.

    Unless is adopted by SW i don't use it, as been there in the past.

    But thanks

  • No worries - For the record, I'm using it on the latest version and it's still working. But yes it is an un-supported change that I have to replace if/when we rebuild or upgrade so I appreciate your point.

  • Not trying to convince you one way or the other... but I do think it's worth noting that is an old thread and not really necessary to go to that length I don't believe. There's fully supported methods to do what's mentioned in that thread.

    I'm not sure when SolarWinds implemented the ability to "Allow HTML Tags" in columns of reports / custom tables but it's there now in the latest versions.

    A simple example...

    Go to your view, add the "Custom Table" widget, edit it and select a SWQL datasource (could be done in SQL as well).

    Here's an example SWQL query to use:

    SELECT
          CONCAT('<a href="https://localhost"', n.DetailsUrl, '>', n.Caption, '</a>') AS [Node]
         ,n.IP_Address
        ,CASE WHEN n.ResponseTime < 5 THEN CONCAT('<p style="color:blue">', TOSTRING(n.ResponseTime), '</p')
            ELSE CONCAT('<p style="color:red;font-weight:bold">', TOSTRING(n.ResponseTime), '</p') 
         END AS [Latency]
    FROM Orion.Nodes AS n

    Add the columns in and make sure you "Allow HTML Tags" on the columns that have them.

    End result:

    So in the end... multiple ways to skin that cat as they say lol.