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
Legend

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
Legend

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!