When running the following query, if there are no match to the first CASE statement, all goes well:
SELECT TOP 100 N.Caption AS [Device name],
N.IP_Address,
N.MachineType,
N.Vendor,
N.HardwareHealthInfos.ServiceTag AS [Serial Number],
T.AVG_Node_Temp_Celcius AS [AVG Temp in C_plain],
T.AVG_Node_Temp_Farenheit AS [AVG Temp in F_plain],
CASE
WHEN T.AVG_Node_Temp_Celcius > 50
THEN CONCAT (
'<font color="red"><b>',
T.AVG_Node_Temp_Celcius,
'</font></b>'
)
ELSE T.AVG_Node_Temp_Celcius
END AS [AVG Temp in C_html]
FROM Orion.Nodes AS N
LEFT OUTER JOIN (
SELECT NodeID,
CASE
WHEN H.HardwareUnit.Name = 'DegreesC'
THEN ROUND(AVG(H.Value), 0)
WHEN H.HardwareUnit.Name = 'DegreesF'
THEN ROUND(((AVG(H.Value) - 32) * 5 / 9), 0)
END AS [AVG_Node_Temp_Celcius],
CASE
WHEN H.HardwareUnit.Name = 'DegreesC'
THEN ROUND(((AVG(H.Value) * 9) / 5 + 32), 0)
WHEN H.HardwareUnit.Name = 'DegreesF'
THEN ROUND(AVG(H.Value), 0)
END AS [AVG_Node_Temp_Farenheit]
FROM Orion.HardwareHealth.HardwareItem AS H
WHERE H.HardwareUnit.Name IN (
'DegreesF',
'DegreesC'
)
AND H.IsDeleted = 'FALSE'
AND H.Value IS NOT NULL
GROUP BY H.NodeID,
H.HardwareUnit.Name
) AS T ON N.Nodeid = T.Nodeid
WHERE N.Vendor = 'Cisco'
I receive 59 rows in my query in less than a second.
If I am lowering WHEN T.AVG_Node_Temp_Celcius > 50 to something like 30 in order to make sure it matches something in my environment, the query runs, and timeout after 2 minutes:
I know that the CONCAT statement in my case is fine. To verify it, I replaced the whole CASE statement by CONCAT ('<font color="red"><b>', T.AVG_Node_Temp_Celcius,'</font></b>') AS [AVG Temp in C_html] and I got the expected results:
While trying to pimp the solution to https://thwack.solarwinds.com/product-forums/the-orion-platform/f/orion-sdk/94568/device-temperature, I ran into an issue.
When running the following query, if there are no match to the first CASE statement, all goes well:
I receive 59 rows in my query in less than a second.
If I am lowering
WHEN T.AVG_Node_Temp_Celcius > 50
to something like 30 in order to make sure it matches something in my environment, the query runs, and timeout after 2 minutes:I know that the CONCAT statement in my case is fine. To verify it, I replaced the whole CASE statement by
CONCAT ('<font color="red"><b>', T.AVG_Node_Temp_Celcius,'</font></b>') AS [AVG Temp in C_html]
and I got the expected results:Shouldnt a CONCAT statement in a CASE work?