How can I list full table entries when an extracted field has multiple values?

[user12345a_2]

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.

[Richfez · Accepted solution]

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.

[user12345a_2]

Perfect, just what I needed, thanks!

[somesoni2]

@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.