topic Re: Simple Json formatting into table in Splunk Search

Simple Json formatting into table

psable — Fri, 01 Jul 2016 02:45:03 GMT

Hi, I posted similar question earlier but I dont see it anymore as posted so reposting simplified version.

json has this format

"Diagnosis": {
    "Version": 2,
    "dia": [
      {
        "name": "EF",
        "stringValue": "Emergency",
        "isRequired": false,
        "Defaultvalue": "EF"
      },
      {
        "name": "WR",
        "stringValue": 0,
        "isRequired": true,
        "Defaultvalue": "EN"
      } ]
The table needs to be in this format

name stringvalue isrequired defaultValue
EF Emergency false EF
WR 0 true EN

I am not able to figure out how to put in this format, I used spath but the columns entries do not match to corresponding rows...i.e. EF might match with 0 in stringValue instead in Emeregency . I saw mention that mvzip might work but I do not know how to use it. Can someone please help me ?

Thank you !

Re: Simple Json formatting into table

renjith_nair — Fri, 01 Jul 2016 04:53:21 GMT

Referring to the example in http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/spath#Example_3:_Extract_and_expand_JSON_events_with_multi-valued_fields, below works fine for me

|stats count |eval json1="{
\"Diagnosis\": 
      {
   \"Version\": 2,
    \"dia\": 
    [
      {
        \"name\": \"EF\",
        \"stringValue\": \"Emergency\",
        \"isRequired\": false,
        \"Defaultvalue\": \"EF\"
      },
      {
        \"name\": \"WR\",
        \"stringValue\": 0,
        \"isRequired\": true,
        \"Defaultvalue\": \"EN\"
      } 
    ]
}     
}"
|spath input=json1|rename Diagnosis.dia{}.* as *
|eval values=mvzip(mvzip(mvzip(name,stringValue),isRequired),Defaultvalue)
|mvexpand values| eval values = split(values,",") 
|eval name=mvindex(values,0)|eval stringValue=mvindex(values,1) |eval isRequired=mvindex(values,2)|eval Defaultvalue=mvindex(values,3) | table name,stringValue,isRequired,Defaultvalue

Re: Simple Json formatting into table

psable — Fri, 01 Jul 2016 12:59:18 GMT

Thanks for reply. For some reason, this one does not return any result for me, am I missing anything ?

Re: Simple Json formatting into table

renjith_nair — Thu, 07 Jul 2016 02:22:16 GMT

This is just a sample dummy search and you need to apply this in your original. Are you not getting anything if you copy paste the entire section to a search window? are you getting any error?