How do you correlate one field between two sources, and then if they match, find value from another field from the second source type?

[luke222010]

I have:

sourcetype_a` and`sourcetype_b

Where one field message_ID exists in both source types.

I want to loop through each message_ID in sourcetype_a and look for it in sourcetype_b, then if it finds it, look for the value of field: result in sourcetype_b, and print out all where result=success.

Can anyone help explain how this can be achieved, please?

[bhavikbhalodia]

@luke222010,

You can try below query :

sourcetype="sourcetype_a"
|table msgID
|appendpipe
[|search sourcetype="sourcetype_b" result="success" |table result,msgID ]
|stats values(result) as result count by msgID
| where count=2
| table result

Thanks,
Bhavik

[gcusello]

Hi luke222010,
try something like this

index=my_index sourcetype=sourcetype_b [ search index=my_index sourcetype=sourcetype_a | fields message_ID ] result=access
| table _time message_ID result

in other words you use the message_IDs resulting from subsearch to filter the main search, then you can display results in a table (I displayed only _time, message_ID and result fields but you can display also other fields from the main search).

Bye.
Giuseppe

[renjith_nair]

@luke222010,

Give this a try

(sourcetype="sourcetype_a" OR sourcetype="sourcetype_b")
|eventstats dc(sourcetype) as c by message_ID |where c> 1 AND result="success"