Hi, I am new to this space and have been following the documentation and tips shared by fellow community members. I've been working on to get the monthly availability reports for the monitored devices from Solarwinds via API. On one of the posts I came across a suggestion solution with the SWQL query - which works fine on SWQL studio.
SELECT [Nodes].Caption AS Devices , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability] FROM Orion.Nodes AS [Nodes] WHERE [Nodes].Category = '1' -- Put whatever filters here -- Start on the first day of the previous month (inclusive) AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= DATETRUNC('month',(ADDMONTH(-1, GETUTCDATE()))) -- End on the first day of this month (non-inclusive) AND [Nodes].ResponseTimeHistory.ObservationTimestamp < DATETRUNC('month', GETUTCDATE()) GROUP BY [Nodes].Caption
"Copy Query As" - with curl(cmd) gave me the below query to be run from Postman which somehow fails with an error code 400 Bad Request with the below message -
"Message": "Column 'dbo.NodesData.Caption' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Below is the API query -
servername:17774/.../Query
query=SELECT+%5bNodes%5d.Caption+AS+Devices+%2c+AVG(%5bNodes%5d.ResponseTimeHistory.Availability)+AS+%5bAvailability%5d+FROM+Orion.Nodes+AS+%5bNodes%5d+
WHERE+%5bNodes%5d.Category+%3d+%271%27+--+Put+whatever+filters+here+--+Start+on+the+first+day+of+the+previous+month+(inclusive)+AND+%5bNodes%5d.ResponseTimeHistory.ObservationTimestamp+%3e%3d+DATETRUNC(%27month%27%2c(ADDMONTH(-1%2c+GETUTCDATE())))+--+End+on+the+first+day+of+this+month+(non-inclusive)
+AND+%5bNodes%5d.ResponseTimeHistory.ObservationTimestamp+%3c+DATETRUNC(%27month%27%2c+GETUTCDATE())+ GROUP+BY+%5bNodes%5d.Caption
Would be helpful if I can get pointers as what is wrong with the queries or how should i be fixing this error. Thanks.
