Splunk Search

How to edit my correlation search to find any messages where two recipients I define received the same message from the same sender?

Path Finder

Hello Splunk Experts

I am pretty new to Splunk and hoping someone can help me figure out how to do the following. I would like to find any messages where two recipients I define received the same message and where the sender and message subject were the same. I'm assuming I have to do some sort of field comparison on subject or distinct and where recipient=user1 AND user2. Below is my base search and I've tried various variations of WHERE statements, but I can never seem to get the output where the users I define are the recipients of both messages.

 filter on user1 OR user2 | stats values(subject) by sender, recipient

Sample data
user1 subject=test sender=user100
user2 subject=test sender=user100
user1 subject=abc sender=user99
user2 subject=test sender=user98
user1 subject=abc sender=user100
user2 subject=abc sender=user100
user2 subject=abc sender=user99
user1 subject=abc sender=user337

Results should be as follows

Sender    subject    recipient 
user100   test       user1
                     user2

user99    abc        user1
                     user2

user100   abc        user1
                     user2
Tags (2)
0 Karma

Esteemed Legend

Like this:

|noop|stats count AS raw|eval raw="user1 subject=test sender=user100:::
 user2 subject=test sender=user100:::
 user1 subject=abc sender=user99:::
 user2 subject=test sender=user98:::
 user1 subject=abc sender=user100:::
 user2 subject=abc sender=user100:::
 user2 subject=abc sender=user99:::
 user1 subject=abc sender=user337" | makemv delim=":::" raw
| mvexpand raw
| rex field=raw "(?<recipient>.*)\s+subject=(?<subject>.*)\s+sender=(?<sender>.*)" | fields - raw
| rename COMMENT AS "The above is setup, the below is solution"
| stats count values(*) AS * BY sender subject
0 Karma

Path Finder

Thanks for the quick reply guys, appreciate it. The problem I encounter is the same I've been having however. If other recipients received the same message the count of where mvcount>1 will always match, but both the recipients I need to be in that list aren't always there. I am looking for something like where values(recipient) = (user1 AND user2). If user1 AND user2 aren't in the values(recipient) list, although there's multiple recipients, I'd like to discard those. Thanks for help in advance!

0 Karma

Esteemed Legend

I didn't add the final step because I thought it was obvious and that you would like to see the intermediate data. In any case, add this to my solution to match your stated desired results exactly:

... | search count>1
0 Karma

Path Finder

Thanks for the reply. No go, that wouldn't do it in this case. See my above response. The issue is that my results is a mix of multivalues and single so doing >1 will be true a lot of the times. Trying to only show results where (user1 AND user2) exist within the the same subject + recipient field. Still haven't been able to figure out how to filter results where both users exist in the recipient field. A sample of the current multivalue results shown in my earlier post of reference.

Here is what I tried doing, but for some reason I get results when one user exists and the other doesn't. Not sure why the AND statement is ruling those out.

 |makemv delim=";" recipient 
 | stats values(recipient) as recipient by sender, subject 
 | where mvcount(recipient)>1 AND (isnotnull(match(recipient,"user1")) AND  isnotnull(match(recipient,"user2")) )
0 Karma

SplunkTrust
SplunkTrust

Try like this

 filter on user1 OR user2 | stats values(recipient) as recipient by sender, subject | where mvcount(recipient)>1

Updated
Give this a try

filter on user1 OR user2 | stats values(recipient) as recipient by sender, subject | where isnotnull(match(recipient,"user1")) AND  isnotnull(match(recipient,"user2")) 
0 Karma

Path Finder

Didn't work. I still get results where BOTH user1 AND user2 are NOT in the recipient field

The results appear to be a mix of multivalue and single values. I think the matching is not working on the multiple value where they are separated by a ";" For example

Sender    subject    recipient 
user100   test       user1;user3;user4;user5;user10;user20;user30
                     user2

 user99    abc       user1
                     user1;user2

 user100   abc       user1;user100;user20;user30;user50;user70
                     user2

I tried adding the following but still no go.

|makemv delim=";" recipient 
| stats values(recipient) as recipient by sender, subject 
| where mvcount(recipient)>1 AND (isnotnull(match(recipient,"user1")) AND  isnotnull(match(recipient,"user2")))
0 Karma