Need help on joining on multiple search

[bhaskar5428]

Hi , Please check above two screenshot , i want to join these queries in such way where i will get AppID along with coluns in first search query 

requirement is appid should come against order id from from first screen shot

pls suggest . . 

[bhaskar5428]

If you want i can share raw event for both queries

[richgalloway]

Please post the SPL as text rather than as screen shots.

It looks like the first search would become a subsearch within the second search.

[bhaskar5428]

index = app_events_sdda_core_de_prod source="/home/sdda/apps/logs/sep-app/app-json.log" level=TRACE
| fields message
| rex field=message "\"orderId\":\"(?<orderId>[^\"]+)\"},\"error\":\{\"errorCode\":\"(?<errorCode>[^\"]+)\""
| dedup orderId
| table orderId, errorCode

---------------------------------------------------------------------------------------------------------------------------------------

index = app_events_sdda_core_de_prod "Process transaction locally" b95d0d10-9709-4299-9d3e-8c65dd5a539d source="/home/sdda/apps/logs/sep-app/app-json.log"
|rex field=message "deliveringApplication=(?<AppID>\w+)"
|dedup AppID
|table AppID

Above order id i have added just for showcase purpose
actually i want SPl in such way that order id in my first SPL automatically get checks in 2nd 
and i will get three column.
Inner search kind of thing

pls help 

[gcusello]

Hi @bhaskar5428 ,

you need a correlation key that cannot be a simple string, in this case you need to extract this field:

in your case, the correlation key should be orderId, so you could run something like this:

index = app_events_sdda_core_de_prod source="/home/sdda/apps/logs/sep-app/app-json.log" level=TRACE
| rex field=message "\"orderId\":\"(?<orderId>[^\"]+)\"},\"error\":\{\"errorCode\":\"(?<errorCode>[^\"]+)\""
| fields orderId errorCode 
| dedup orderId
| table orderId, errorCode
| append [
index = app_events_sdda_core_de_prod "Process transaction locally" b95d0d10-9709-4299-9d3e-8c65dd5a539d source="/home/sdda/apps/logs/sep-app/app-json.log"
| rex field=message "deliveringApplication=(?<AppID>\w+)"
| rex "(?<orderId>\w{8}-\w{4}-\w{4}-\w{4}-\w{12})"
| dedup AppID
| table AppID orderId ]
| stats 
     values(errorCode) AS errorCode 
     values(AppID) AS AppID 
     BY orderId

This solution has only one limit: you must be sure that the second search will have less than 50,000 results, otherwise, you need a different solution

Ciao.

Giuseppe