Nested JSON and SPATHING

[BearMormont]

Hi,

I have another question similar to the question I asked at https://answers.splunk.com/answers/624148/expanding-nested-events.html. The solution posted sort of worked, but stopped working when the number of Tasks changed between Projects.

Take for example the following single Event (which is the result of a search):

 {
   FirstName: John
   LastName: Doe
   Projects: [
     {
       ProjectName: Build Computer
       Tasks: [
         {
           TaskName: Order hardware
           TaskDueDate: 1/1/2018
         }
         {
           TaskName: Install software
           TaskDueDate: 1/2/2018
         }
         {
           TaskName: Deploy machine
           TaskDueDate: 1/3/2018
         }
       ]
     }
     {
       ProjectName: Submit Timesheet
       Tasks: [
         {
           TaskName: Fill out Timesheet
           TaskDueDate: 2/1/2018
         }
         {
           TaskName: Email Timesheet
           TaskDueDate: 2/2/2018
         }
       ]
     }
   ]
 }

I guess my issue is the SPATHing and linking the fields. If I Spath to Projects{}.Tasks{} I get two events with three Tasks in the first event and two tasks in the second event, which I was expecting. And if I were only interested in those tasks I could easily mvzip them and expand them to separate events. My issue is no matter what I try I cannot match them up with the proper Project Name. It seems to just give Build Computer to each of them, even though the second set of tasks should match up to Submit Timesheet.

This is the same result I get if I use SPATH alone without actually specifying a path.

If I SPATH into just Projects{}, I get two Tasks in the first event and two Tasks in the second, then I end up losing some of the tasks. Basically I am trying to get the output to look like:

 FirstName LastName  ProjectName       TaskName           TaskDueDate
 John      Doe       Build Computer    Order hardware     1/1/2018
 John      Doe       Build Computer    Install software   1/2/2018
 John      Doe       Build Computer    Deploy Machine     1/3/2018
 John      Doe       Submit Timesheet  Fill out Timesheet 2/1/2018
 John      Doe       Email Timesheet   Email Timesheet    2/2/2018

Thanks!

[kamlesh_vaghela · Accepted solution]

Hi @BearMormont,

I have tried a lot with your given JSON and finally, I got the expected result you want. Can you please try this search?

YOUR_SEARCH 
| spath Projects{} output=Projects 
| stats count by FirstName LastName Projects 
| eval _raw=Projects 
| spath Tasks{} output=Tasks | spath ProjectName output=ProjectName 
| stats count by FirstName LastName ProjectName Tasks 
| eval _raw=Tasks | spath TaskName output=TaskName | spath TaskDueDate output=TaskDueDate | table FirstName LastName ProjectName TaskName TaskDueDate

Thanks

[BearMormont]

THANK YOU!!!

First, this works perfectly. Thank you so much.

Second, can you please explain to me a little bit about how it works. Based on what I had read previously I thought I would need to use a combination of SPATH and MVZIP/MVEXPAND, but this didn't involve any multi value commands. Any insight you could impart would be greatly appreciated.

[kamlesh_vaghela]

Glad to help you.

Yes, first I have tried with mvzip but I got just first value & other values removed from results. I'm not suggesting mvexpand command if you have heavy JSON events because it has its own limitation. So I've used here stats commands to make avail all possible multivalued fields. Well here spath works well for us. if you execute this search up to stats command you will get another JSON. Eg, this search.

 YOUR_SEARCH 
 | spath Projects{} output=Projects 
 | stats count by FirstName LastName Projects 

After the stats by FirstName LastName Projects I will get JSON in Projects fields. This JSON field will work for as raw event for next spath command. Checkout this.

 | eval _raw=Projects

So spath will enough for us to go deeper in JSON.

I hope you this will help you and I'm sure you will try it with different types of JSON.

Thanks
Happy Splunking

[ktwingstrom]

I would be VERY interested in an answer to this. I basically need to do the same thing.