I was hoping someone could help me with a SWQL query related to SRM Performance Data. What I ultimately want are Storage Latency Stats for the previous month showing me Max values. The query below is pretty much what I want except I want to know the day and time that the Max latency occurred in the previous month. The trouble is when I try to Select L.Statistics.ObservationTimeStamp I get an error about ObservationTimeStamp is invalid because it is not contained in either an aggregate function or in the Group By clause. If I add it to Group By the returned values are no longer the Max Latency. I know I'm missing something fundamental here; what am I doing wrong?
SELECT L.StorageArray.Caption AS [Storage Array], L.Caption AS [LUN], MAX(L.Statistics.IOLatencyTotal) AS [Peak IO Latency Total]
FROM Orion.SRM.LUNs L
Where L.StorageArray.CustomProperties.Department LIKE 'Department of Information Technology' AND L.Statistics.ObservationTimestamp BETWEEN DATETRUNC('month', AddDate('month', -1, GETDATE())) AND DATETRUNC('month', GETDATE())
GROUP BY L.Caption, L.StorageArray.Caption
ORDER BY [Peak IO Latency Total] DESC
