I’m use the next query to get the average of memory used, but only need get data when average is more than x value
SELECT AVG(system.memory.actual.used.pct) AS averageMemory from "metricbeat*" where host.name.keyword='data.storage' HAVING AVG(system.memory.actual.used.pct)>0.3 and "@timestamp" BETWEEN '2021-10-01T00:00:00.000Z' and '2021-10-15T00:00:00.000Z'
And show me the next error
Found 1 problemnline 1:152: Cannot use HAVING filter on non-aggregate [@timestamp]; use WHERE instead”
And when use WHERE clause, like this
SELECT AVG(system.memory.actual.used.pct) AS averageMemory from "metricbeat*" where host.name.keyword='data.storage' and averageMemory>0.3 and "@timestamp" BETWEEN '2021-10-01T00:00:00.000Z' and '2021-10-15T00:00:00.000Z'
Show the next error
Found 1 problemnline 1:8: Cannot use WHERE filtering on aggregate function [AVG(system.memory.actual.used.pct)], use HAVING instead
Im confused. Any idea?
Advertisement
Answer
HAVING AVG(system.memory.actual.used.pct)>0.3 is correct, as HAVING expresses a constraint on aggregated data, and WHERE ... averageMemory>0.3 is not correct, as WHERE expresses a constraint at record level.
For the same reason, HAVING @timestamp BETWEEN '2021-10-01T00:00:00.000Z' and '2021-10-15T00:00:00.000Z' is wrong, since @timestamp is not an aggregated value but a record level attribute.
So WHERE ... and @timestamp BETWEEN '2021-10-01T00:00:00.000Z HAVING AVG(system.memory.actual.used.pct)>0.3 should be the correct way.