Using regular expirations in a query

ahiyaz · September 27, 2020, 8:15am

hi
I’ve started using the httpjson plugin for collecting metrics via APIs.
this plugin creates fields automatically and it includes in the field name each interface name what makes it impossible to use variables in the query.
I’ve tried to do so using regular expirations, but beside /./ that just selected all fields I could really get any results.

more specific details:

im using API to collect the results of the sd-wan performance SLA check from my FortiGate.
it returning values of latency, packet loss, jitter etc. for every wan interface to various destinations.
the response looks like this:
indent preformatted text by 4 spaces
telegraf --config /etc/telegraf/telegraf.conf --test
2020-09-27T06:45:14Z I! Starting Telegraf 1.14.5

httpjson_sd_wan_stats,host=telegraf-upwork,server=https://172.17.200.1/api/v2/monitor/virtual-wan/health-check/select build=268,response_time=0.293239661,results_http\ check_wan1_jitter=10.928666,results_http\ check_wan1_latency=107.960655**,results_http\ check_wan1_packet_loss=0**,results_http\ check_wan1_packet_received=1426838,results_http\ check_wan1_packet_sent=1434961,results_http\ check_wan1_rx_bandwidth=111760,results_http\ check_wan1_session=60,results_http\ check_wan1_state_changed=1600866072,results_http\ check_wan1_tx_bandwidth=71906,results_http\ check_wan2_jitter=9.614668,results_http\ check_wan2_latency=107.431297,results_http\ check_wan2_packet_loss=0,results_http\ check_wan2_packet_received=748680,results_http\ check_wan2_packet_sent=1434961,results_http\ check_wan2_rx_bandwidth=15380,results_http\ check_wan2_session=2,results_http\ check_wan2_state_changed=1601187656,results_http\ check_wan2_tx_bandwidth=4193,results_ping\ check_wan1_jitter=0.0266,results_ping\ check_wan1_latency=39.58223,results_ping\ check_wan1_packet_loss=0,results_ping\ check_wan1_packet_received=497883,results_ping\ check_wan1_packet_sent=501163,results_ping\ check_wan1_rx_bandwidth=111760,results_ping\ check_wan1_session=59,results_ping\ check_wan1_state_changed=1600866072,results_ping\ check_wan1_tx_bandwidth=71906,results_ping\ check_wan2_jitter=0.0275,results_ping\ check_wan2_latency=39.608501,results_ping\ check_wan2_packet_loss=0,results_ping\ check_wan2_packet_received=409617,results_ping\ check_wan2_packet_sent=501163,results_ping\ check_wan2_rx_bandwidth=15380,results_ping\ check_wan2_session=1,results_ping\ check_wan2_state_changed=1601187656,results_ping\ check_wan2_tx_bandwidth=4193 1601189114000000000
indent preformatted text by 4 spaces

ive bolded all relevant packet loss metrics for this example.
i got 4 different valus 2 for each interface(wan1 and 2)
i preform 2 tests ping and http (different destinations)
and i want to pull all 4 in one query.
something like this

indent preformatted text by 4 spaces

SELECT mean(“results_ping check_wan1_packet_loss”) AS “packet_loss(ping check_wan1)”, mean(“results_ping check_wan2_packet_loss”) AS “packet_loss(ping check_wan2)”, mean(“results_http check_wan1_packet_loss”) AS “packet_loss(http check_wan1)”, mean(“results_http check_wan2_packet_loss”) AS “packet_loss(http check_wan2)” FROM “httpjson_sd_wan_stats” WHERE (“host” =~ /^$site$/) AND $timeFilter GROUP BY time($__interval) fill(null)
indent preformatted text by 4 spaces

how could i do that in one line when i know that test will name http or ping
and the interface name could be wan1/wan2/port1-30? is it even possible?

ill appreciate any feedback.

I’m using:
Telegraf 1.14.5
Influxdb 1.8.0-1
Grafana 7.0.5

thanks

ahiyaz · September 28, 2020, 3:57am

hi

can anyone help with this?

thanks

Anaisdg · September 28, 2020, 4:59pm

Hello @ahiyaz,
Calculating mean() across tags or fields that you’ve filtered for is much easier to do in Flux.
For example you can filter for your specific

|>from(bucket: "my-bucket")
|> range(start: -10m)
|> filter(fn: (r) =>
    r._measurement == "httpjson_sd_wan_stats" and
    r._field == "wan1/wan2/port1-30" and
    r.host =~ /^site/
  )
