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!

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...