simple left join returning too many events

[yuvaldo]

** edit: **

if i add dedup _time,clientip to the left (upper) search, i get 2580 events.

Hi, ive got this search:

host=tutorialdata _time=* clientip=*
| eval test1=0
| fields clientip _time test1
| join type=left clientip, _time
[ search host=tutorialdata _time=* clientip=*
| transaction clientip maxspan=6h
| eval test2=1
| fields clientip _time test2]
| eval testFinal= if(test2 == "1","1","0")
| stats sum(testFinal)

the search left to the join alone, returns 39532 events. the right one, alone, 2580.

i added the test1, 2, and testFinal to verify the results,  but if i run the whole search it sums up 3457 instead of 2580. whats going on? thanks 🙂

[tscroggins]

@yuvaldo 

This occurs because there are more than 2580 matching events in the left outer search.

For example, this:

sourcetype=access_combined_wcookie source=tutorialdata.zip:* clientip=71.192.86.205 earliest=1617832126 latest=1617832127

returns three events; however, this:

sourcetype=access_combined_wcookie source=tutorialdata.zip:* clientip=71.192.86.205 earliest=1617832126 latest=1617832127 
| transaction clientip maxspan=6h 

returns 1 event.

When you join the results, each event in the left outer search joins with the first matching event in the subsearch, and the total number of events remains the same, with each joined event containing the fields added by the subsearch.