Splunk Enterprise Security

Matching value from two multi-value field

twh1
Communicator

I am working with MS-Exchange data. I am taking recipient email value and matching with user lookup for other details. Same email have multiple matching values in lookup table. I want only matching records in same row, instead of repeating it.

Ex.: I have an email xyz@abc.com in log. I have 3 records matching in user lookup like below.

 email      first  last   id    type
xyz@abc.com  Ram   Singh  1001   T
xyz@abc.com  Ram   Singh  1042   C
xyz@abc.com  Ram   Singh  1063   T

I am using below line to match recipient value and get other details from lookup.

| stats values(recipient) as recipient count by _time sender 
| mvexpand recipient
| eval recipient=lower(recipient)
| lookup users email AS recipient OUTPUT id type first last

I am getting output like below.

sender       recipient     id    type    first  last 
abc@xyz.com  xyz@abc.com   1001   T      Ram   Singh
                           1042   C      
                           1063   T      

But I am expecting result like this, so that i can perform some conditional action.

sender       recipient     id    type    first  last 
abc@xyz.com  xyz@abc.com   1001   T      Ram   Singh
abc@xyz.com  xyz@abc.com   1042   C      Ram   Singh
abc@xyz.com  xyz@abc.com   1063   T      Ram   Singh 

If I am using mvexpand command, it's providing wrong output rows.

0 Karma
1 Solution

to4kawa
Ultra Champion
....
| stats values(recipient) as recipient count by _time sender 
| mvexpand recipient
| eval recipient=lower(recipient)
| lookup users email AS recipient OUTPUT id
| mvexpand id
| lookup users id OUTPUT type first last

If you provide sample jpg, more clearly.

View solution in original post

0 Karma

twh1
Communicator

Hi @to4kawa ,
I have multi value field not NULL value field. If i have only 1 multi-value field, I can use mvexpand and get the output. But I have multiple multi -value field, for which I need row with respective value.

I have made little change in output now. Hope this will bring more clarity to my question.

0 Karma

to4kawa
Ultra Champion
....
| stats values(recipient) as recipient count by _time sender 
| mvexpand recipient
| eval recipient=lower(recipient)
| lookup users email AS recipient OUTPUT id
| mvexpand id
| lookup users id OUTPUT type first last

If you provide sample jpg, more clearly.

0 Karma
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 ...