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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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