Expanding nested events

[BearMormont]

Let's say I have an event that looks like this:

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

I am trying to format a table that ultimately combines these elements like this for a single event:

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       Submit Timesheet  Fill out Timesheet 2/1/2018
John      Doe       Email Timesheet   Email Timesheet    2/2/2018

I have been playing around with mvexand which lets me get the Projects level organized, but I do not know how to continue this into the Tasks level. Would someone be able to point me in the correct direction?

[p_gurav]

Hi
How you get Projects level ? Can you share query your are using?

[BearMormont]

Not really. This is more a made up example that is similar to my production data. But the query is really just an index statement.

[miyamaet]

You can find some useful sample in below document,
http://docs.splunk.com/Documentation/SplunkCloud/7.0.0/SearchReference/Spath

And try like this,

index= <your_own_search> | spath |rename Projects{}.Tasks{}.TaskDueDate AS x, Projects{}.ProjectName AS y, Projects{}.Tasks{}.TaskName AS z |eval joinfield = mvzip(x,mvzip(y,z)) |mvexpand joinfield |eval Name=mvindex(FirstName,0) +" "+mvindex(LastName,0) | eval spjoinfield = split(joinfield,",") | eval Date=mvindex(spjoinfield ,0) | eval ProjectName=mvindex(spjoinfield ,1)| eval TaskName=mvindex(spjoinfield ,2)|table Name ProjectName TaskName Date

I hope this helps.

[BearMormont]

It does help quite a great deal, thanks!

I do have one follow up question. It seems to work when each project has exactly two tasks, but let's say the second project (Submit Timesheet) had a third task added (Get paid) while the first project (Build Computer) stayed the way it is.

In the output I only see the first two tasks for the second project; the third just drops off. I have no way to know exactly how many tasks any given project will have ahead of time.

Thanks!