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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...