Splunk table with nested JSON - print parent item with each child item

[ddelapasse]

I'm a newbie and I know this should be super easy, but I can't create a table with separate rows (events) for each combination of project name + task. Given the JSON below, I'm trying to create a table with each task in each project -- so I should have 4 entries. Instead, I'm getting the 2 projects each with 1 task (screenshot attached).

Strangely, I got this example from another forum post where people seemed to believe it was behaving correctly. Here's what I want:

ProjectName                  TaskName
Build Computer             Order Hardware
Build Computer             Install Software
Submit Timesheet           Fill out Timesheet
Submit Timesheet           Email Timesheet

Orig Json

 {
   "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"
         }
       ]
     }
   ]
 }

Here's the "answer" from the original post:

| makeresults
 | eval _raw="{\"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\"}]}]}" | 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

^which returns the wrong answer (but closer than I was getting with spath):

    Build Computer             Order Hardware
    Submit Timesheet          Install Software

[rsantkumar]

Hi @ddelapasse

were you able to get a solution to this problem? I am breaking my head with a similar issue.

My json output looks like this:
{
"plugins": [
{
"key": "aemrules",
"name": "AEM Rules for SonarQube",
"category": "External Analysers",
"description": "Adds rules for AEM Java development",
"license": "The Apache Software License, Version 2.0",
"organizationName": "Cognifide Limited",
"organizationUrl": "http://www.cognifide.com",
"homepageUrl": "https://github.com/Cognifide/AEM-Rules-for-SonarQube",
"issueTrackerUrl": "https://github.com/Cognifide/AEM-Rules-for-SonarQube/issues",
"editionBundled": false,
"updates": [
{
"release": {
"version": "1.0",
"date": "2020-02-07",
"description": "HTL Support",
"changeLogUrl": "https://github.com/Cognifide/AEM-Rules-for-SonarQube/releases/tag/v1.0"
},
"status": "COMPATIBLE",
"requires": [
{
"key": "java",
"name": "Java Code Quality and Security",
"description": "Code Analyzer for Java"
}
]
},
{
"release": {
"version": "1.1",
"date": "2020-04-02",
"description": "Java Plugin 6.2 support",
"changeLogUrl": "https://github.com/Cognifide/AEM-Rules-for-SonarQube/releases/tag/v1.1"
},
"status": "COMPATIBLE",
"requires": [
{
"key": "java",
"name": "Java Code Quality and Security",
"description": "Code Analyzer for Java"
}
]
}
]
}

each parent "key" has multiple child "releases" for which i need the corresponding "version" and "date"

output format: key version date

No matter what i do, i am unable to print the key (repeatedly for each release/version) in a table .The results get truncated to the exact number of keys. However when i print just version and date, all versions and dates get printed. Seems like i am unable to pair the "key" with multiple "versions" and "dates". Please help.

I am trying to use the query:

index=edot_sonarqube_prod sourcetype="sonarqube-ceLogs-splunk" "/api/plugins/updates"|head 1
|eval json_field = _raw
| spath input=json_field path=data.plugins{}.key output=key | spath input=json_field path=data.plugins{}.updates{}.release{}.version output=version|spath input=json_field path=data.plugins{}.updates{}.release{}.date output=date
| eval test=mvzip(key,version)|mvexpand test|eval test1=mvzip(test,date)
| mvexpand test1
| eval test1 = split(test1,",") | eval key=mvindex(test1,0) | eval version=mvindex(test1,1) | eval date=mvindex(test1,2) | table key version date|sort key

[richgalloway]

@rsantkumar This question has an accepted answer. If the solution does not work for you then please post a new question.

[kamlesh_vaghela · Accepted solution]

Hi @ddelapasse,

Can you please try this?

YOUR_SEARCH | spath path=Projects{} output=Projects | mvexpand Projects | rename Projects as _raw | kv | rename Tasks{}.TaskName as TaskName | mvexpand TaskName | table ProjectName TaskName

Sample Search:

| makeresults 
| eval _raw="{\"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\"}]}]}" 
| spath path=Projects{} output=Projects | mvexpand Projects | rename Projects as _raw | kv | rename Tasks{}.TaskName as TaskName | mvexpand TaskName | table ProjectName TaskName

Thanks

[ddelapasse]

@kamlesh_vaghela, thank you SO much! I found 3 different examples similar to the one I posted but none of them worked for me. I guess maybe something changed between v6 and v7? I had not seen the kv command. Heading to read the docs right now.

thanks again!

[ddelapasse]

@kamlesh_vaghela I did find a problem with this solution when I try to extract the task due date (1:1 with task name) as well. Is there a way to make it mvexpand on the Task object rather than a specific attribute? I tried this but then I was only getting 1 ProjectName with 2 task names/dates which won't work -- I need a full row for each task.

Example:
| makeresults
| eval _raw="{\"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\"}]}]}"
| spath path=Projects{} output=Projects | mvexpand Projects | rename Projects as _raw | kv | rename Tasks{}.TaskName as TaskName Tasks{}.TaskDueDate as TaskDueDate | mvexpand TaskName | table ProjectName TaskName TaskDueDate