Splunk Search

How to parse my JSON data with spath and table the data?

deepak312
Explorer

I am trying to parse this json using spath,

{
  "Class": "11",
  "date": "05/16/2016",
  "Student": [
    {
      "RollNo": "1234",
      "SubjectDetails": [
        {
          "type": "Mandatory",
          "startTime": "05/16/2016 21:30",
          "endTime": "05/16/2016 22:00",
          "name": "English"
        },
        {
          "type": "Optional",
          "startTime": "05/16/2016 20:00",
          "endTime": "05/16/2016 21:30",
          "name": "Math"
        }
      ]
    }
  ]
}

I am trying to plot it in a table. Any help will be appreciated.

Tags (3)
1 Solution

jkat54
SplunkTrust
SplunkTrust

Hello,

This seems to work with your data:

...
| spath 
| rename Student{}.SubjectDetails{}.name AS name, Student{}.SubjectDetails{}.type AS type, Student{}.SubjectDetails{}.endTime as endTime, Student{}.SubjectDetails{}.startTime as startTime, Student{}.RollNo as RollNo 
| eval a=mvzip(Class,RollNo) 
| eval b=mvzip(date,RollNo) 
| eval c=mvzip(RollNo,name) 
| eval d=mvzip(name,type) 
| eval e=mvzip(name,startTime) 
| eval f=mvzip(name,endTime) 
| eval g=mvzip(a,b) 
| eval h=mvzip(c,d) 
| eval i=mvzip(e,f) 
| eval j=mvzip(g,h) 
| eval k=mvzip(g,i) 
| eval x=mvzip(j,k) 
| mvexpand x 
| eval x=split(x,",") 
| eval Class=mvindex(x,0) 
| eval RollNo=mvindex(x,1)
| eval date=mvindex(x,2)
| eval name=mvindex(x,5)
| eval type=mvindex(x,7)
| eval startTime=mvindex(x,13)
| eval endTime=mvindex(x,15)
| table  Class, RollNo, date, name, type startTime, endTime

alt text

This was my first time successfully parsing json this way... I used this to learn how to do it: http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Spath

View solution in original post

ddelapasse
Explorer

Can someone please tell me why this answer isn't working in my 7.1.3? I only get one row instead of the two rows shown above. I'm brand new to Splunk, but this is the 3rd similar example I've tried that is supposed to render multiple rows but does not for me.

| makeresults
 | eval _raw="{\"Class\":\"11\",\"date\":\"05/16/2016\",\"Student\":[{\"RollNo\":\"1234\",\"SubjectDetails\":[{\"type\":\"Mandatory\",\"startTime\":\"05/16/2016 21:30\",\"endTime\":\"05/16/2016 22:00\",\"name\":\"English\"},{\"type\":\"Optional\",\"startTime\":\"05/16/2016 20:00\",\"endTime\":\"05/16/2016 21:30\",\"name\":\"Math\"}]}]}"
| spath | rename Student{}.SubjectDetails{}.name AS name, Student{}.SubjectDetails{}.type AS type, Student{}.SubjectDetails{}.endTime as endTime, Student{}.SubjectDetails{}.startTime as startTime, Student{}.RollNo as RollNo 
 | eval a=mvzip(Class,RollNo) 
 | eval b=mvzip(date,RollNo) 
 | eval c=mvzip(RollNo,name) 
 | eval d=mvzip(name,type) 
 | eval e=mvzip(name,startTime) 
 | eval f=mvzip(name,endTime) 
 | eval g=mvzip(a,b) 
 | eval h=mvzip(c,d) 
 | eval i=mvzip(e,f) 
 | eval j=mvzip(g,h) 
 | eval k=mvzip(g,i) 
 | eval x=mvzip(j,k) 
 | mvexpand x 
 | eval x=split(x,",") 
 | eval Class=mvindex(x,0) 
 | eval RollNo=mvindex(x,1)
 | eval date=mvindex(x,2)
 | eval name=mvindex(x,5)
 | eval type=mvindex(x,7)
 | eval startTime=mvindex(x,13)
 | eval endTime=mvindex(x,15)
 | table  Class, RollNo, date, name, type startTime, endTime

