Using regular expirations in a query

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. :raised_hands:

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

thanks

hi

can anyone help with this?

thanks

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() 

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

@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.




1 Like

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

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.

hi Phil

with direct match it works.

1 Like