Splunk Enterprise

Recombining events after mvexpand

DATT
Path Finder

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
Path Finder

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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...