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!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...