Getting Data In

Create Splunk Table from nested json output

niddhi
Explorer

Hi,

Can someone please help me create a Splunk Table from the below data:

 

{
   "cd":[
      {
         "cn":"cust-1",
         "s":"api",
         "ps":61,
         "fs":94,
         "es":142,
         "ud":[
            {
               "un":"user-0",
               "ps":61,
               "fs":94,
               "es":142,
               "ad":[
                  {
                     "at":"asset2",
                     "ps":61,
                     "fs":94,
                     "es":142,
                     "ttd":[
                        {
                           "tt":null,
                           "ps":61,
                           "fs":94,
                           "es":142
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         "cn":"cust-2",
         "s":"api",
         "ps":727,
         "fs":152,
         "es":26,
         "ud":[
            {
               "un":"user-6",
               "ps":725,
               "fs":149,
               "es":21,
               "ad":[
                  {
                     "at":"asset1",
                     "ps":722,
                     "fs":149,
                     "es":20,
                     "ttd":[
                        {
                           "tt":null,
                           "ps":722,
                           "fs":149,
                           "es":20
                        }
                     ]
                  },
                  {
                     "at":"asset2",
                     "ps":3,
                     "fs":0,
                     "es":1,
                     "ttd":[
                        {
                           "tt":null,
                           "ps":3,
                           "fs":0,
                           "es":1
                        }
                     ]
                  }
               ]
            },
            {
               "un":"user1",
               "ps":1,
               "fs":0,
               "es":0,
               "ad":[
                  {
                     "at":"asset1",
                     "ps":1,
                     "fs":0,
                     "es":0,
                     "ttd":[
                        {
                           "tt":null,
                           "ps":1,
                           "fs":0,
                           "es":0
                        }
                     ]
                  }
               ]
            },
            {
               "un":"user2",
               "ps":1,
               "fs":3,
               "es":5,
               "ad":[
                  {
                     "at":"asset2",
                     "ps":1,
                     "fs":3,
                     "es":5,
                     "ttd":[
                        {
                           "tt":null,
                           "ps":1,
                           "fs":3,
                           "es":5
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         "cn":"cust-3",
         "s":"api",
         "ps":0,
         "fs":1,
         "es":0,
         "ud":[
            {
               "un":"user-3",
               "ps":0,
               "fs":1,
               "es":0,
               "ad":[
                  {
                     "at":"asset2",
                     "ps":0,
                     "fs":1,
                     "es":0,
                     "ttd":[
                        {
                           "tt":null,
                           "ps":0,
                           "fs":1,
                           "es":0
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         "cn":"cust-4",
         "s":"api",
         "ps":1,
         "fs":4,
         "es":22,
         "ud":[
            {
               "un":"user-4",
               "ps":1,
               "fs":4,
               "es":22,
               "ad":[
                  {
                     "at":"asset1",
                     "ps":1,
                     "fs":4,
                     "es":22,
                     "ttd":[
                        {
                           "tt":null,
                           "ps":1,
                           "fs":4,
                           "es":22
                        }
                     ]
                  }
               ]
            }
         ]
      }
   ]
}

 

 

I want the output to be like:

cnunatttpsfses
cust-1

user-0

asset2

null6194142
cust-2

user-6

 

user1

user2

asset1

asset2

asset1

asset2

null

null

null

null

722

3

1

1

149

0

0

3

20

1

0

5

cust-3user-3asset2null010
cust4user-4asset1null1422

 

Thanks in advance.

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults | eval _raw="{
   \"cd\":[
      {
         \"cn\":\"cust-1\",
         \"s\":\"api\",
         \"ps\":61,
         \"fs\":94,
         \"es\":142,
         \"ud\":[
            {
               \"un\":\"user-0\",
               \"ps\":61,
               \"fs\":94,
               \"es\":142,
               \"ad\":[
                  {
                     \"at\":\"asset2\",
                     \"ps\":61,
                     \"fs\":94,
                     \"es\":142,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":61,
                           \"fs\":94,
                           \"es\":142
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         \"cn\":\"cust-2\",
         \"s\":\"api\",
         \"ps\":727,
         \"fs\":152,
         \"es\":26,
         \"ud\":[
            {
               \"un\":\"user-6\",
               \"ps\":725,
               \"fs\":149,
               \"es\":21,
               \"ad\":[
                  {
                     \"at\":\"asset1\",
                     \"ps\":722,
                     \"fs\":149,
                     \"es\":20,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":722,
                           \"fs\":149,
                           \"es\":20
                        }
                     ]
                  },
                  {
                     \"at\":\"asset2\",
                     \"ps\":3,
                     \"fs\":0,
                     \"es\":1,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":3,
                           \"fs\":0,
                           \"es\":1
                        }
                     ]
                  }
               ]
            },
            {
               \"un\":\"user1\",
               \"ps\":1,
               \"fs\":0,
               \"es\":0,
               \"ad\":[
                  {
                     \"at\":\"asset1\",
                     \"ps\":1,
                     \"fs\":0,
                     \"es\":0,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":1,
                           \"fs\":0,
                           \"es\":0
                        }
                     ]
                  }
               ]
            },
            {
               \"un\":\"user2\",
               \"ps\":1,
               \"fs\":3,
               \"es\":5,
               \"ad\":[
                  {
                     \"at\":\"asset2\",
                     \"ps\":1,
                     \"fs\":3,
                     \"es\":5,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":1,
                           \"fs\":3,
                           \"es\":5
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         \"cn\":\"cust-3\",
         \"s\":\"api\",
         \"ps\":0,
         \"fs\":1,
         \"es\":0,
         \"ud\":[
            {
               \"un\":\"user-3\",
               \"ps\":0,
               \"fs\":1,
               \"es\":0,
               \"ad\":[
                  {
                     \"at\":\"asset2\",
                     \"ps\":0,
                     \"fs\":1,
                     \"es\":0,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":0,
                           \"fs\":1,
                           \"es\":0
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         \"cn\":\"cust-4\",
         \"s\":\"api\",
         \"ps\":1,
         \"fs\":4,
         \"es\":22,
         \"ud\":[
            {
               \"un\":\"user-4\",
               \"ps\":1,
               \"fs\":4,
               \"es\":22,
               \"ad\":[
                  {
                     \"at\":\"asset1\",
                     \"ps\":1,
                     \"fs\":4,
                     \"es\":22,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":1,
                           \"fs\":4,
                           \"es\":22
                        }
                     ]
                  }
               ]
            }
         ]
      }
   ]
}"
| spath path=cd{} output=cd
| fields - _*
| mvexpand cd
| spath input=cd path=ud{} output=ud
| rex field=cd "\"cn\":\"(?<cn>[^\"]+)"
| fields - cd
| mvexpand ud
| spath input=ud path=ad{} output=ad
| rex field=ud "\"un\":\"(?<un>[^\"]+)"
| fields - ud
| mvexpand ad
| spath input=ad path=ttd{} output=ttd
| rex field=ad "\"at\":\"(?<at>[^\"]+)"
| fields - ad
| spath input=ttd
| fields - ttd
| table cn un at tt ps fs es

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults | eval _raw="{
   \"cd\":[
      {
         \"cn\":\"cust-1\",
         \"s\":\"api\",
         \"ps\":61,
         \"fs\":94,
         \"es\":142,
         \"ud\":[
            {
               \"un\":\"user-0\",
               \"ps\":61,
               \"fs\":94,
               \"es\":142,
               \"ad\":[
                  {
                     \"at\":\"asset2\",
                     \"ps\":61,
                     \"fs\":94,
                     \"es\":142,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":61,
                           \"fs\":94,
                           \"es\":142
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         \"cn\":\"cust-2\",
         \"s\":\"api\",
         \"ps\":727,
         \"fs\":152,
         \"es\":26,
         \"ud\":[
            {
               \"un\":\"user-6\",
               \"ps\":725,
               \"fs\":149,
               \"es\":21,
               \"ad\":[
                  {
                     \"at\":\"asset1\",
                     \"ps\":722,
                     \"fs\":149,
                     \"es\":20,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":722,
                           \"fs\":149,
                           \"es\":20
                        }
                     ]
                  },
                  {
                     \"at\":\"asset2\",
                     \"ps\":3,
                     \"fs\":0,
                     \"es\":1,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":3,
                           \"fs\":0,
                           \"es\":1
                        }
                     ]
                  }
               ]
            },
            {
               \"un\":\"user1\",
               \"ps\":1,
               \"fs\":0,
               \"es\":0,
               \"ad\":[
                  {
                     \"at\":\"asset1\",
                     \"ps\":1,
                     \"fs\":0,
                     \"es\":0,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":1,
                           \"fs\":0,
                           \"es\":0
                        }
                     ]
                  }
               ]
            },
            {
               \"un\":\"user2\",
               \"ps\":1,
               \"fs\":3,
               \"es\":5,
               \"ad\":[
                  {
                     \"at\":\"asset2\",
                     \"ps\":1,
                     \"fs\":3,
                     \"es\":5,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":1,
                           \"fs\":3,
                           \"es\":5
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         \"cn\":\"cust-3\",
         \"s\":\"api\",
         \"ps\":0,
         \"fs\":1,
         \"es\":0,
         \"ud\":[
            {
               \"un\":\"user-3\",
               \"ps\":0,
               \"fs\":1,
               \"es\":0,
               \"ad\":[
                  {
                     \"at\":\"asset2\",
                     \"ps\":0,
                     \"fs\":1,
                     \"es\":0,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":0,
                           \"fs\":1,
                           \"es\":0
                        }
                     ]
                  }
               ]
            }
         ]
      },
      {
         \"cn\":\"cust-4\",
         \"s\":\"api\",
         \"ps\":1,
         \"fs\":4,
         \"es\":22,
         \"ud\":[
            {
               \"un\":\"user-4\",
               \"ps\":1,
               \"fs\":4,
               \"es\":22,
               \"ad\":[
                  {
                     \"at\":\"asset1\",
                     \"ps\":1,
                     \"fs\":4,
                     \"es\":22,
                     \"ttd\":[
                        {
                           \"tt\":null,
                           \"ps\":1,
                           \"fs\":4,
                           \"es\":22
                        }
                     ]
                  }
               ]
            }
         ]
      }
   ]
}"
| spath path=cd{} output=cd
| fields - _*
| mvexpand cd
| spath input=cd path=ud{} output=ud
| rex field=cd "\"cn\":\"(?<cn>[^\"]+)"
| fields - cd
| mvexpand ud
| spath input=ud path=ad{} output=ad
| rex field=ud "\"un\":\"(?<un>[^\"]+)"
| fields - ud
| mvexpand ad
| spath input=ad path=ttd{} output=ttd
| rex field=ad "\"at\":\"(?<at>[^\"]+)"
| fields - ad
| spath input=ttd
| fields - ttd
| table cn un at tt ps fs es

niddhi
Explorer

Saved my day. This works perfectly. Thank you so much!!

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...