Splunk Search

null events while using spath

New Member

JSON:

"mainArray":
[
{"name":"MS","value":20},
{"name":"MC","value":20},
{"name":"CF","value":20},
{"name":"ST"},
{"name":"CMR","value":20}
]

-- i am currently using the search as " | spath output=code path=mainArray{}.name | spath output=cnt path=mainArray{}.value | table code,cnt"

and the output i see is as :

code cnt
MS 20
MC 20
CF 20
ST 20
CMR

The Expected Output is:

code cnt
MS 20
MC 20
CF 20
ST

CMR 20

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Try like this (runanywhere sample. Everything before | rex command is for generating sample data, replace that with your base search.

| gentimes start=-1 | eval _raw="{\"mainArray\":
[
 {\"name\":\"MS\",\"value\":20},
 {\"name\":\"MC\",\"value\":20},
 {\"name\":\"CF\",\"value\":20},
 {\"name\":\"ST\"},
 {\"name\":\"CMR\",\"value\":20}
]}" | table _raw 
| rex mode=sed "s/(\"name\":\"[^\"]+\")\}/\1,\"value\":\"NA\"}/g" | spath  | spath output=code path=mainArray{}.name | spath output=cnt path=mainArray{}.value | table code,cnt

View solution in original post

SplunkTrust
SplunkTrust

Try like this (runanywhere sample. Everything before | rex command is for generating sample data, replace that with your base search.

| gentimes start=-1 | eval _raw="{\"mainArray\":
[
 {\"name\":\"MS\",\"value\":20},
 {\"name\":\"MC\",\"value\":20},
 {\"name\":\"CF\",\"value\":20},
 {\"name\":\"ST\"},
 {\"name\":\"CMR\",\"value\":20}
]}" | table _raw 
| rex mode=sed "s/(\"name\":\"[^\"]+\")\}/\1,\"value\":\"NA\"}/g" | spath  | spath output=code path=mainArray{}.name | spath output=cnt path=mainArray{}.value | table code,cnt

View solution in original post

New Member

Thanks a lot @ somesoni2. i was able to use your regex with sed to make the null event as NA and it worked perfectly.

0 Karma