Splunk Search

Extract data from JSON array

karlbosanquet
Path Finder

I have some data which is along the following format;

{"event":
     {
     "Timestamp":"2019-01-16 22:20:26.123"
     "Stream":"123456",
     "Id":"789",
     "Data": [
         {
        "name":"0",
            "rank":"1",
            "score":"34.56787"
         },
         {
        "name":"1",
            "rank":"3",
            "score":"4.74287"
         },
         {
        "name":"2",
            "rank":"2",
            "score":"12.54863"
         },
         {
        "name":"3",
            "rank":"4",
            "score":"1.78916"
         },

     ]
     }
}

We get around 800,000 of these per day and have around 50 data elements in each one.

I am trying to find the best way to return the top 2 rank name and score for each event, e.g.;

1_name = 0
1_score = 34.56787
2_name = 2
2_score = 12.54863

And another search to timechart all scores by name

0 Karma
1 Solution

mayurr98
Super Champion

Hi Can you try this

| makeresults 
| eval _raw = "{\"event\":
      {
      \"Timestamp\":\"2019-01-16 22:20:26.123\",
      \"Stream\":\"123456\",
      \"Id\":\"789\",
      \"Data\": [
          {
         \"name\":\"0\",
             \"rank\":\"1\",
             \"score\":\"34.56787\"
                  },
          {
         \"name\":\"1\",
             \"rank\":\"3\",
             \"score\":\"4.74287\"
                  },
          {
         \"name\":\"2\",
             \"rank\":\"2\",
             \"score\":\"12.54863\"
                  },
          {
         \"name\":\"3\",
             \"rank\":\"4\",
             \"score\":\"1.78916\"
                  },

      ]
      }
 }" 
| spath 
| rename event.Data{}.* as * event.Timestamp as Timestamp event.Id as Id event.Stream as Stream 
| eval data=mvzip(mvzip(name,rank),score) 
| mvexpand data 
| rex field=data "(?<name>[^\,]+)\,(?<rank>[^\,]+)\,(?<score>.*)" 
| table Timestamp Stream Id name rank score

Also try this for top 2 scores:

|stats values(score) as score by rank name 
|  sort 2 -score

let me know if this helps!

View solution in original post

0 Karma

mayurr98
Super Champion

Hi Can you try this

| makeresults 
| eval _raw = "{\"event\":
      {
      \"Timestamp\":\"2019-01-16 22:20:26.123\",
      \"Stream\":\"123456\",
      \"Id\":\"789\",
      \"Data\": [
          {
         \"name\":\"0\",
             \"rank\":\"1\",
             \"score\":\"34.56787\"
                  },
          {
         \"name\":\"1\",
             \"rank\":\"3\",
             \"score\":\"4.74287\"
                  },
          {
         \"name\":\"2\",
             \"rank\":\"2\",
             \"score\":\"12.54863\"
                  },
          {
         \"name\":\"3\",
             \"rank\":\"4\",
             \"score\":\"1.78916\"
                  },

      ]
      }
 }" 
| spath 
| rename event.Data{}.* as * event.Timestamp as Timestamp event.Id as Id event.Stream as Stream 
| eval data=mvzip(mvzip(name,rank),score) 
| mvexpand data 
| rex field=data "(?<name>[^\,]+)\,(?<rank>[^\,]+)\,(?<score>.*)" 
| table Timestamp Stream Id name rank score

Also try this for top 2 scores:

|stats values(score) as score by rank name 
|  sort 2 -score

let me know if this helps!

0 Karma

karlbosanquet
Path Finder

Expected output examples;

  • Search 1 (table timestamp Stream Id 1_name 1_value 2_name 2_value)

||Timestamp||Stream||Id||1_name||1_score||2_name||2_score||
|2019-01-16 22:20:26.123|123456|789|0|34.56787|2|12.54863|
|2019-01-16 22:21:28.567|123456|790|12|54.78797|46|34.82632|

  • Search 2 (timechart values(score) by name)

||Timestamp||0||1||2||3||4||5||
|2019-01-16 22:20:26.123|34.56787|4.74287|12.54863|1.78916|3.35732|
|2019-01-15 12:21:34.244|2.34689|12.45211|21.24852|14.72412|2.28342|

0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...