How to correlate two searches with one field containing other index's field?

[sangs8788]

Hi,

I have two queries with one field being common to correlate and combine the result. But the problem i am facing is one index field is int value and other field is string with multiple int values comma separated.

Here is 1st source

index=dbquery_killed,  transactionid = 1234

2nd source

index=request_info , trans_id ="1234, 569, 890"

I want to combine these events and show all the related fields. Something with condition like 2nd source field containing 1st source field value, list all the related metrics from both indexes. How do i write a subquery to get this logic ?

Please let me know

[somesoni2]

Give this a try

index=dbquery_killed OR index= request_info 
| table index transactionid db_query killtime rundruation trans_id R Responsetime 
| eval transaction_id=coalesce(trans_id,transactionid)  | fields - transactionid trans_id 
| makemv transaction_id delim="," | mvexpand transaction_id
| stats values(*) as * by transaction_id 
| where mvcount(index)=2

[sangs8788]

No this doesnt result me any rows and takes a long time. Is there a way where we dont use mvexpand and just try contains functionality in splunk ?

[adonio]

what is the desired result of the query from the events above?

[sangs8788]

index=dbquery_killed, transactionid = 1234 | table db_query, killtime, runduration

index= request_info | table R, Responsetime., transactionid

combine above two queries and table out all the fields based on transaction id

[sangs8788]

to get the Request (Request_info) which triggered the dbquery(dbquery_killed) which got killed.