UQL query for JSON data

Grafana
1

I want to transform below data in grafana such that I have 3 columns.

  1. for vhost e.g. www.google.com
  2. for 1st value under sent_bytes i.e. 1741421400 (epoch)
  3. for 2nd value under sent_bytes i.e. 204626090 (bytes)

Can someone please help me write a UQL query to transform the data as needed?

Below is the data:

{
  "vhost": {
    "www.google.com": {
      "metrics": {
        "sent_bytes": [
          [
            1741421400,
            204626090
          ],
          [
            1741421460,
            32288988229
          ],
          [
            1741421520,
            30957862076
          ],
          [
            1741421580,
            32160304293
          ],
          [
            1741421640,
            32199266036
          ],
          [
            1741421700,
            31333896590
          ]
        ]
      }
    },
    "2ndvhost": {
      "metrics": {
        "sent_bytes": [
          [
            1741421400,
            0
          ],
          [
            1741421460,
            517576
          ],
          [
            1741421520,
            0
          ],
          [
            1741421580,
            0
          ],
          [
            1741421640,
            0
          ],
          [
            1741421700,
            0
          ]
        ]
      }
    },
    "3rdvhost": {
      "metrics": {
        "sent_bytes": [
          [
            1741421400,
            0
          ],
          [
            1741421460,
            456499281
          ],
          [
            1741421520,
            444765703
          ],
          [
            1741421580,
            477708716
          ],
          [
            1741421640,
            439811300
          ],
          [
            1741421700,
            409478691
          ]
        ]
      }
    }
  }
}
2

Welcome @tanmaymadan95

What have you tried so far?

Check out this document also

https://try.jsonata.org/oHcVGcQR6

3

Hello @yosiasz

I was able to achieve the result and create 3 columns writing below UQL query, however now I also want to add a filter on top so I can filter out traffic according to the domain/vhost. I have added a variable and also able to see the list(values of hostnames), but it is not yet integrated with the GRAPH. What should I add in the UQL query to make it happen? can you help?

===
parse-json
scope “vhost”
project kv()
extend “sent_bytes”=“value.metrics.sent_bytes”
mv-expand “sent_bytes”
extend “vhost_name”=“key”
extend “Time”=unixtime_seconds_todatetime(“sent_bytes[0]”)
extend “bytes”=“sent_bytes[1]”
project “vhost_name”, “Time”, “bytes”

1 Like
4

what does the Table view show when you implement that UQL

5

image
image1524×474 38.8 KB

image
image1533×472 30.2 KB

Hiding hostnames for confidentiality

Can you help me how can I integrate the variable with the data? Currently selecting the variable doesn’t work.

1 Like
6

please create a new thread as variable issue was not in the original post

7

Created a new topic.
Thanks!

1 Like