Getting Data In

Parse JSON series data into a chart

jercra
Explorer

I'm trying to parse the following JSON data into a timechart "by label". The "data" section is a timestamp and a value. I've managed to get each series into its own event but I can't seem to get anything parse to below the series level;

{
  "9": {
    "series": [
      {
        "label": "content",
        "data": [
          [
            1493673985000,
            10
          ],
          [
            1493673990000,
            10
          ],
          [
            1493673995000,
            10
          ]
        ]
      },
      {
        "label": "filters",
        "data": [
          [
            1493673985000,
            3
          ],
          [
            1493673990000,
            3
          ],
          [
            1493673995000,
            3
          ]
        ]
      },
      {
        "label": "total",
        "data": [
          [
            1493673985000,
            14
          ],
          [
            1493673990000,
            14
          ],
          [
            1493673995000,
            14
          ]
        ]
      }
    ]
  }
}

By using | spath output=series path="9.series{}" I'm able to get the each series split into an event. I think I just need to extract label, mvzip it with data{0} and data{1} and then mvexpand that but I can't seem to find the syntax that works for that. In then I'd like something that allows me to do something like | eval _time=timestamp| timechart max(value) by label

Tags (2)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

You're right, it was remarkably ugly to extract that nested array. IMHO, path=....data should have copied the whole array of arrays to the output field, and it didn't. path=....data{}{} just dumped them all into a single-dimensional array and lost which was which. So, here's your code.

| makeresults |eval _raw="{ \"9\": {\"series\": [ {\"label\": \"content\" , \"data\": [ [ 1493673985000, 10 ] , [ 1493673990000, 11 ] , [ 1493673995000, 12 ]  ] }, {\"label\": \"filters\" , \"data\": [ [ 1493673985000, 3 ] , [ 1493673990000, 1 ] , [ 1493673995000, 3 ]  ] }, {\"label\": \"total\" , \"data\": [ [ 1493673985000, 14 ] , [ 1493673990000, 14 ] , [ 1493673995000, 17 ]  ] } ] } }" | eval junk="{\"data\": [ [ 1493673985000, 10 ] , [ 1493673990000, 10 ] , [ 1493673995000, 10 ] ] }"
| rename COMMENT as "The above just enters your sample data"

| spath input=_raw output=series path=9.series{}
| table series
| mvexpand series
| spath input=series output=label path=label
| spath input=series output=datatime path=data{}{0}
| spath input=series output=datavalue path=data{}{1}
| eval values=mvzip(datatime,datavalue,"=")
| mvexpand values
| rex field=values "(?<time>[^=]+)=(?<value>\d+)"
| eval _time = time/1000
| table _time label value
| timechart span=5s avg(value) by label

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

You're right, it was remarkably ugly to extract that nested array. IMHO, path=....data should have copied the whole array of arrays to the output field, and it didn't. path=....data{}{} just dumped them all into a single-dimensional array and lost which was which. So, here's your code.

| makeresults |eval _raw="{ \"9\": {\"series\": [ {\"label\": \"content\" , \"data\": [ [ 1493673985000, 10 ] , [ 1493673990000, 11 ] , [ 1493673995000, 12 ]  ] }, {\"label\": \"filters\" , \"data\": [ [ 1493673985000, 3 ] , [ 1493673990000, 1 ] , [ 1493673995000, 3 ]  ] }, {\"label\": \"total\" , \"data\": [ [ 1493673985000, 14 ] , [ 1493673990000, 14 ] , [ 1493673995000, 17 ]  ] } ] } }" | eval junk="{\"data\": [ [ 1493673985000, 10 ] , [ 1493673990000, 10 ] , [ 1493673995000, 10 ] ] }"
| rename COMMENT as "The above just enters your sample data"

| spath input=_raw output=series path=9.series{}
| table series
| mvexpand series
| spath input=series output=label path=label
| spath input=series output=datatime path=data{}{0}
| spath input=series output=datavalue path=data{}{1}
| eval values=mvzip(datatime,datavalue,"=")
| mvexpand values
| rex field=values "(?<time>[^=]+)=(?<value>\d+)"
| eval _time = time/1000
| table _time label value
| timechart span=5s avg(value) by label

jercra
Explorer

I was so close and yet so far. This absolutely solved my issue and since this is the format I get from multiple API calls, it solves a bunch of future issues as well. Thank you!!

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...