|> mean()

ahiyaz · September 29, 2020, 12:18pm

thanks for responding
but currently, we don’t use flux.
meanwhile, i was able to do so with regex, but with this way it collects all matched fields that it finds in DB, and the “WHERE (“host” =~ /^$site$/)” statement doesn’t work.
the real issue and why I don’t have the flexibility I need in modifying fields name.
it creates the name automatically and includes the check and interface name instead of creating tags of check and interface name.
is there a different plugin for pulling via API?

thanks

API response example:

indent preformatted text by 4 spaces

“http check”: {

        "wan1": {

            "status": "up",

            "latency": 113.979927,

            "jitter": 13.634867,

            "packet_loss": 0.000000,

            "packet_sent": 1624422.000000,

            "packet_received": 1615787.000000,

            "session": 145,

            "tx_bandwidth": 83819,

            "rx_bandwidth": 149249,

            "state_changed": 1600866072

        },

        "wan2": {

            "status": "up",

            "latency": 119.423218,

            "jitter": 15.958531,

            "packet_loss": 0.000000,

            "packet_sent": 1624422.000000,

            "packet_received": 937646.000000,

            "session": 1,

            "tx_bandwidth": 4266,

            "rx_bandwidth": 17982,

            "state_changed": 1601187656

        }

    },

    "ping check": {

        "wan1": {

            "status": "up",

            "latency": 39.590530,

            "jitter": 0.028200,

            "packet_loss": 0.000000,

            "packet_sent": 690623.000000,

            "packet_received": 687325.000000,

            "session": 145,

            "tx_bandwidth": 83819,

            "rx_bandwidth": 149249,

            "state_changed": 1600866072

        },

        "wan2": {

            "status": "up",

            "latency": 39.617825,

            "jitter": 0.031700,

            "packet_loss": 0.000000,

            "packet_sent": 690623.000000,

            "packet_received": 599058.000000,

            "session": 1,

            "tx_bandwidth": 4266,

            "rx_bandwidth": 17982,

            "state_changed": 1601187656

        }

    }

},

indent preformatted text by 4 spaces

the telegraf plugin output:

indent preformatted text by 4 spaces

telegraf --config /etc/telegraf/telegraf.conf --test
2020-09-29T08:53:34Z I! Starting Telegraf 1.14.5

httpjson_sd_wan_stats,host=telegraf-upwork,server=https://172.17.200.1/api/v2/monitor/virtual-wan/health-check/select build=268,response_time=0.29672028,results_dns\ check_wan1_jitter=0.030733,results_dns\ check_wan1_latency=39.649036,results_dns\ check_wan1_packet_loss=0,results_dns\ check_wan1_packet_received=675547,results_dns\ check_wan1_packet_sent=678836,results_dns\ check_wan1_rx_bandwidth=117646,results_dns\ check_wan1_session=89,results_dns\ check_wan1_state_changed=1600866072,results_dns\ check_wan1_tx_bandwidth=70278,results_dns\ check_wan2_jitter=0.0226,results_dns\ check_wan2_latency=39.626728,results_dns\ check_wan2_packet_loss=0,results_dns\ check_wan2_packet_received=587286,results_dns\ check_wan2_packet_sent=678836,results_dns\ check_wan2_rx_bandwidth=15045,results_dns\ check_wan2_session=1,results_dns\ check_wan2_state_changed=1601187656,results_dns\ check_wan2_tx_bandwidth=4193,results_http\ check_wan1_jitter=11.460266,results_http\ check_wan1_latency=108.773949,results_http\ check_wan1_packet_loss=0,results_http\ check_wan1_packet_received=1604028,results_http\ check_wan1_packet_sent=1612613,results_http\ check_wan1_rx_bandwidth=117646,results_http\ check_wan1_session=88,results_http\ check_wan1_state_changed=1600866072,results_http\ check_wan1_tx_bandwidth=70278,results_http\ check_wan2_jitter=14.1043,results_http\ check_wan2_latency=116.506836,results_http\ check_wan2_packet_loss=0,results_http\ check_wan2_packet_received=925882,results_http\ check_wan2_packet_sent=1612613,results_http\ check_wan2_rx_bandwidth=15045,results_http\ check_wan2_session=1,results_http\ check_wan2_state_changed=1601187656,results_http\ check_wan2_tx_bandwidth=4193,results_ping\ check_wan1_jitter=0.033967,results_ping\ check_wan1_latency=39.584064,results_ping\ check_wan1_packet_loss=0,results_ping\ check_wan1_packet_received=675523,results_ping\ check_wan1_packet_sent=678814,results_ping\ check_wan1_rx_bandwidth=117646,results_ping\ check_wan1_session=86,results_ping\ check_wan1_state_changed=1600866072,results_ping\ check_wan1_tx_bandwidth=70278,results_ping\ check_wan2_jitter=0.0269,results_ping\ check_wan2_latency=39.6166,results_ping\ check_wan2_packet_loss=0,results_ping\ check_wan2_packet_received=587255,results_ping\ check_wan2_packet_sent=678814,results_ping\ check_wan2_rx_bandwidth=15045,results_ping\ check_wan2_session=1,results_ping\ check_wan2_state_changed=1601187656,results_ping\ check_wan2_tx_bandwidth=4193 1601369615000000000
root@telegraf-upwork:~# history | grep ES
indent preformatted text by 4 spaces

