Stacked column chart for average values of arbitrary keysAverage timeslice

Follow

Daniel Olshansky

• August 10, 2019 12:28

I'm building a stacked column chart for some of my data via the following query and it works great:

_sourceCategory="<source>" and "<filter>"
| json field=_raw "json_field.key1.key2.key2" as json_value
| json auto field=json_value "v1", "v2", "v3"
| fields -_raw
| timeslice 2m
| avg(v1) as v1_avg,
avg(v2) as v2_avg
avg(v3) as v3_avg
by _timeslice

For one of my new datasets however, the keys of the json value are not known ahead of time - I do not know what v1, v2 and v3 are.

I was wondering if it's possible to achieve the same result without knowing these ahead of time?

I found this link which parses the json as a regex, but I have two issues with it:

• It works to aggregate counts, but I can't figure out how I can compute timesliced averages using the same approach
• The fact that I have a well-formatted json object, but am parsing it as a regex makes me feel that I'm doing something incorrectly

Thanks!

1

• 

  Jorge Silva

  • August 14, 2019 17:55
  • Edited

  
  Give this query a try:

  _sourceCategory="<source>" AND "<filter>"
  | json field=_raw "my_field" as my_field
  | parse regex field=my_field "(?<key>[^\"]+?)\"\s*:(?<value>\d+\.\d+)" multi
  | fields -_raw
  | timeslice 2m
  | avg(value) by _timeslice, key
  | transpose row _timeslice column key

  Here we use parse regex to extract each key and its value, then we average the value by timeslice and key and finally we use the "transpose" operator to create your chart.

  1

  Comment actions Permalink
• 

  Daniel Olshansky

  • August 15, 2019 13:23

  That works perfectly Jorge, thanks so much!
  
  Just for anyone else who might stumble upon this post, the above query can be used to easily created a stacked column chart where different log lines have an arbitrary number of key-value pairs and the keys are not known ahead of time, like so:
  
  Source:

  log1: {"key1": 1} 
  log2: {"key1": 2, "key2": 3} 
  log3: {"key1": 4, "key2": 5, "key3": 6}

  Output:

  key1: avg(1, 2, 4) 
  key2: avg(3, 5) 
  key3: avg(6)

  0

  Comment actions Permalink
• 

  Anthony Manning-Franklin

  • August 03, 2022 09:18

  I'm having a similar issue, except I want to work json values, so I have an object schema like

  ```

  {
    data: {
      [SOME_UNKNOWN_KEY]: {
        avgMs: number,
        sumMs: number,
        frequency: number,
        minMs: number,
        maxMs: number
      }
    }
  }

  I want to perform aggregation, analysis, and build stacked area line charts where each [SOME_UNKNOWN_KEY] is a line, and I chart one of its numeric values over time.

  0

  Comment actions Permalink
• 

  Anthony Manning-Franklin

  • August 03, 2022 13:34

  Wound up solving this one myself with

  | parse regex field=%message.data "(?<key>[^\"]*?)\"\s*:(?<value>[^}]*})" multi
  | json field=value "{{Field}}"
  | fields -_raw
  | timeslice 30m
  | avg({{Field}}) by _timeslice, key
  | transpose row _timeslice column key

  Where Field is one of avgMs, sumMs, maxMs, minMs, or frequency

  I just changed the regex to capture an object, and then ran | json on it again

  0

  Comment actions Permalink

Please sign in to leave a comment.