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
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
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!
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
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")) )
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"))
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")))