Join two heavy tables

[chakheevav]

I have a bunch of events in one index. The events are divided by sourcetype, for example:

sourcetype=foo | fields from, to  (about 5 million events)
sourcetype=bar | fields from, to  (about 2 million events)

These searches return the results:

1. from=A , to=B

2. from=B , to=C

So the question is how to join the above results to get a table:

from   to
A      C
........
A{n}   C{k}

[jeff · Accepted solution]

As I understand it, your example from A to C through B (A -> B -> C)? Are A, B, and C always unique? I'm presuming there's something to build the transaction on... B if unique, B in combination with _time, or some transaction identifier...

sourcetype=foo OR sourcetype=bar 
| eval int=if(sourcetype==foo,to,from)
| eval f=case(sourcetype==foo,from)
| eval t=case(sourcetype==bar,to)
| transaction int
| fields f,int,t
| stats count by f,t

If I missed the mark post some more specific examples and I or someone else from the community may be able to give better guidance.

[chakheevav]

Thanks, jeff.

B is not unique but I can narrow span .. | transaction int maxspan=1d. I need to get just a fact of chain A->B->C existance, so I think the query you provided does the job. Thank you