Influxql where regular expression


#1

Helllo!
i want use regular expression in “WHERE clause TAG KEY” for example
SELECT * FROM measurement WHERE /^(tagkey1|tagkey2|tagkey3|tagkey4|tagkey5)$/ =~ /^(1|2|3|4|5)$/ limit 1
it is possible?


#2

Yeah you can

Just be careful if you have high cardinality, regex might not be too performant. I used something similar to omit irrellavent disk drives from the query.

You can also set up a template variable in grafana and use something like

SHOW TAG VALUES WITH KEY = “host” - which will show you all the hosts in the datasource. you can use it in the grafana dashboard queries also to visualise the data if you wanted. Then you can update/refresh them without editing the queries each time.

In short, yes regex works in queries. Hope it helps


#3

thank you for reply!
i using variable in grafana it is used by some customer, so i have to setting variable for make they do not have to modify metric because they don’t know query,
so i use Variable it is working but Variable(turn on Multy-value option) is not working in "left Value of “Where-Clause” like that img
image
influx cli print error like this

so i wonder regex not working LeftValue of Where-Clause in influxdb
if it is not working, i try another way

(ps. i try adc hoc filter in grafana it is good but adc hoc filter not supported change databases or measurement)


#4

Hi,

I believe example 4,5,6,7 on the documentation should help. https://docs.influxdata.com/influxdb/v1.2/query_language/data_exploration/#regular-expressions

At a guess though, counting the characters in the query it looks like it might be the ‘=’ in your query. Try changing it from

where /PM/ = ‘1’

to this: where /PM/ =~ ‘1’

It could be that you need to pass the regex equivillent of ‘equals to’ or ‘not equals to’ into the query.

Let me know if that works, if not i have a script somewhere that uses regex to omit specific hard drives from my alerts. I’ll try and find it.


#5

thank you!
I tried it on. but still not solve my problem
this is my error in _internalDB


#6

HI,

I don’t think that will work in the CLI. Perhaps Grafana.

If you queried select * from cq where “query” =~ /1/ that would work, if “query” was a field or tag. I’m guessing you are trying to use /query/ to bring results based on queryFail and queryOk. where the result is greater than 0? I’m not sure that will work so i don’t know what else to suggest. I tried your query and escaping the slashes but it doesn’t like that.

A query like this will work
select * from cq where “hostname” =~ /Influx/

That would bring any results back from cq where the host name matched all or part. As for regexing the tags/fields like that i don’t know if you can. I can’t find anyting in the documentation to suggest otherwise.

Sorry!


#7

I 'll find another way. Thank you for helping me !