Query with regex for measurement returns from only one measurement

influxql
#1

I’m having trouble with a query that uses a regex to specify the measurement. In a database with about a dozen measurements, when I run:

SELECT last(value) FROM alerta.autogen./Heart.*/ WHERE time > now() - 7d group by resource

it works as expected. There are two measurements that start with ‘Heart’, and I get a result from each for each value of the tag ‘resource’ and from each of those two measurements.

However, when I try to expand this to cover what I think should be every measurement in the database with:

SELECT last(value) FROM alerta.autogen./.*/ WHERE time > now() - 7d group by resource

I get results from only a single measurement (grouped by resource). This happens to be the alphabetically last measurement in the database, not sure if that is significant.

This is version 1.2.2 using a CLI query. Sorry if I’m doing something obviously wrong.

#2

@rwa I tried the following:

> insert Heart.foo,resource=bar value=1
> insert Heart.bar,resource=foo value=1
> insert Beats.foo,resource=bar value=1
> select last(value) from /.*/  group by resource
name: Beats.foo
tags: resource=bar
time				last
----				----
2017-05-15T16:22:04.828790408Z	1

name: Heart.bar
tags: resource=foo
time				last
----				----
2017-05-15T16:21:49.109496891Z	1

name: Heart.foo
tags: resource=bar
time				last
----				----
2017-05-15T16:21:40.039106847Z	1

So it seems to be something specific to your database. Have you tried fully quoting the db.rp like so: "alerta"."autogen"./.*/?

#3

When I couldn’t get this to work I modified the way alerta sends alerts to InfluxDB to put them all in one measurement with the alert name as a tag (vs. multiple measurements w/each alert name its own measurement). That gave me some other benefits, so I sort of let this go and then deleted the excess data that demonstrated this.

I’ve turned the old method back on, but the issue hasn’t reappeared. I don’t think I have all the cardinality I did the last time, so it isn’t a perfect test (and I was thinking the high cardinality might be the issue since the restricted query worked).

So I don’t know if it was just the measurements I had before and not the new ones, if it was the cardinality, or if I just was confused. I did copy and paste the queries, so I would hope I got it correct, but you never know.

I’ll leave the parallel data flow in place for a while to try to build up some cardinality and post back if I can duplicate it. Thanks for the reply.

#4

This is definitely the recommend schema.

#5

It’s certainly much easier to use which is why I switched it. The alerta-contrib InfluxDB plugin uses one measurement per alert name, so I think I’ll submit a pull request to at least give people the option.

#6

@rwa Thank you! Please do! If you drop it here I’ll give you a +1 and a good word too.

#7

I did this here:

You can still have the old behavior, but single measurement is an option too. You can do both as well.