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?
Hi
How you get Projects level ? Can you share query your are using?
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.
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.
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!