Splunk Enterprise

Recombining events after mvexpand

DATT
Explorer

Hello everybody,

I'm working on a query that does the following:

1. Pull records, mvexpand on a field named INTEL. This is a multi-value field that could have anywhere from 1 to 11 different values.

2. Once expanded, perform a lookup using INTEL to retrieve a field WEIGHT. A weight is assigned to each INTEL value, between 1 and 5.

3. After the lookup, collapse the split records back into one record. 

At first glance I figured I could do `... | mvexpand | lookup | mvcombine | nomv` but since the records are no longer identical because both INTEL and WEIGHT are different, I don't think I can use mvcombine anymore.

To Visually demonstrate the issue

IDINTEL
12345A, B, C, D

 

After mvexpand

IDINTEL
12345A
12345B
12345C
12345D

 

After Lookup

IDINTELWEIGHT
123456A1
123456B2
123456C3
123456D4

 

Ultimately, I would like to get back to this

IDINTELWEIGHT
123456A,B,C,D1,2,3,4

 

Any tips?

Labels (1)
Tags (1)
0 Karma
1 Solution

renjith_nair
Legend

@DATT ,
try using stats on those values

| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT 
| nomv INTEL
| nomv WEIGHT

Here is a run anywhere example . Add/remove your columns according to the requirements

| makeresults | fields - _time 
| eval INTEL="A B C D E" | makemv INTEL | mvexpand INTEL 
| streamstats count | eval WEIGHT=count | rename count as ID 
| makemv delim="," INTEL 
| rename comment as "Above is just data generation" 
| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT 
| nomv INTEL 
| nomv WEIGHT
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

0 Karma

renjith_nair
Legend

@DATT ,
try using stats on those values

| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT 
| nomv INTEL
| nomv WEIGHT

Here is a run anywhere example . Add/remove your columns according to the requirements

| makeresults | fields - _time 
| eval INTEL="A B C D E" | makemv INTEL | mvexpand INTEL 
| streamstats count | eval WEIGHT=count | rename count as ID 
| makemv delim="," INTEL 
| rename comment as "Above is just data generation" 
| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT 
| nomv INTEL 
| nomv WEIGHT
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

DATT
Explorer

This got me close enough to what I needed.  In my effort to streamline and reduce clutter I oversimplified the issue in my original post.  In any case though, thank you for the help!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

@renjith_nair is missing a by clause

| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT by ID
| nomv INTEL
| nomv WEIGHT
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...