Splunk Search

simple left join returning too many events

yuvaldo
Engager

** 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 :slightly_smiling_face:

Labels (1)
0 Karma

tscroggins
Influencer

@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.

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...