Getting Data In

Issues with Parsing JSON

dvmodeste
New Member

Hello everyone,

I having issues using Splunk to read and extract fields from this JSON file.
I would appreciate any help.

json data

{
  "uid" : "a82ee257",
  "name" : "Throughput Utilization",
  "axisXType" : "DateTime",
  "elementReports" : [ {
    "element" : {
      "id" : "001",
      "name" : "NS-001",
      "type" : "NetworkSegment"
    },
    "series" : [ {
      "uid" : "3242d4e4",
      "instance" : "0",
      "name" : "Utilization",
      "data" : [ {
        "x" : 1551051000000,
        "y" : 0.0
      }, {
        "x" : 1551051300000,
        "y" : 3.1
      }, {
        "x" : 1551136800000,
        "y" : 7.4
      }, {
        "x" : 1551137100000,
        "y" : 1.6
      } ],
      "e" : 1
    } ]
  }, {
    "element" : {
      "id" : "002",
      "name" : "NS-002",
      "type" : "NetworkSegment"
    },
    "series" : [ {
      "uid" : "4654d4e4",
      "instance" : "0",
      "name" : "Utilization",
      "data" : [ {
        "x" : 1551051000000,
        "y" : 0.3
      }, {
        "x" : 1551051300000,
        "y" : 0.0
      }, {
        "x" : 1551051600000,
        "y" : 0.0
      }, {
        "x" : 1551137100000,
        "y" : 2.12
      } ],
      "e" : 1
    } ]
  }, {
    "element" : {
      "id" : "003",
      "name" : "NS-003",
      "type" : "NetworkSegment"
    },
    "series" : [ {
      "uid" : "2481d4e6",
      "instance" : "0",
      "name" : "Utilization",
      "data" : [ {
        "x" : 1551051000000,
        "y" : 0.0
      }, {
        "x" : 1551051300000,
        "y" : 0.0
      }, {
        "x" : 1551051900000,
        "y" : 0.0
      }, {
        "x" : 1551136800000,
        "y" : 0.0
      } ],
      "e" : 1
    } ]
  }, {
    "element" : {
      "id" : "004",
      "name" : "NS-004",
      "type" : "NetworkSegment"
    },
    "series" : [ ]
  } ]
}

Here is my setting:

