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

[gravi]

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?

[to4kawa]

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

[kamlesh_vaghela]

@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]

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.