How to parse JSON data with spath and table the data.

[adibrr16]

Hi

I am trying to parse this json using spath. I am not able to parse "data" element.

{  
   "id":"eab50eea-4b3c-4ca1-9c59-eb66500b57ea",
   "severity":"Error",
   "data":{  
      "CallingMethod":"UpdResponse",
      "ClientRequestId":"8fb58962-88df-4fa4-b787-163c3bca92b0",
      "SourceTransactionId":"9344949-ABS",
      "OrderType":"Domestic",
      "Errors":[  
         {  
            "ErrNumber":14076,
            "Message":"SE 2 already exists in OTC."
         },
         {  
            "ErrNumber":14076,
            "Message":"SE 3 already exists in OTC."
         }
      ],
      "Level":"Error",
      "TimeStamp":"4/19/2018 1:07:15 PM"
   }
}

Planning to show data in tabular format

id | severity | SourceTransactionId | ErrNumber | Message

I tried some queries bust was not successful.

source="otc" severity="error" 
| spath data{} output=data 
| stats count by severity data 
| eval _raw=data 
| spath Errors{} output=Errors 
| spath CallingMethod output=CallingMethod 
| stats count by severity CallingMethod Errors 
| eval _raw=Erors | spath ErrNumber output=ErrNumber | spath Message output=Message 
| table severity CallingMethod ErrNumber Message 

Any help appreciated.

Thanks

[niketn]

@adibrr16, Please try the following run anywhere search based on your sample data. Instead of first two pipes i.e. | makeresults and | eval _raw... you can use your base search i.e. source="otc" severity="error"

| makeresults
| eval _raw=" {  
    \"id\":\"eab50eea-4b3c-4ca1-9c59-eb66500b57ea\",
    \"severity\":\"Error\",
    \"data\":{  
       \"CallingMethod\":\"UpdResponse\",
       \"ClientRequestId\":\"8fb58962-88df-4fa4-b787-163c3bca92b0\",
       \"SourceTransactionId\":\"9344949-ABS\",
       \"OrderType\":\"Domestic\",
       \"Errors\":[  
          {  
             \"ErrNumber\":14076,
             \"Message\":\"SE 2 already exists in OTC.\"
          },
          {  
             \"ErrNumber\":14076,
             \"Message\":\"SE 3 already exists in OTC.\"
          }
       ],
       \"Level\":\"Error\",
       \"TimeStamp\":\"4/19/2018 1:07:15 PM\"
    }
 }"
| spath
| rename "data.*" as "*"
| rename "Errors{}.*" as "*"
| eval ErrorData=mvzip(ErrNumber,Message)
| mvexpand ErrorData
| makemv ErrorData delim=","
| eval ErrNumber=mvindex(ErrorData,0),Message=mvindex(ErrorData,1)
| table id severity CallingMethod SourceTransactionId ErrNumber Message