Combining Data from 2 Tables

Mike_Lomax

I want to combine data from 2 reports into the same query result displayed in a Report Table.

One data set is from a UPS temperature sensor and is collected using SNMP within a SAM Application Monitor.  I am able to get the data I want using:

SELECT TOP 1000 hivs.ObservationTimestamp AS Timestamp, hivs.HardwareItemID AS ItemID, hib.Name AS Name, ((hivs.AvgValue*9)/5)+32 AS AvgFValue
FROM Orion.HardwareHealth.HardwareItemValueStatistics hivs
JOIN Orion.HardwareHealth.HardwareItemBase hib ON hib.ID = hivs.HardwareItemID
WHERE hivs.HardwareItemID = 23269 AND hivs.ObservationTimestamp > ADDDAY(-14, GETUTCDATE())
ORDER BY Timestamp DESC

The second data set is using HWH from a server temperature sensor.  I am able to get the data I want using:

SELECT TOP 1000 ce2.TimeStamp AS Timestamp, ce2.ComponentID AS ItemID, comp.Name AS Name, ce2.AvgStatisticData AS AvgFValueFROM Orion.APM.ChartEvidence2 ce2 JOIN Orion.APM.Component comp ON comp.ComponentID = ce2.ComponentIDWHERE ce2.ComponentID = 16308 AND ce2.TimeStamp > ADDDAY(-14, GETUTCDATE())ORDER BY Timestamp DESC

Combining the two queries with a Union, as follows, does not give me the result I am wanting:

SELECT TOP 1000 hivs.ObservationTimestamp AS Timestamp, hivs.HardwareItemID AS ItemID, hib.Name AS Name, ((hivs.AvgValue*9)/5)+32 AS AvgFValueFROM Orion.HardwareHealth.HardwareItemValueStatistics hivsJOIN Orion.HardwareHealth.HardwareItemBase hib ON hib.ID = hivs.HardwareItemIDWHERE hivs.HardwareItemID = 23269 AND hivs.ObservationTimestamp > ADDDAY(-14, GETDATE())UNION(SELECT TOP 1000 ce2.TimeStamp AS Timestamp, ce2.ComponentID AS ItemID, comp.Name AS Name, ce2.AvgStatisticData AS AvgFValueFROM Orion.APM.ChartEvidence2 ce2 JOIN Orion.APM.Component comp ON comp.ComponentID = ce2.ComponentIDWHERE ce2.ComponentID = 16308 AND ce2.TimeStamp > ADDDAY(-14, GETDATE()))ORDER BY Timestamp DESC

... and I do know why.  Basically this is collecting the top 1000 rows from each select statement, which is the oldest rows.  But only a few days are part of the result without having the ORDER BY Timestamp DESC associated with each select statement.

I am no SQL or SWQL expert.  Can someone help me with redrafting the query to get the result I am looking for?

I have successfully been able to take the above unioned query, minus `ORDER BY Timestamp DESC` and create a report.  With `ORDER BY Timestamp DESC` I get "Query is not valid."

antonis.athanasiou

I've tried something similar on SWQL Studio to simulate and it seems there's an issue with sorting by the datetime fields? I was able to successfully order by other columns (NodeID, statistics, etc.) but not any of the datetime fields. How are you trying to get the report down? using the web reporting engine -> custom SWQL code? In that case you can omit the ORDER BY clause and skip straight to the table properties and sort by the datetime column over there:

dan jagnow​ seems an issue with the way datetime fields are parsed? see below: