Joining two index searches and print the common results

[parthiban]

Dear team,

I need to join the two-index search and print the common ID's count. The below mentioned two different index it work independently, both the index having same correlation_ID but different messages. So common correlation ID count for the both index need to print.

index = Test1  invoked_component="XXXX" "genesys" correlation_id="*" message="Successfully received"
| stats count by correlation_id

index = Test2  invoked_component="YYYY" correlation_id="*"
| where message IN ("Successfully created" , "Successfully updated")
| stats count by correlation_id

[parthiban]

index = Test1  invoked_component="XXXX" "genesys" correlation_id="*" message="Successfully received" [ search index = Test2  invoked_component="YYYY" correlation_id="*" message IN ("Successfully created" , "Successfully updated") | dedup correlation_id | fields correlation_id ]
| stats count by correlation_id

This query is working as expected, slightly I modified the query, Just I put Test 2 is a main search and Test 1 is sub search. 

Thanks for your support@gcusello 

[gcusello]

hi @parthiban ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello]

Hi @parthiban ,

good for you, see next time!

let me know if I can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[parthiban]

Hi @gcusello 
Thanks for your response,

I need to print count of Test 2 correletion_ID with comparing the test 1 results common correletion_ID, Here the sample results I mentioned. 

[gcusello]

Hi @parthiban 

let me understand: you said the you want to correlate the count of Correrlation_IDs in the two searches, is it correct?

I don't understand where are correlation_IDs in your results and what's the rule 

please try this:

(index = Test1  invoked_component="XXXX" "genesys" correlation_id="*" message="Successfully received") OR (index = Test2  invoked_component="YYYY" correlation_id="*" message IN ("Successfully created" , "Successfully updated"))
| stats 
   count(eval(index="Test1") AS Test1_count 
   count(eval(index="Test2") AS Test2_count
   count 
   BY correlation_id

in this way, you have the results of both searches for corre.atio_ID.

Ciao

Giuseppe

[parthiban]

Hi @gcusello 
the correlation_ID is a unique value for each record, and each record has distinct messages for each lambda.

Yes, I want to correlate both Test1 and Test2, but the result with the common correlation_ID is printed only in Test2. I have already shared the example.

the below mentioned query is not working throwing  error  mismatch quotes  and/or parenthesis error.

[gcusello]

Hi @parthiban ,

yes, sorry, I forgot a parenthesis in the eval command:

(index = Test1  invoked_component="XXXX" "genesys" correlation_id="*" message="Successfully received") OR (index = Test2  invoked_component="YYYY" correlation_id="*" message IN ("Successfully created" , "Successfully updated"))
| stats 
   count(eval(index="Test1")) AS Test1_count 
   count(eval(index="Test2")) AS Test2_count
   count 
   BY correlation_id

Ciao.

Giuseppe

[gcusello · Accepted solution]

hi @parthiban ,

do you only that to be sure that the correlation_ID of the first search contains only correlation_IDs of the second you can use a subsearc:

index = Test1  invoked_component="XXXX" "genesys" correlation_id="*" message="Successfully received" [ search index = Test2  invoked_component="YYYY" correlation_id="*" message IN ("Successfully created" , "Successfully updated") | dedup correlation_id | fields correlation_id ]
| stats count by correlation_id

this method work only if you have less than 50,000 results in the subsearch, otherwise you have to try something like this:

(index = Test1  invoked_component="XXXX" "genesys" correlation_id="*" message="Successfully received") OR (index = Test2  invoked_component="YYYY" correlation_id="*" message IN ("Successfully created" , "Successfully updated"))
| stats dc(index) AS index_count count by correlation_id
| where index_count=2

Ciao.

Giuseppe