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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...