Influxdb subquery to count top X

1

I’m exited to have subqueries in InfluxQL now so I started exploring but encountered a problem.
I want to use a subquery to get the top X of a measurement Visits by Country

When I query my measurement:

select count("Visit_Count") as daily_visit from "Visits by Channel" where time > now() - 24h group by country

I get an result like this:

name: Visits by Channel
tags: country=Vietnam
time                daily_visit
----                -----------
1491501463914120735 14

name: Visits by Channel
tags: country=Zambia
time                daily_visit
----                -----------
1491501463914120735 15

That looks good, the country is a tag and I thought I should be able to use that in a subquery to get the top visits by country:

select country,top(daily_visit,10) as visits from (select count("Visit_Count") as daily_visit from "Visits by Channel" where time > now() - 24h group by country)

but that gives me just an empty country column:

time                country visits
----                ------- ------
1491501689195632928         58
1491501689195632928         31
1491501689195632928         27
1491501689195632928         25
1491501689195632928         23
1491501689195632928         22
1491501689195632928         22
1491501689195632928         21
1491501689195632928         21
1491501689195632928         21

Seems I’m missing something? Is the tag (country) the problem?

2

I’m having the exact same issue. I did a top process sum, and once I try to select the tag I grouped by, I’m unable to get anything back.

For example:
select top(IOReads,5),process_name from (select sum(IOReadBytesPerSec) as IOReads FROM "top_processes" WHERE time > now() - 1h GROUP BY "process_name" fill(null) )

The process_name is empty when selected

3

If you perform a Group by “process_name” under the top query; the output should show you the process_name under the group tag.

tags: process_name=influx
time IOReads

1491501463914120735 15

4

Mind giving an example as I did try that but still had no results for the grouped field

5

Try the below:
select top(IOReads,5) from (select sum(IOReadBytesPerSec) as IOReads FROM “top_processes” WHERE time > now() - 1h GROUP BY “process_name” fill(null) ) WHERE time > now() - 1h GROUP BY “process_name”

6

Getting close! This did help provide back the group name finally. However, the results are not limited to the top 5. Maybe I’m misunderstanding, as the time grouping may be changing the behavior?

If I want to only provide back the top (x) after doing the subquery, shouldn’t I be getting back only 5 tagged groups?

7

I think you are seeing TOP 5 values for each “process_name” but you are looking for single “Max” value for each process_name?

Instead of using TOP try using Max

select Max(IOReads) from (select sum(IOReadBytesPerSec) as IOReads FROM “top_processes” WHERE time > now() - 1h GROUP BY “process_name” fill(null) ) WHERE time > now() - 1h GROUP BY “process_name”

8

I’m trying to take the sum of all IO during the period I mention, group by process name, and then select the 5 highest read/write type processes to show which processes are generating the highest load. Does that make sense?

9

So when you run it with top using multi grouping options as performed earlier. The response resulted in showing top 5 entries for each “process_name”. Based on your need, use the below query:

select top(IOReads,process_name,5) from (select sum(IOReadBytesPerSec) as IOReads FROM “top_processes” WHERE time > now() - 1h GROUP BY “process_name” fill(null) )

1 Like
10

So I had no luck with the statement above returning multiple groupings by name.
When I included an additional group by statement at the end, it “SEEMS” to be working.

Does this syntax make sense to you? And thank you again for the help.

select top(PercentProcessorTime,process_name,5) from (select sum("PercentProcessorTime") as "PercentProcessorTime" FROM "top_processes" WHERE $timeFilter GROUP BY time($reqinterval), "process_name" fill(null))

image
image1901×582

select top(PercentProcessorTime,process_name,5) from (select sum("PercentProcessorTime") as "PercentProcessorTime" FROM "top_processes" WHERE $timeFilter GROUP BY time($reqinterval), "process_name" fill(null)) WHERE $timeFilter GROUP BY time($reqinterval), "process_name"

image
image1880×567

Also friendly reminder for anyone who reads this later on… make sure to update InfluxDb. I had missed an update and the version prior had a panic crash bug with top aggregate functions. Updating fixed, it’s just confusion on the logic I’m having :slight_smile:

11

Great! I wonder if you execute these queries from command line “influx”; Do they show the same result or different result? Running a similar query on my test environment gave the expected output.

Group on the “TOP” query will produce “grouping” on each “process_name”. So you are looking at top 5 entries for each “process_name”.

12

When running in Chronograph, or Grafana nothing.

This query:

select top(PercentProcessorTime,process_name,5) from (select sum("PercentProcessorTime") as "PercentProcessorTime" FROM "top_processes" WHERE time > now() - 2h GROUP BY time(5s), "process_name" fill(null))

image
image.png803×237 5.85 KB

the other grouping provides a much longer result set, which might be a timeline reset

1 Like
13

I thought so that the graphical tool isn’t able to process the request; since top is generally associated with a single column rather than two columns in this case.

Raise a request with Grafana to handle both situations.

14

Chronograf failed to process for me as well in my test fyi. Not sure if you tested in there

15

Unfortunately I don’t use anything else other than Influxdb - I just use the standard client library to build custom graphs. I ran into graphical tool limitations so I built what was needed.