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!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...