Elasticsearch SQL in Kibana

Elastic Stack Kibana
1

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.

2

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

3

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.

4

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

5

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.

6

oh then you do group by timestamp

7

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