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!

Enterprise Security Content Update (ESCU) | New Releases

In January, the Splunk Threat Research Team had one release of new security content via the Splunk ES Content ...

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...