[json_sample]
TRUNCATE = 0
SHOULD_LINEMERGE=false
LINE_BREAKER = (,*){\s+"element"

Here is what I am expecting:

element.id,element.name,element.type,element.series.uid,element.series.instance,element.series.name,data.x,data.y,,,,
001,NS-001,NetworkSegment,3242d4e4,0,Utilization,1551051000000,0.0,,,,
001,NS-001,NetworkSegment,3242d4e4,0,Utilization,1551051300000,3.1,,,,
001,NS-001,NetworkSegment,3242d4e4,0,Utilization,1551136800000,7.4,,,,
001,NS-001,NetworkSegment,3242d4e4,0,Utilization,1551137100000,1.6,,,,
002,NS-002,NetworkSegment,4654d4e4,0,Utilization,1551051000000,0.3,,,,
002,NS-002,NetworkSegment,4654d4e4,0,Utilization,1551051300000,0.0,,,,
002,NS-002,NetworkSegment,4654d4e4,0,Utilization,1551136800000,0.0,,,,
002,NS-002,NetworkSegment,4654d4e4,0,Utilization,1551137100000,2.12,,,,
003,NS-003,NetworkSegment,2481d4e6,0,Utilization,1551051000000,,,,,
003,NS-003,NetworkSegment,2481d4e6,0,Utilization,1551051300000,,,,,
003,NS-003,NetworkSegment,2481d4e6,0,Utilization,1551136800000,,,,,
003,NS-003,NetworkSegment,2481d4e6,0,Utilization,1551137100000,,,,,
0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults
| eval _raw="{\"uid\":\"a82ee257\",\"name\":\"Throughput Utilization\",\"axisXType\":\"DateTime\",\"elementReports\":[{\"element\":{\"id\":\"001\",\"name\":\"NS-001\",\"type\":\"NetworkSegment\"},\"series\":[{\"uid\":\"3242d4e4\",\"instance\":\"0\",\"name\":\"Utilization\",\"data\":[{\"x\":1551051000000,\"y\":0.0},{\"x\":1551051300000,\"y\":3.1},{\"x\":1551136800000,\"y\":7.4},{\"x\":1551137100000,\"y\":1.6}],\"e\":1}]},{\"element\":{\"id\":\"002\",\"name\":\"NS-002\",\"type\":\"NetworkSegment\"},\"series\":[{\"uid\":\"4654d4e4\",\"instance\":\"0\",\"name\":\"Utilization\",\"data\":[{\"x\":1551051000000,\"y\":0.3},{\"x\":1551051300000,\"y\":0.0},{\"x\":1551051600000,\"y\":0.0},{\"x\":1551137100000,\"y\":2.12}],\"e\":1}]},{\"element\":{\"id\":\"003\",\"name\":\"NS-003\",\"type\":\"NetworkSegment\"},\"series\":[{\"uid\":\"2481d4e6\",\"instance\":\"0\",\"name\":\"Utilization\",\"data\":[{\"x\":1551051000000,\"y\":0.0},{\"x\":1551051300000,\"y\":0.0},{\"x\":1551051900000,\"y\":0.0},{\"x\":1551136800000,\"y\":0.0}],\"e\":1}]},{\"element\":{\"id\":\"004\",\"name\":\"NS-004\",\"type\":\"NetworkSegment\"},\"series\":[]}]}"
| rex mode=sed "s/.({\s*\"element)/#\1/g"
| makemv delim="#" _raw
| stats count by _raw
| rename COMMENT as "this is your sample with LINE_BREAKER. From here, the logic"
| spath path=series{}.data{} output=data
| stats values(_raw) as _raw by data
| spath input=data
| spath
| fields - _* series{}.data{}* data

props.conf

[json_sample]
TRUNCATE = 0
SHOULD_LINEMERGE = false
LINE_BREAKER = (.){\s*\"element

I made SPL with your setting.


| makeresults
| eval _raw="{
\"uid\" : \"a82ee257\",
\"name\" : \"Throughput Utilization\",
\"axisXType\" : \"DateTime\",
\"elementReports\" : [ {
\"element\" : {
\"id\" : \"001\",
\"name\" : \"NS-001\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ {
\"uid\" : \"3242d4e4\",
\"instance\" : \"0\",
\"name\" : \"Utilization\",
\"data\" : [ {
\"x\" : 1551051000000,
\"y\" : 0.0
}, {
\"x\" : 1551051300000,
\"y\" : 3.1
}, {
\"x\" : 1551136800000,
\"y\" : 7.4
}, {
\"x\" : 1551137100000,
\"y\" : 1.6
} ],
\"e\" : 1
} ]
}, {
\"element\" : {
\"id\" : \"002\",
\"name\" : \"NS-002\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ {
\"uid\" : \"4654d4e4\",
\"instance\" : \"0\",
\"name\" : \"Utilization\",
\"data\" : [ {
\"x\" : 1551051000000,
\"y\" : 0.3
}, {
\"x\" : 1551051300000,
\"y\" : 0.0
}, {
\"x\" : 1551051600000,
\"y\" : 0.0
}, {
\"x\" : 1551137100000,
\"y\" : 2.12
} ],
\"e\" : 1
} ]
}, {
\"element\" : {
\"id\" : \"003\",
\"name\" : \"NS-003\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ {
\"uid\" : \"2481d4e6\",
\"instance\" : \"0\",
\"name\" : \"Utilization\",
\"data\" : [ {
\"x\" : 1551051000000,
\"y\" : 0.0
}, {
\"x\" : 1551051300000,
\"y\" : 0.0
}, {
\"x\" : 1551051900000,
\"y\" : 0.0
}, {
\"x\" : 1551136800000,
\"y\" : 0.0
} ],
\"e\" : 1
} ]
}, {
\"element\" : {
\"id\" : \"004\",
\"name\" : \"NS-004\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ ]
} ]
}"
| spath path=elementReports{} output=elementReports
| mvexpand elementReports
| spath input=elementReports path=series{}.data{} output=data
| mvexpand data
| spath input=elementReports
| spath input=data
| fields - series{}.data* elementReports data _*
| rename series{}.* as *

props.conf

[json_sample]
TRUNCATE = 0
SHOULD_LINEMERGE = false
KV_MODE = JSON

Isn't it okay if you don't divide it?

View solution in original post

0 Karma

dvmodeste
New Member

Thanks to4kawa.
you did great job. it is working.
could you please help me understand this ?

| rex mode=sed "s/.({\s*\"element)/#\1/g"
| makemv delim="#" _raw
| stats count by _raw
| rename COMMENT as "this is your sample with LINE_BREAKER. From here, the logic"
| spath path=series{}.data{} output=data
| stats values(_raw) as _raw by data
| spath input=data
| spath
| fields - _* series{}.data{}* data

0 Karma

to4kawa
Ultra Champion
| rex mode=sed "s/.({\s*\"element)/#\1/g"
| makemv delim="#" _raw
| stats count by _raw
| rename COMMENT as "this is your sample with LINE_BREAKER. From here, the logic"
| spath path=series{}.data{} output=data
| stats values(_raw) as _raw by data
| spath input=data
| spath
| fields - series{}.data{} data
  • rex makes delimiter # LINE_BREAKER = (.){\s*\"element| rex mode=sed "s/.({\s*\"element)/#\1/g" | makemv delim="#" _raw .(dot) is break point.
  • from | spath path=series{}.data{} output=data, try line by line and check result.
  • stats is an alternative to mvexpand.
  • two spath extract the required fields.
  • at last, remove extra fields.
