topic Re: Need help on joining on multiple search in Reporting

Need help on joining on multiple search

bhaskar5428 — Wed, 17 Jul 2024 07:45:48 GMT

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

Re: Need help on joining on multiple search

richgalloway — Wed, 17 Jul 2024 12:26:53 GMT

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.

Re: Need help on joining on multiple search

bhaskar5428 — Thu, 18 Jul 2024 04:25:09 GMT

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

Re: Need help on joining on multiple search

bhaskar5428 — Thu, 18 Jul 2024 04:25:55 GMT

If you want i can share raw event for both queries

Re: Need help on joining on multiple search

gcusello — Thu, 18 Jul 2024 06:07:11 GMT

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