Splunk Search

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

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

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

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

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
0 Karma

Explorer

I think it's a simplest answer:

yoursearch | spath input=yourjsonfield

Try it!

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

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

Explorer

Thank You !! Worked like a charm.

0 Karma

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