Getting Data In

How to zip multiple cardinalities of nested json arrays?

cblanton
Communicator

We are trying to zip and expand several levels of nested json data. Here is an example of our json data. Below is an example of the desired output.

   {
     "level0": {
         "globalname": "TOP_A",
         "globalver": "1",
         "level1": { 
           "level2": [ 
               {
                "lvl2name": "LVL2A", 
                "warnings": { 
                    "totalcount": "26",
                    "rulebreakdown": [
                    { 
                      "rulecount": "2",
                      "rulename": "ruleA"
                    },
                    { 
                     "rulecount": "24",
                     "rulename": "ruleB"
                    }
                 ]
               }
              },

              {
                "lvl2name": "LVL2B", 
                "warnings": { 
                    "totalcount": 81,
                    "rulebreakdown": [
                    { 
                      "rulecount": "11",
                      "rulename": "ruleG"
                    },
                    { 
                     "rulecount": "67",
                     "rulename": "ruleR"
                    },
                    { 
                     "rulecount": "3",
                     "rulename": "ruleZ"
                    }
                 ]
               }
              }
           ]
          }
        }
    }

    {
      "level0": {
          "globalname": "TOP_D",
          "globalver": "1.5",
          "level1": { 
            "level2": [ 
                {
                 "lvl2name": "LVL6A", 
                 "warnings": { 
                     "totalcount": "2",
                     "rulebreakdown": [
                     { 
                       "rulecount": "2",
                       "rulename": "ruleAB"
                     }
                  ]
                }
               }

               {
                 "lvl2name": "LVL6D", 
                 "warnings": { 
                     "totalcount": "23",
                     "rulebreakdown": [
                     { 
                       "rulecount": "5",
                       "rulename": "ruleGG"
                     }
                     { 
                      "rulecount": "14",
                      "rulename": "ruleRG"
                    }
                    { 
                     "rulecount": "4",
                     "rulename": "ruleGZ"
                   }
                  ]
                }
               }
            ]
           }
         }
     }

This would be the desired output of these two events:

alt text

Labels (1)
0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval _raw="[{\"level0\":{\"globalname\":\"TOP_A\",\"globalver\":\"1\",\"level1\":{\"level2\":[{\"lvl2name\":\"LVL2A\",\"warnings\":{\"totalcount\":\"26\",\"rulebreakdown\":[{\"rulecount\":\"2\",\"rulename\":\"ruleA\"},{\"rulecount\":\"24\",\"rulename\":\"ruleB\"}]}},{\"lvl2name\":\"LVL2B\",\"warnings\":{\"totalcount\":81,\"rulebreakdown\":[{\"rulecount\":\"11\",\"rulename\":\"ruleG\"},{\"rulecount\":\"67\",\"rulename\":\"ruleR\"},{\"rulecount\":\"3\",\"rulename\":\"ruleZ\"}]}}]}}},{\"level0\":{\"globalname\":\"TOP_D\",\"globalver\":\"1.5\",\"level1\":{\"level2\":[{\"lvl2name\":\"LVL6A\",\"warnings\":{\"totalcount\":\"2\",\"rulebreakdown\":[{\"rulecount\":\"2\",\"rulename\":\"ruleAB\"}]}},{\"lvl2name\":\"LVL6D\",\"warnings\":{\"totalcount\":\"23\",\"rulebreakdown\":[{\"rulecount\":\"5\",\"rulename\":\"ruleGG\"},{\"rulecount\":\"14\",\"rulename\":\"ruleRG\"},{\"rulecount\":\"4\",\"rulename\":\"ruleGZ\"}]}}]}}}]"
| spath {} output=root
| stats count by root
| spath input=root level0.level1.level2{} output=level2
| stats values(root) as root by level2
| spath input=level2 warnings.rulebreakdown{} output=rulebreakdown
| mvexpand rulebreakdown
| spath input=level2 warnings.totalcount output=totalcount
| spath input=rulebreakdown
| spath input=root level0 output=level0
| spath input=level0
| fields - level* root rulebreakdown

That's a lot of work.

0 Karma
Get Updates on the Splunk Community!

Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

Customer Experience | Join the Customer Advisory Board!

Are you ready to take your Splunk journey to the next level? 🚀 We invite you to join our elite squad ...