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 |
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")))
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
| where mvcount (mvfilter (match (recipient,"\.gmail\.com") OR match (recipient,"\.hotmail\.com") ) )>2
updated to add one more close parenthesis.
Error in 'where' command: The expression is malformed. Expected ).
Added one close paren.
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")))
"Error in 'where' command: The arguments to the 'mvfind' function are invalid."
That did it! Thanks!
Try the updated answer.