Splunk Enterprise Security

Compare search using join and subsearch in 2 different indexes

SplunkNewbie18
New Member

Hi,

I've got 2 index logs to do a comparison with for emails. So in my mind is to use subsearch and join - but doesnt seems to work. 😞

Condition:
If more than 5 emails with the same sender was detected, these results will compare with the other sets of index logs. Field comparison is by subject. Only when more than 5 counts of same sender in index emailA and any of the subject matches to the ones in index emailB, will trigger an alert.

My search:
index=emailA
| stats values(subject) as subject count by sender
| where count > 5
| join subject [|search index=emailB
| table subject]
| table count, subject, sender

Can help to see whats wrong with my query? 😞

0 Karma

woodcock
Esteemed Legend

Like this:

index=emailA
| stats values(subject) AS subject count BY sender
| search (count > 5) AND [search index=emailB | stats count BY subject | table subject ]
| table count, subject, sender
0 Karma

to4kawa
Ultra Champion
index=emailA OR index=emailB
| streamstats dc(index) as dcSub by subject
| stats values(dcSub) as dcSub values(subject) as subject count by sender
| where count > 5 AND dcSub==2
| table count, subject, sender

Hi, How about it?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi SplunkNewbie18,
probably subject isn't a field useful for comparison because could be some space or char.
In addition: how many results have in subsearch? remember that there's the limit of 50,000 results in subsearches.

Ciao.
Giuseppe

0 Karma

SplunkNewbie18
New Member

Ohh we can only base on the subject as there's no other matching fields to compare with between the 2 index. I can confirm that the subsearch fields is less than 50000. On average abt less than 50 results.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi SplunkNewbie18,
let me understand: you want to display count, subject and sender that are from the first index and the first stats, so, why do you want the join?
Ciao.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...