Getting Data In

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

ddelapasse
Explorer

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
Tags (2)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

rsantkumar
Observer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

ddelapasse
Explorer

@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!

0 Karma

ddelapasse
Explorer

@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

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 ...