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?

splunker1981
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

woodcock
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

splunker1981
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

woodcock
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

splunker1981
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

somesoni2
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

splunker1981
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...