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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...