Splunk Search

Nested Json with two different set of formats. How to retrieve data

gravi
Explorer

Hi,

I have nested json with Payload and the payload values are not consistent .

First Format:

{
  Activity: Logger
   ActivityResult: success
   Application: TestApp
   CorrelationId: test_file.txt
   DestinationIp:
   Level: Info
   Logger: TestApp
   Message: { 
     Message: { 
       PayLoad: { 
         generated: 2019-11-27T17:28:55.134008
         report: { 
           created: 2019-11-27 17:28:55.133 UTC       
           failed: 0
           processed: 46
           succeed: 46
         }
       }
       Status: SUCCESS
     }
   }
   Protocol: TCP
   SourceIp:
   ThreadId:
   Timestamp: 2019-11-27 11:28:55.169200
   Type: App
   UserId:test
}

Second Format:

 {
      Activity: Logger
       ActivityResult: success
       Application: TestApp
       CorrelationId: test_file_1.txt
       DestinationIp:
       Level: Info
       Logger: TestApp
       Message: { 
         Message: { 
           PayLoad: { 
             generated: 2019-11-27T17:28:55.134008
             failed: 0
             processed: 46
             succeed: 46
           }
           Status: SUCCESS
         }
       }
       Protocol: TCP
       SourceIp:
       ThreadId:
       Timestamp: 2019-11-27 11:28:55.169200
       Type: App
       UserId:test
    }

How can i extract the fields failed, proccessd and succeed from them?

Each file will have either of the formats and I need all the files list with the counts.

Have tried stats with each of the spath and it gives values when used individually but not in a single query.

could you please help?

Tags (1)
0 Karma

to4kawa
Ultra Champion
| makeresults 
 | eval _raw="{
   \"Activity\": \"Logger\",
    \"ActivityResult\": \"success\",
    \"Application\": \"TestApp\",
    \"CorrelationId\": \"test_file.txt\",
    \"DestinationIp\": \"\",
    \"Level\": \"Info\",
    \"Logger\": \"TestApp\",
    \"Message\": {
      \"Message\": { 
        \"PayLoad\": { 
          \"generated\": \"2019-11-27T17:28:55.134008\",
          \"report\": { 
            \"created\": \"2019-11-27 17:28:55.133 UTC\",
            \"failed\": 0,
            \"processed\": 46,
            \"succeed\": 46 } },
        \"Status\": \"SUCCESS\" }  },
    \"Protocol\": \"TCP\",
    \"SourceIp\": \"\",
    \"ThreadId\": \"\",
    \"Timestamp\": \"2019-11-27 11:28:55.169200\",
    \"Type\": \"App\",
    \"UserId\": \"test\" }" 
 | append 
     [| makeresults 
     | eval _raw="{
         \"Activity\": \"Logger\",
         \"ActivityResult\": \"success\",
         \"Application\": \"TestApp\",
         \"CorrelationId\": \"test_file_1.txt\",
         \"DestinationIp\": \"\",
         \"Level\": \"Info\",
         \"Logger\": \"TestApp\",
         \"Message\": { 
           \"Message\": { 
             \"PayLoad\": { 
               \"generated\": \"2019-11-27T17:28:55.134008\",
               \"failed\": 0,
               \"processed\": 46,
               \"succeed\": 46 },
             \"Status\": \"SUCCESS\" }  },
         \"Protocol\": \"TCP\",
         \"SourceIp\": \"\",
         \"ThreadId\": \"\",
         \"Timestamp\": \"2019-11-27 11:28:55.169200\",
         \"Type\": \"App\",
         \"UserId\": \"test\"
              }"] 
 | spath path=Message.Message.PayLoad.report output=payload1 
 | spath path=Message.Message.PayLoad output=payload2
 | spath path=Message.Message.PayLoad.generated output=generated 
 | eval payload=coalesce(payload1,payload2) 
 | spath input=payload path=processed output=processed 
 | spath input=payload path=failed output=failed 
 | spath input=payload path=succeed output=succeed 
 | table generated processed succeed failed

