Influxdb: Wire up SORDER

[pauldix]

SORDER is a way to order the series returned in a result. This is useful if you're going to return the top or bottom series by some function. For example:

SELECT mean(value)
FROM cpu
WHERE time > now() - 24h
GROUP BY time(1h), host
SORDER max(value) 5m desc
SLIMIT 5

This query says to take the most recent 5m period in the query's time range and compute the max for every host. It would then order those in descending order and then take the first 5 and do queries for them to get their series.

[srikara]

+1

[will-prcm]

+1

[zapient]

+1

[Kaelten]

+1

[vlitvin]

+1

[nordewal]

that would be a really useful feature. is there working going on in regards to this?

[tbriggs-curse]

+1

[Kaelten]

Please reopen and schedule. This feature would make influxdb much more useful.

[andycarlsonuk]

+1

I'm now using InfluxDB and Grafana to monitor a 36 node cluster. The previous project had nine nodes. The graph legends become too big and most of the series on the graph are of no real interest. I want to find the things that need action by looking at, say, the worst 5 or 10 nodes but I can't find a way with InfluxDB. A shame because it's really easy with ElasticSearch/Kibana but otherwise Grafana+InfluxDB is a much better fit for my needs. SORDER with SLIMIT seems to be exactly what I need.

Rather a shame to see this issue closed 16 hours before I found it.

[nordewal]

We have about the same use-case and problem as @andycarlsonuk. This feature would make InfluxDB actually much more helpful!

[jwilder]

Thanks for commenting. We'll keep this open, but it's not currently scheduled for any release yet.

[Kaelten]

That's exactly my use case as well. We're monitoring hundreds of servers and storing metrics in InfluxDB. Not having a SORDER operator has been a major limiting factor on how useful our graphing is.

It's too the point where when I have down time I'm going to see if I can figure out how to implement it myself

Thank you for reopening this.

[bimalm]

+1

[clheikes]

I'm looking for an influxdb equivalent to graphite's highestAverage. Other posts have mentioned this but they eventually link to #5930 which contains "Top accepts nested functions", but from what i've seen the top function doesn't return a series.

This looks like the feature request i need to follow. Can anybody confirm?

[vlitvin]

@clheikes Yeah, totally, all we need from SORDER is to do something like "SORDER BY mean(bytes) DESC LIMIT 10", which would be full analog of highestAverage. But, further, it allows all sorts of series sorting.

from what i've seen the top function doesn't return a series.

Yep, so I have custom script to achieve this (metrics are assigned back to series), but it leads to null values in series and more series returned, for example top(10) would lead to ~30 series. Of course number of series depends on data.

[mhiller]

@clheikes this is a pretty big missing functionality and from reading various threads people dont seem to under that the "fix" they are pushing is not actually what the want. they needs a time series of "top".

@vlitvin Could you share an example of your script in a Gist ? it would be helpful.

[white-hat]

Without SORDER we can't switch from Prometheus to influxdb enterprise, please add this feature

[pauldix]

@white-hat can you give me a little more detail about what you're trying to do with the query?

[Kaelten]

@pauldix in my use case SLIMIT is kinda useless without SORDER. I can graph a line per server for example but I can't say 'show me the worst 10'. Makes a lot of dashboarding much less useful

[pauldix]

@Kaelten the worst 10 based on what criteria? For example, look at the most recent cpu system utilization for every server and take the highest 10 servers, then query them for some period of time? Is it important to say worst over some longer period or the entire period for what is returned?

[Kaelten]

@pauldix without SORDER I get a random 10 series. With SORDER I should be able to say 'order by avg cpu utilization' or 'max 15 min load' or other similar statements. Right now if I query and get back 30 series and SLIMIT it to 10 I have no idea if I'm being the most useful 10.

In our case we have ~ 900 servers monitored right now and some charts have too many series on them to be even remotely useful or actionable. SLIMIT with SORDER would make these charts significantly more useful.

[LionelCons]

We really miss the following Graphite functions that limit the number of series returned based on some criteria: highestAverage, highestCurrent, highestMax, lowestAverage, lowestCurrent as well as mostDeviant.

All these Graphite functions order the series (aka SORDER) before limiting the number of series returned (aka SLIMIT).

[elvishsu66]

It would be really nice to have SORDER feature unless someone can come up an alternative solution

[Deriul]

I agree. The lack of that feature is the only reason why we have to use graphite+influx.

[PaulieShore]

Another +1 here. I recently switched from Graphite to InfluxDB. I rely on functions like highestAverage, highestCurrent, and highestMax for our dashboard as we then limit the results in Grafana using a a template for "Top Resources". With Graphite this is incredibly simple to achieve.

[aurimasplu]

+1
@rbetts you mean this will not be developed?

[dolvany]

This is absolutely the most glaring feature omission in influxdb.

[rbetts]

This feature will be delivered through IFQL. You can read the ifql announcement and a more recent update. We also blog each completed ifql iteration as we continue early development. You can follow the ifql repository to track our progress.

We aren't far enough along yet to publicize a release date for IFQL 1.0; however, we are doing all of the development in the open and welcome your feedback and thoughts. We package the query processor in the influxdata sandbox, if you'd like to install and try it in its current state.

We appreciate your feedback about desired query functionality. We read and listen to all of your comments even when we can't always reply promptly.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[clheikes]

@nathanielc
Is there a working example of the functionality described here in flux?

[marrotte]

Is the original issue solvable with InfluxSQl or IFLQ, yet? Is there a way to order series on an aggregate or selector?