jkat54
SplunkTrust
SplunkTrust

Not sure when the behavior changed, but now i have to mvexpand twice:

  | makeresults
  | eval _raw="{\"Class\":\"11\",\"date\":\"05/16/2016\",\"Student\":[{\"RollNo\":\"1234\",\"SubjectDetails\":[{\"type\":\"Mandatory\",\"startTime\":\"05/16/2016 21:30\",\"endTime\":\"05/16/2016 22:00\",\"name\":\"English\"},{\"type\":\"Optional\",\"startTime\":\"05/16/2016 20:00\",\"endTime\":\"05/16/2016 21:30\",\"name\":\"Math\"}]}]}"
  | spath 
  | rename Student{}.SubjectDetails{}.name AS name, Student{}.SubjectDetails{}.type AS type, Student{}.SubjectDetails{}.endTime as endTime, Student{}.SubjectDetails{}.startTime as startTime, Student{}.RollNo as RollNo
  | eval a=mvzip(name,type) 
  | mvexpand a
  | eval b=mvzip(startTime,RollNo) 
  | eval c=mvzip(a,b)
  | mvexpand c
  | eval x=split(c,",")
  | eval name=mvindex(x,0)
  | eval type=mvindex(x,1)
  | eval startTime=mvindex(x,2)
  | eval RollNo=mvindex(x,3)
  | table name, type, startTime, RollNo

chlima
Explorer

I think it's a simplest answer:

yoursearch | spath input=your_json_field

Try it!

woodcock
Esteemed Legend

This is a more general approach:

 | makeresults count=3
 | eval ExtendedProperties.Name="DisplayName IsPublic Mail"
 | makemv ExtendedProperties.Name
 | eval ExtendedProperties.Value=case(
    (serial=1), "Test Group::False::testgroup@microsoft.com",
    (serial=2), "Group1::True::group1@microsoft.com",
    (serial=3), "Group2::True::group2@microsoft.com")
 | makemv delim="::" ExtendedProperties.Value
 | fields - _time

 | rename COMMENT AS "Everything above is faking your data; everyting below is your solution"

 | streamstats count AS serial
 | eval KVP=mvzip('ExtendedProperties.Name', 'ExtendedProperties.Value', "=")
 | table KVP serial
 | mvexpand KVP
 | rex field=KVP "^(?<KVP_key>[^=]+)=(?<KVP_value>[^=]+)$"
 | eval {KVP_key}=KVP_value
 | fields - KVP* 
 | stats values(*) AS * BY serial
0 Karma

jkat54
SplunkTrust
SplunkTrust

Hello,

This seems to work with your data:

...
| spath 
| rename Student{}.SubjectDetails{}.name AS name, Student{}.SubjectDetails{}.type AS type, Student{}.SubjectDetails{}.endTime as endTime, Student{}.SubjectDetails{}.startTime as startTime, Student{}.RollNo as RollNo 
| eval a=mvzip(Class,RollNo) 
| eval b=mvzip(date,RollNo) 
| eval c=mvzip(RollNo,name) 
| eval d=mvzip(name,type) 
| eval e=mvzip(name,startTime) 
| eval f=mvzip(name,endTime) 
| eval g=mvzip(a,b) 
| eval h=mvzip(c,d) 
| eval i=mvzip(e,f) 
| eval j=mvzip(g,h) 
| eval k=mvzip(g,i) 
| eval x=mvzip(j,k) 
| mvexpand x 
| eval x=split(x,",") 
| eval Class=mvindex(x,0) 
| eval RollNo=mvindex(x,1)
| eval date=mvindex(x,2)
| eval name=mvindex(x,5)
| eval type=mvindex(x,7)
| eval startTime=mvindex(x,13)
| eval endTime=mvindex(x,15)
| table  Class, RollNo, date, name, type startTime, endTime

alt text

This was my first time successfully parsing json this way... I used this to learn how to do it: http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Spath

deepak312
Explorer

Thank You !! Worked like a charm.

0 Karma

jkat54
SplunkTrust
SplunkTrust

Many thanks to you for the opportunity to learn how to parse json... now if only i had known this a couple years ago 😉

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...