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!

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 ...