philjb · September 30, 2020, 7:15pm

@ahiyaz -

Your data is in a difficult structure to make querying easy. I have a few suggestions: (1) You can put regexes into the mean function like SELECT mean(/packet_loss/). This will return a separate mean for each matching field. I think this is what you are looking for. (2) You can list out all the options that you expect for the packet_loss field names. This option is tedious and brittle if a field name changes. (3) You can preprocess the json output before telegraf with a shell script for example.

The http input plugin with the json data format is recommended now as the httpjson plugin is deprecated. It would be better to switch in the long run. Flux does have better support for regex’s on field names as @Anaisdg pointed out.

github.com/influxdata/influxdb

Support using regexes to select fields and dimensions

influxdata:master ← influxdata:js-5955-make-regex-work-on-field-keys-in-select

opened 07:42PM - 10 Oct 16 UTC

jsternberg

+110 -33

The functionality works the same as wildcards, but this time, you can specify a …regular expression. One limitation is that you can't specify whether you only want to select fields or tags. Since the regex can be changed to suit the person's needs, I don't currently think this is an issue. Fixes #5955.

github.com

influxdata/telegraf/blob/release-1.15/plugins/inputs/http/README.md

# HTTP Input Plugin

The HTTP input plugin collects metrics from one or more HTTP(S) endpoints.  The endpoint should have metrics formatted in one of the supported [input data formats](../../../docs/DATA_FORMATS_INPUT.md).  Each data format has its own unique set of configuration options which can be added to the input configuration.


### Configuration:

```toml
# Read formatted metrics from one or more HTTP endpoints
[[inputs.http]]
  ## One or more URLs from which to read formatted metrics
  urls = [
    "http://localhost/metrics"
  ]

  ## HTTP method
  # method = "GET"

  ## Optional HTTP headers
  # headers = {"X-Special-Header" = "Special-Value"}

This file has been truncated. show original

ahiyaz · October 1, 2020, 7:19am

thanks @philjb

I did use regex and was able to select the required fields.
but for some reason, the Preformatted text WHERE (“host” =~ /^site/) Preformatted textstatement didn’t work.
I got the “packet_loss” field from other hosts as well (multi-sites) although the fields of other sites didn’t contain any values (that good) I could still see them under Legend options. so what I did is selecting under the " Hide series" the “with only null” and that did the work.
do you think there is a better way?
thanks

philjb · October 13, 2020, 5:11am

Great! I’m glad the regex did what you needed for packet_loss.

I don’t usually write WHERE clauses with parentheses, but I would expect the where clause to be respected and process regular expressions too. Perhaps test with a direct match (no regex) to see that the where clause is indeed filtering your hosts?

Let us know what work for you.

ahiyaz · October 13, 2020, 6:27am

hi Phil

with direct match it works.

Topic		Replies	Views
Filter by metric value Telegraf telegraf , smnp	21	7870	January 10, 2020
Does influx support regexp (or similar) please? Telegraf influxdb	4	1124	May 11, 2018
Telgraf json -= first attempt Telegraf	1	482	February 25, 2019
How to create a tag from a field name with Telegraf? Telegraf	4	3590	February 17, 2022
Convert influxdb field into tag value	5	4352	July 22, 2018

Using regular expirations in a query

Related topics