Hi, spath is useful.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@gravi

Can you please try this?

YOUR_SEARCH | rename "Message.Message.PayLoad.report.*" as 1_*, "Message.Message.PayLoad.*" as 2_* 
| table 1_* 2_* 
| eval failed="",processed="",succeed="",t="" 
| foreach "1_*" 
    [ eval t="<<MATCHSTR>>",failed=if(t=="failed" AND failed=="",'<<FIELD>>',failed),processed=if(t=="processed" AND processed="",'<<FIELD>>',processed),succeed=if(t=="succeed" AND succeed="",'<<FIELD>>',succeed) ] 
| foreach "2_*" 
    [ eval t="<<MATCHSTR>>",failed=if(t="failed" AND isnull(failed),'<<FIELD>>',failed),processed=if(t=="processed" AND isnull(processed),'<<FIELD>>',processed),succeed=if(t=="succeed" AND isnull(succeed),'<<FIELD>>',succeed) ] 
| table failed processed succeed

Sample search:

| makeresults 
| eval _raw="{\"Activity\": \"Logger\",\"ActivityResult\": \"success\",\"Application\": \"TestApp\",\"CorrelationId\": \"test_file.txt\",\"DestinationIp\": \"\",\"Level\": \"Info\",\"Logger\": \"TestApp\",\"Message\": {\"Message\": { \"PayLoad\": {\"generated\": \"2019-11-27T17:28:55.134008\",\"report\": {\"created\": \"2019-11-27 17:28:55.133 UTC\",\"failed\": \"0\",\"processed\": \"46\",\"succeed\": \"46\"}}, \"Status\": \"SUCCESS\"}}, \"Protocol\": \"TCP\", \"SourceIp\": \"\", \"ThreadId\":\"\", \"Timestamp\": \"2019-11-27 11:28:55.169200\", \"Type\": \"App\", \"UserId\": \"test\"}" 
| append 
    [| makeresults 
    | eval _raw="{\"Activity\":\"Logger\",\"ActivityResult\":\"success\",\"Application\":\"TestApp\",\"CorrelationId\":\"test_file_1.txt\",\"DestinationIp\":\"\",\"Level\":\"Info\",\"Logger\":\"TestApp\",\"Message\":{ \"Message\": { \"PayLoad\": { \"generated\":\"2019-11-27T17:28:55.134008\",\"failed\":\"1\",\"processed\":\"47\",\"succeed\":\"46\"},\"Status\":\"SUCCESS\"}},\"Protocol\":\"TCP\",\"SourceIp\":\"\",\"ThreadId\":\"\",\"Timestamp\":\"2019-11-27 11:28:55.169200\",\"Type\":\"App\",\"UserId\":\"test\"}"
        ] 
| kv 
| rename "Message.Message.PayLoad.report.*" as 1_*, "Message.Message.PayLoad.*" as 2_* 
| table 1_* 2_* 
| eval failed="",processed="",succeed="",t="" 
| foreach "1_*" 
    [ eval t="<<MATCHSTR>>",failed=if(t=="failed" AND failed=="",'<<FIELD>>',failed),processed=if(t=="processed" AND processed="",'<<FIELD>>',processed),succeed=if(t=="succeed" AND succeed="",'<<FIELD>>',succeed) ] 
| foreach "2_*" 
    [ eval t="<<MATCHSTR>>",failed=if(t="failed" AND isnull(failed),'<<FIELD>>',failed),processed=if(t=="processed" AND isnull(processed),'<<FIELD>>',processed),succeed=if(t=="succeed" AND isnull(succeed),'<<FIELD>>',succeed) ] 
| table failed processed succeed

Thanks

Anantha123
Communicator

try this

basequery | spath |

you will have all fields from Json expanded . Use the proper field that represents failed , proccessd and succeed from the extracted fields .

Thanks
Anantha.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...