Create Splunk Table from nested json output

[niddhi]

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:

cn	un	at	tt	ps	fs	es
cust-1	user-0	asset2	null	61	94	142
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-3	user-3	asset2	null	0	1	0
cust4	user-4	asset1	null	1	4	22

Thanks in advance.

[ITWhisperer · Accepted solution]

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

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