Splunk Search

Expanding nested events

BearMormont
Path Finder

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?

0 Karma

p_gurav
Champion

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

0 Karma

BearMormont
Path Finder

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.

0 Karma

miyamaet
Explorer

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.

0 Karma

BearMormont
Path Finder

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!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...