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!

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...