Regex in where clause

InfluxDB 2
1

Hi,
can you please help me to trim the port number in a variable and use it in the where clause of a query

myvariable=“180.180.180.180:8080”

select * from mytable where host_ip = “180.180.180.180”

Basically i want to apply a regex at new variable or where clause to trim the port number and use in the above query.

could you please tell me how to apply regex. in the influx query

2

Hi @Parthiban_Ramadoss,

You can use regex in InfluxQL like this:

<my_tag> =~ /my_regex/

The regex will depend on how precise you want to match IPs and whatever other conditions you want to match on, however, the piece that matches the port number at the end would be (:\d+)?$

So a possible query would look like this:

SELECT * FROM mytable
WHERE "host_ip" =~ /^(\d{1,3}\.){3}\d{1,3}(:\d+)?$/

You can workshop your regex a bit but a tool I like to use for that is https://regex101.com/.

Be aware though that if your use case has high cardinality (lots of tag/value combinations), using regex can result in a significant performance hit.

3

Hello @Parthiban_Ramadoss,
Welcome back. Are you using influxql or sql? I’m confused because you tagged this question v2.

Here’s the docs for influxql and using regex:
Regular expressions | InfluxDB Cloud Serverless Documentation.

or in sql:

SELECT *
FROM "cpu"
WHERE
time >= now() - interval '1 hour' AND
"cpu" ~ 'cpu*'

For you it would be "host_ip" = '180.180.180.180*'

4

Thanks @DanCamp & @Anaisdg, and apollogise for late reply.

I am using influxql, To be more precise with my query.

I already have a variable with ip and port number. (full_address=“180.180.180.180:8080”)

Want to trim the port number alone in the above variable and use it in the following query.

ip_address = trim port number(full_address)

select * from mytable where host_ip = <ip_address>

do we have any functionality or process like “substring” to trim the port number here? or how can we apply regex on the above variable “full_address” to trim the port number and use in the query?