Elasticsearch SQL in Kibana

I'm trying to do a SUM on a query, and can't seem to get anything to work. Perhaps I'm using the wrong syntax, but I keep getting the error message "Cannot use non-grouped column".

I'm using metricbeat to collect metrics on some systems. The fields I'm interested in are "host.name" and "system.diskio.iostat.read.request.per_sec". I want to sum each host's system.diskio.iostat.read.request.per_sec value to give me a total IOPS across all hosts. The query I'm trying to run is as follows:

"SELECT host.name as hostname, SUM(system.diskio.iostat.read.request.per_sec) as IOPS, "@timestamp" as time FROM "metricbeat-*""

I'm not sure if my syntax is wrong, or if I need to do some grouping by time stamp, or what.

try something like this. I am not an expert in sql

select host.name, sum(xyz) from metricbeat-* group by host.name
grou by this will sum for a host, then second host and so on.

you can't have timestamp there. if you want for particular date then you have to do
select host.name,sum(xyz) from metricbeat-* where date = xyz group by host.name

I don't want the average over a particular day, I want to return values at all times so I can graph it. I just want to aggregate the values from each host at any particular timestamp. It works fine graphing them individually, I just want to sum all the individual hosts together.

but I think group by does same

for example
host1 - 100
host1 - 100
host2 - 200
host2 - 200

then when you do group by you can get
host1 - 200
host2 - 400

But that isn't what I want to do. I want to add host1 and host2 at every timestamp, giving 300 for each timestamp. I want the aggregate IOPS of all hosts at any particular time, and then graph that over time. Basically, I have a cluster that I want to get the total IOPS of the cluster, but each node is reported individually by metricbeat.

oh then you do group by timestamp

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.