Join two logs in Splunk

[Athira]

hi ,

I wanted to search and save result as table from two log statements.
 one log statement using regex to extract "ORDERS"
and another log statement using regex to extract "ORDERS, UNIQUEID"

my requirement is to use the combine two log statements  on "ORDERS"  and pull the ORDER and UNIQUEID in table  .

I am using Join to combine the two log statements on "ORDERS" , but my splunk query not returning any results

[Athira]

above query produce results for all the ORDERS &UNIQUEID . my subquery fetches ORDERS & UNIQUEID

i am trying to match the ORDERS in subquery with the outer query, and result display should be  ORDERS & UNIQUEID.   the common field in two query i am using is ORDERS 

[gcusello]

Hi @Athira ,

my search correlates the results from both the searches usig ORDERS and displays ORDERS and UNIQUEID, whats missing?

Please share an example of data and results.

Ciao.

Giuseppe

[Athira]

hi @gcusello 

thanks for your inputs, i have some correction in my query.

in the outer query i am trying to pull  the ORDERS which is Not available .I need to match the ORDERS  which is Not available to with the ORDERS on Sub query. 

Result to be displayed  ORDERS  & UNIQUEID .  common field in two query is ORDERS 

Below is the query i am using 

index=source "status for : *  | "status for : * " AND "Not available"  | rex field=_raw "status for : (?<ORDERS>.*?)" | join ORDERS [search Message=Request for : * | rex field=_raw "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)" | rex field=_raw "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""] | table ORDERS UNIQUEID

[gcusello]

Hi @Athira ,

try to follow my approach using stats instead join applied to your conditions:

index=source "status for : *   "Not available"  
| rex "status for : (?<ORDERS>.*?)" 
| append [ search 
     Message="Request for : *" 
     | rex "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)" 
     | rex "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""] 
| stats values(UNIQUEID) AS UNIQUEID BY ORDERS

if you have more values for UNIQUEID and you want a row foreach one, you can add the statement | mvexpand UNIQUEID.

As I said, this solution has only one limit: the subsearch must return maximun 50,000 results.

Ciao.

Giuseppe

[Athira]

hi @gcusello  i tried your approach , i'm getting results  for all ORDERS.  i want only the ORDERS and UNIQUEID from subquery to be displayed  which matches the ORDERS (in the outer query)  those  Not available 

[gcusello]

Hi @Athira ,

you should check the presence in bothe the searches, something like this:

index=source "status for : *   "Not available"  
| rex "status for : (?<ORDERS>.*?)" 
| eval type="one"
| append [ search 
     Message="Request for : *" 
     | rex "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)" 
     | rex "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""
     | eval type="two" ] 
| stats 
     dc(type) AS type_count 
     values(UNIQUEID) AS UNIQUEID 
     BY ORDERS
| where type_count=2

Ciao.

Giuseppe

[Athira]

hi @gcusello 

I have shared the details, could you check 

[gcusello]

Hi @Athira ,

could you share your two searches?

in few words, to correlate events, you need to find a common key, sharing your searches, I could guide you in this.

Ciao.

Giuseppe

[Athira]

here is the splunk query i am trying to use, Common field in 2 query is ORDERS

index=source "status for : * " | rex field=_raw "status for : (?<ORDERS>.*?)" | join ORDERS [search Message=Request for : * | rex field=_raw "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)" | rex field=_raw "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""] | table ORDERS UNIQUEID

[gcusello]

Hi @Athira ,

please try this approach:

(index=source "status for : * ") OR "Message=Request for : *"
| rex field=_raw "status for : (?<ORDERS>.*?)" 
| rex field=_raw "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)" 
| rex field=_raw "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""] 
| stats count values(UNIQUEID) AS UNIQUEID BY ORDERS 

or

index=source "status for : * " 
| rex field=_raw "status for : (?<ORDERS>.*?)" 
| append [ search 
     Message=Request for : * 
     | rex field=_raw "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)" 
     | rex field=_raw "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""] 
| stats count values(UNIQUEID) AS UNIQUEID BY ORDERS 

The second solution has the limit of 50,000 results for the subsearch.

Ciao.

Giuseppe