Splunk Search

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

Communicator

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

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Communicator

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 ?

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Communicator

index=dbquerykilled, transactionid = 1234 | table dbquery, killtime, runduration

index= request_info | table R, Responsetime., transactionid

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

0 Karma

Communicator

to get the Request (Requestinfo) which triggered the dbquery(dbquerykilled) which got killed.

0 Karma