Influxdb: [Feature Request] Make regex work on field keys in SELECT clause

[thunderstumpges]

As an extension of (or at least related to issue #2715, per @jsternberg) It would be wonderful if the field key (name) in the SELECT clause supported regex as is available for measurements in the FROM clause and tags in the WHERE clause. This is also mentioned on the google user groups here.

The reason this is "all of a sudden" very important is now with 0.10.0 of Telegraf (and InfluxDb) there is a shift from many measurements with one value, to one measurement with many values.

A great example of this (though this same thing is EVERYWHERE now) is the change to telegraf's CPU plugin. Prior to telegraf 0.10.0, our query for our CPU graph was simply:

SELECT mean("value") FROM /cpu_usage_.*/ WHERE ...

And Grafana creates a nice graph with series for each of 'user', 'system', 'iowait', etc. The series alias can be created from the measurement name, and it all just works.

Now that we've upgraded to telegraf 0.10.0 we have had to add every one individually, and alias every one individually, so the query now looks like this:

SELECT mean("usage_guest") AS "guest", mean("usage_iowait") AS "iowait", mean("usage_irq") AS "irq", mean("usage_nice") AS "nice", mean("usage_softirq") AS "softirq", mean("usage_system") AS "system", mean("usage_user") AS "user" FROM "cpu" WHERE

I would REALLY like not to have to go through this tedious effort for so many of our dashboards (it applies to all sorts of things, from GC counters, to Heap, to System Memory, to Disk Usage)

We should be able to write the above query like this:

SELECT mean("/usage_.*/") FROM "cpu" WHERE ...

Thanks!

[pauldix]

I would update to not have quotes around the regex:

SELECT mean(/usage_.*/) FROM cpu ...

I think the behavior on this should be that it returns a series for each field, just like when you have a regex against a tag. In that output format it'll be consumable by Grafana.

[jsternberg]

@pauldix the syntax is a bit confusing for me. So if the following fields exist: cpu_idle, cpu_user

If you do SELECT mean(/cpu_.*/) FROM cpu should it expand to SELECT mean(cpu_idle), mean(cpu_user) FROM cpu? The syntax seems to hint that it would return the mean of all of those fields together to me (which we don't currently have a syntax for).

[thunderstumpges]

grafana seems to put double quotes around field names as far as I can see. Not that I care one way or the other as long as it works (preferably without using the advanced/manual query editor) :)

[noose]

I have similar problem. I want to use regex on fields:

> select * from exceptions where time > now() - 4h limit 5
name: exceptions
----------------
time            code    env     url                                     memory   time
1458702354672049920 404 prod    http://78.46.xxx.yyy/CHANGELOG.txt      1000      0.1
1458702366911688960 404 prod    http://78.46.xxx.yyy/readme.html        987       0.3
1458706626875323136 404 prod    http://78.46.xxx.yyy/status?full=true   433       0.1 
1458706634679529984 404 prod    http://78.46.xxx.yyy/jmx-console        4003      0.5
1458706639527687936 404 prod    http://78.46.xxx.yyy/manager/html       5023      0.3

> select * from exceptions where time > now() - 4h and url =~ /.*78.*/
> select * from exceptions where time > now() - 4h and url =~ /.*readme.*/

How can I do that? I want to get memory / time for some urls (single domain or sample path with all files inside etc)

[sslupsky]

+1

[Will-Beninger]

Also interested in this.

I have a scenario where we have fields named read_[0-30] and would really love to pull all with something like the following: (Some of you may think this is an insane use case but blame Lustre...)

select sum(/^read_[0-9]+$/) from db.autogen.table

EXPANDED:

select sum(read_0), sum(read_1), sum(read_2), ... from db.autogen.table

[jsternberg]

We now support this with wildcards so it should be easy enough to support this with regexes in a future version since the foundation is there.

[vvuk]

We now support this with wildcards so it should be easy enough to support this with regexes in a future version since the foundation is there.

Can you say more about "now"? (which version of grafana, or perhaps influxdb, etc?) I'm trying with Grafana 3.1.0, and field(_), field(usage__), field("usage_*"), etc. all return no values. But maybe I'm doing something wrong overall -- the full query is:

SELECT mean("usage_*") FROM "cpu" WHERE $timeFilter GROUP BY time($interval) fill(null)

[jsternberg]

Ah, that is my mistake for the unclear language. We do not support this exact feature. I should have said:

We now support something similar to this with wildcards ...

Wildcards can only exist for expanding all of the fields rather than a subset. We still need to implement regex support.

[vvuk]

Ah, makes sense, but I can't make it work with all fields either -- unless that came with Grafana newer than 3.1? (that is, "*" or just bare * all return errors).

Under the hood, it looks like InfluxDB doesn't really support any of this for fields, so any expansion would have to come from Grafana.

[jsternberg]

I'm not sure if Grafana supports it, but it was just implemented in InfluxDB very recently. I think you need to run 1.0.0-beta3 and do mean(*). No quotes.

[seb-koch]

+1 awaiting this feature too, we've got null values in fields and want to query for these fields.

[erSitzt]

I'm using grafana templating with influxdb to select the fields to be displayed and grafana creates this query if i select multiple values

SELECT "/^{loadtime,resizetime,totaltime}$/" FROM "portal/imageproxy/master"

It would be nice if this would work.
I'm currently looking into how grafana is building the queries and maybe change it, but still it would be nice if influxdb supported this.

regards
Dennis

[mentaloaf]

+1 awaiting this feature as well to match new fields as they are dynamically added to schema.

[Electronickss]

This would be nice as it would be a workaround for some issues with time operators. You could build a subquery that would regex timestamps for certain values

[pauldix]

@Electronickss this has already been implemented. For instance if you're using telegraf you can do:

select count(/.*/) from cpu where time > now() - 1h
select /.*/ from cpu limit 1

[alex-lmi]

How can I use the matches of the regex to use it in the query? Use case would be to be
select max(/^foo_bar_long_field_([0-9]{3})$/ as $match[1] from foo
Would that require another feature request or is this actually possible right now?

[debu99]

how can use not match