Good morning. So I have a search which generates a list of recipients for a particular message subject.
The search is as follows:
index="exchange*" message_subject="test message subject" | rex field=recipient_address "(?<my_recip>[a-zA-Z0-9._%+-]+)@(?<my_todomain>[a-zA-Z0-9.+]+[\.(a-zA-Z)+])" max_match=0| convert ctime(_time) as my_date timeformat=%m-%d-%y | convert ctime(_time) as my_hour timeformat=%H:%M | eval full_email=my_recip."@".my_todomain | table my_date, my_hour, message_subject, my_recip, my_todomain, full_email, sender_address | dedup sender_address, my_recip
In a given scenario, if user1 sends 3 messages:
Message 1: To: userA@this.org
Message 2: To: userB@this.org
Message 3: To: userC@this.org, userD@this.org, userE@this.org
The query returns the following results:
my_date | my_hour | message_subject | my_recip | my_todomain | full_email | sender_address
7-22-16 | 10:43 | test message | userA | this.org | userA@this.org | user1@this.org
7-22-16 | 10:44 | test message | userB | this.org | userA@this.org | user1@this.org
7-22-16 | 10:45 | test message | userC | this.org | userA@this.org | user1@this.org
userD
userE
What I’d like the query to return is:
my_date | my_hour | message_subject | my_recip | my_todomain | full_email | sender_address
7-22-16 | 10:43 | test message | userA | this.org | userA@this.org | user1@this.org
7-22-16 | 10:44 | test message | userB | this.org | userA@this.org | user1@this.org
7-22-16 | 10:45 | test message | userC | this.org | userA@this.org | user1@this.org
7-22-16 | 10:45 | test message | userD | this.org | userA@this.org | user1@this.org
7-22-16 | 10:45 | test message | userE | this.org | userA@this.org | user1@this.org
Is there an easy way to do this? Thanks in advance for any assistance.
Try adding mvexpand on your field name before all your make-pretty stuff.
index="exchange*" message_subject="test message subject"
| rex field=recipient_address "(?<my_recip>[a-zA-Z0-9._%+-]+)@(?<my_todomain>[a-zA-Z0-9.+]+[\.(a-zA-Z)+])" max_match=0
| mvexpand my_recip
| convert ctime(_time) as my_date timeformat=%m-%d-%y | convert ctime(_time) as my_hour timeformat=%H:%M
| eval full_email=my_recip."@".my_todomain
| table my_date, my_hour, message_subject, my_recip, my_todomain, full_email, sender_address
| dedup sender_address, my_recip
See it in the middle there? It might require a little tweaking - I'd suggest just running the search up to that point (the first three lines) and checking the resulting events look right, then add in the remaining stuff and check again.
Try adding mvexpand on your field name before all your make-pretty stuff.
index="exchange*" message_subject="test message subject"
| rex field=recipient_address "(?<my_recip>[a-zA-Z0-9._%+-]+)@(?<my_todomain>[a-zA-Z0-9.+]+[\.(a-zA-Z)+])" max_match=0
| mvexpand my_recip
| convert ctime(_time) as my_date timeformat=%m-%d-%y | convert ctime(_time) as my_hour timeformat=%H:%M
| eval full_email=my_recip."@".my_todomain
| table my_date, my_hour, message_subject, my_recip, my_todomain, full_email, sender_address
| dedup sender_address, my_recip
See it in the middle there? It might require a little tweaking - I'd suggest just running the search up to that point (the first three lines) and checking the resulting events look right, then add in the remaining stuff and check again.
Perfect, just what I needed, thanks!
@user12345a_2, Glad @rich7177's answer was helpful for you. Please close the question by accepting this answer and reward @rich7177 by upvoting his answer.