Query with regex for measurement returns from only one measurement

rwa · May 10, 2017, 1:48pm

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.

jackzampolin · May 15, 2017, 4:23pm

@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"./.*/?

rwa · May 17, 2017, 8:44pm

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.

jackzampolin · May 18, 2017, 5:51pm

This is definitely the recommend schema.

rwa · May 18, 2017, 7:22pm

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.

jackzampolin · May 18, 2017, 10:39pm

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

rwa · May 19, 2017, 2:36pm

I did this here:

github.com/alerta/alerta-contrib

Allow InfluxDB plugin to use a single measurement

alerta:master ← thehilll:influxdb-single-measurement

opened 02:31PM - 19 May 17 UTC

thehilll

+41 -16

I wanted to provide an option to output to a single InfluxDB measurement rather …than one per event The default behavior is to create one measurement per alert.event in InfluxDB. Some uses of InfluxDB are easier if all Alerta data is available in a single measurement with a tag that indicates alert.event. This introduces two new config variables: INFLUXDB_SEPARATE_MEASUREMENT defaults to True and triggers the default (separate measurement per event) behavior. INFLUXDB_SINGLE_MEASUREMENT defaults to False and triggers the new behavior of one measurement for all events. If set it should be set to the name of the measurement you want to use (e.g. not True but rather "alert_measurement"). Both behaviors can be either enabled or disabled (i.e. it isn't one or the other behavior...you can have both). If neither variable is set (for example if someone installs this code but does not change the config file) the behavior remains at the old default (one measurement per alert name). The InfluxDB points now start as an empty list (line 46). If either of the above config variables are not False a point is added to the list in the format appropriate for that setting. In the case of INFLUXDB_SEPARATE_MEASUREMENT (default behavior) this is unchanged from the original behavior and schema. In the case of INFLUXDB_SINGLE_MEASUREMENT (new behavior) this point uses the value set in INFLUXDB_SINGLE_MEASUREMENT as a measurement name and adds a new tag called "event" which is populated with the alert.event value. I've made one other change to each point type which is to coerce the value field (alert.value in both cases) to a string. The issue we ran into is that InfluxDB defines a field's type based on the first point. If the first point is a string the old code worked fine (subsequent numeric values were treated as strings), but if the first point was numeric InfluxDB would drop subsequent points that included string values. Since Alerta allows string values (and has to it would seem) I've forced this field to be a string in InfluxDB. Finally within the final try block to write points to InfluxDB I changed this to only do so if the points list is not empty. This handles the case where both configuration variables are false and nothing is written. One thing to consider is the behavior of the INFLUXDB_SINGLE_MEASUREMENT variable. I've made it so that if set to False the new behavior is skipped, but if you want to enable the new behavior you set it to the value of the new measurement you want to use (so the valid values are False and `<name of a measurement>`). That's not the most obvious thing, but I did it to minimize the number of settings you need. Another option would be to have something along the lines of: INFLUXDB_SINGLE_MEASUREMENT: True or False INFLUXDB_SINGLE_MEASUREMENT_NAME: string where the second new option is only used if the first is True and defines the measurement name.

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

Topic		Replies	Views
Using regular expressions (regex) in continuous query (and standard queries) Telegraf query , regex	1	2123	April 5, 2020
Example Query to monitor incoming data regardless of Measurement InfluxQL influxdb , query	1	129	March 11, 2024
SELECT multiple measurements with GROUP BY InfluxDB 2 influxdb	0	419	February 10, 2021
InfluxQL exclude measurements through regex Dashboards grafana , influxql	1	1669	February 11, 2021
Having trouble with influxdb subqueries influxdb	1	1538	November 2, 2017

Query with regex for measurement returns from only one measurement

Related topics