0 Karma

to4kawa
Ultra Champion
| makeresults
| eval _raw="{\"uid\":\"a82ee257\",\"name\":\"Throughput Utilization\",\"axisXType\":\"DateTime\",\"elementReports\":[{\"element\":{\"id\":\"001\",\"name\":\"NS-001\",\"type\":\"NetworkSegment\"},\"series\":[{\"uid\":\"3242d4e4\",\"instance\":\"0\",\"name\":\"Utilization\",\"data\":[{\"x\":1551051000000,\"y\":0.0},{\"x\":1551051300000,\"y\":3.1},{\"x\":1551136800000,\"y\":7.4},{\"x\":1551137100000,\"y\":1.6}],\"e\":1}]},{\"element\":{\"id\":\"002\",\"name\":\"NS-002\",\"type\":\"NetworkSegment\"},\"series\":[{\"uid\":\"4654d4e4\",\"instance\":\"0\",\"name\":\"Utilization\",\"data\":[{\"x\":1551051000000,\"y\":0.3},{\"x\":1551051300000,\"y\":0.0},{\"x\":1551051600000,\"y\":0.0},{\"x\":1551137100000,\"y\":2.12}],\"e\":1}]},{\"element\":{\"id\":\"003\",\"name\":\"NS-003\",\"type\":\"NetworkSegment\"},\"series\":[{\"uid\":\"2481d4e6\",\"instance\":\"0\",\"name\":\"Utilization\",\"data\":[{\"x\":1551051000000,\"y\":0.0},{\"x\":1551051300000,\"y\":0.0},{\"x\":1551051900000,\"y\":0.0},{\"x\":1551136800000,\"y\":0.0}],\"e\":1}]},{\"element\":{\"id\":\"004\",\"name\":\"NS-004\",\"type\":\"NetworkSegment\"},\"series\":[]}]}"
| rex mode=sed "s/.({\s*\"element)/#\1/g"
| makemv delim="#" _raw
| stats count by _raw
| rename COMMENT as "this is your sample with LINE_BREAKER. From here, the logic"
| spath path=series{}.data{} output=data
| stats values(_raw) as _raw by data
| spath input=data
| spath
| fields - _* series{}.data{}* data

props.conf

[json_sample]
TRUNCATE = 0
SHOULD_LINEMERGE = false
LINE_BREAKER = (.){\s*\"element

I made SPL with your setting.


| makeresults
| eval _raw="{
\"uid\" : \"a82ee257\",
\"name\" : \"Throughput Utilization\",
\"axisXType\" : \"DateTime\",
\"elementReports\" : [ {
\"element\" : {
\"id\" : \"001\",
\"name\" : \"NS-001\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ {
\"uid\" : \"3242d4e4\",
\"instance\" : \"0\",
\"name\" : \"Utilization\",
\"data\" : [ {
\"x\" : 1551051000000,
\"y\" : 0.0
}, {
\"x\" : 1551051300000,
\"y\" : 3.1
}, {
\"x\" : 1551136800000,
\"y\" : 7.4
}, {
\"x\" : 1551137100000,
\"y\" : 1.6
} ],
\"e\" : 1
} ]
}, {
\"element\" : {
\"id\" : \"002\",
\"name\" : \"NS-002\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ {
\"uid\" : \"4654d4e4\",
\"instance\" : \"0\",
\"name\" : \"Utilization\",
\"data\" : [ {
\"x\" : 1551051000000,
\"y\" : 0.3
}, {
\"x\" : 1551051300000,
\"y\" : 0.0
}, {
\"x\" : 1551051600000,
\"y\" : 0.0
}, {
\"x\" : 1551137100000,
\"y\" : 2.12
} ],
\"e\" : 1
} ]
}, {
\"element\" : {
\"id\" : \"003\",
\"name\" : \"NS-003\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ {
\"uid\" : \"2481d4e6\",
\"instance\" : \"0\",
\"name\" : \"Utilization\",
\"data\" : [ {
\"x\" : 1551051000000,
\"y\" : 0.0
}, {
\"x\" : 1551051300000,
\"y\" : 0.0
}, {
\"x\" : 1551051900000,
\"y\" : 0.0
}, {
\"x\" : 1551136800000,
\"y\" : 0.0
} ],
\"e\" : 1
} ]
}, {
\"element\" : {
\"id\" : \"004\",
\"name\" : \"NS-004\",
\"type\" : \"NetworkSegment\"
},
\"series\" : [ ]
} ]
}"
| spath path=elementReports{} output=elementReports
| mvexpand elementReports
| spath input=elementReports path=series{}.data{} output=data
| mvexpand data
| spath input=elementReports
| spath input=data
| fields - series{}.data* elementReports data _*
| rename series{}.* as *

props.conf

[json_sample]
TRUNCATE = 0
SHOULD_LINEMERGE = false
KV_MODE = JSON

Isn't it okay if you don't divide it?

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...