Splunk Search
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

View solution in original post

Highlighted

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

Explorer

Perfect, just what I needed, thanks!

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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