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