topic Re: Issues with Parsing JSON in Getting Data In

Issues with Parsing JSON

dvmodeste — Fri, 03 Apr 2020 16:26:32 GMT

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,,,,,

Re: Issues with Parsing JSON

to4kawa — Sat, 04 Apr 2020 04:21:06 GMT

| 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?

Re: Issues with Parsing JSON

dvmodeste — Wed, 30 Sep 2020 04:53:57 GMT

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

Re: Issues with Parsing JSON

to4kawa — Wed, 08 Apr 2020 07:20:02 GMT

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