Device Temperature

CourtesyIT

All,  I am looking for some SWAL/SQL code that can pull out the following information from the database. 

Columns Include: 

Device Name

IP Address

Make

Model

Serial Number

Temperature Fahrenheit and Celsius

Seashore

Changed one line and got some more temperature values (not all temp sensors are called temp sensors....):

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]
,T.AVG_Node_Temp_Farenheit AS [AVG Temp in F]

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

The other once though.... Hard to troubleshoot how your devices look like. Mine that don't have values in the query don't have that in orion at all.