Splunk Search

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

user12345a_2
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
1 Solution

Richfez
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

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

user12345a_2
Explorer

Perfect, just what I needed, thanks!

0 Karma

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

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...