How to filter a multivalue field so it returns results containing 3 or more values?

[cm22486]

Hello, thanks in advance for the help. I'd like to filter a multivalue field to where it will only return results that contain 3 or more values. This is in regards to email querying.

I am trying to figure out when somebody's account has been phished, because when they are phished, the attacker keeps sending out gobs of spam to gmail and hotmail addresses.

My fields are _time, sender, sender_domain, recipient, and message_subject

The recipient field will have up to 100 recipients. I want it to only show results that have greater than 2 recipients, and the recipients have at least one @gmail.com address, or @hotmail.com address. Below is the search I use, but obviously needs work.

sourcetype=MSExchange:2013:MessageTracking |dedup sender,recipient,message_subject, message_id |table _time sender sender_domain recipient recipient_domain message_subject |sort -_time |

[woodcock]

Like this:

sourcetype=MSExchange:2013:MessageTracking
|dedup sender,recipient,message_subject, message_id
| where mvcount(recipient) >= 3 AND isnotnull(mvfilter(match(recipient, "@(?:gmail|hotmail)\.com$")))
| table _time sender sender_domain recipient recipient_domain message_subject

[DalJeanis]

| where mvcount (mvfilter (match (recipient,"\.gmail\.com") OR match (recipient,"\.hotmail\.com") ) )>2

---

updated to add one more close parenthesis.

[cm22486]

Error in 'where' command: The expression is malformed. Expected ).

[DalJeanis]

Added one close paren.

[somesoni2 · Accepted solution]

Try like this (assuming field recipient is mulitivalued field. You may not need the makemv command)
Updated

sourcetype=MSExchange:2013:MessageTracking |dedup sender,recipient,message_subject, message_id |table _time sender sender_domain recipient recipient_domain message_subject | makemv recipient
| where mvcount(recipient)>2 AND (isnotnull(mvfind(recipient,"\.gmail\.com")) OR isnotnull(mvfind(recipient,"\.hotmail\.com")))

[cm22486]

"Error in 'where' command: The arguments to the 'mvfind' function are invalid."

[cm22486]

That did it! Thanks!

[somesoni2]

Try the updated answer.