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 AvgFValue
FROM Orion.APM.ChartEvidence2 ce2
JOIN Orion.APM.Component comp ON comp.ComponentID = ce2.ComponentID
WHERE 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 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, GETDATE())
UNION
(SELECT TOP 1000 ce2.TimeStamp AS Timestamp, ce2.ComponentID AS ItemID, comp.Name AS Name, ce2.AvgStatisticData AS AvgFValue
FROM Orion.APM.ChartEvidence2 ce2
JOIN Orion.APM.Component comp ON comp.ComponentID = ce2.ComponentID
WHERE 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."
