Splunk Search

Alternative to mvexpand in order to count values in multi-value field?

sc0tt
Builder

I currently use mvexpand in order to count the number of unique values in a multi-value field. However, this field is becoming large with 100+ unique values and I only want to count a couple values. My current search is

source="log.txt" value1 OR value2 
| eval my_field = split(my_field, " ") | mvexpand my_field
| search my_field=value1 OR my_field=value2
| stats c by my_field

Is there a more efficient way to do this without using mvexpand? The current method is starting to take longer to run and I'm starting to receive the warning that results will be truncated due to excessive memory usage.

Tags (1)
0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

Hi sc0tt,

there is no need to run multiple search and/or join for this. You can use some mvfilter and mvcount magic. I will show you a run everywhere example:

index=_internal | head 1 | eval myfoo="foo bar boo foo far bar bar near not me but you" | makemv myfoo 
| eval foo=mvfilter(match(myfoo, "foo")) 
| eval bar=mvfilter(match(myfoo, "bar")) 
| eval foo_count=mvcount(foo) 
| eval bar_count=mvcount(bar) 
| table foo bar foo_count bar_count

as you can see I use a myfoo with multiple values in it. The mvfilter is used to filter for foo and bar and mvcount will count the values of each.

The first line

index=_internal | head 1 | eval myfoo="foo bar boo foo far bar bar near not me but you" | makemv myfoo

is only needed to build a multi valued field, so you don't need that in your real world search 😉

hope this helps to get you started ...

cheers, MuS

View solution in original post

MuS
SplunkTrust
SplunkTrust

Hi sc0tt,

there is no need to run multiple search and/or join for this. You can use some mvfilter and mvcount magic. I will show you a run everywhere example:

index=_internal | head 1 | eval myfoo="foo bar boo foo far bar bar near not me but you" | makemv myfoo 
| eval foo=mvfilter(match(myfoo, "foo")) 
| eval bar=mvfilter(match(myfoo, "bar")) 
| eval foo_count=mvcount(foo) 
| eval bar_count=mvcount(bar) 
| table foo bar foo_count bar_count

as you can see I use a myfoo with multiple values in it. The mvfilter is used to filter for foo and bar and mvcount will count the values of each.

The first line

index=_internal | head 1 | eval myfoo="foo bar boo foo far bar bar near not me but you" | makemv myfoo

is only needed to build a multi valued field, so you don't need that in your real world search 😉

hope this helps to get you started ...

cheers, MuS

sc0tt
Builder

Awesome! Thanks for the detailed answer. This works perfectly.

spammenot66
Contributor

is there a way to handle scenarios like this when using a data model/pivot table? I have a field (group) which contains multiple values delimited by |. In many of my dashboards, I need to run a unique count of the group as part of a larger set such as number of unique groups by states, page, market,etc.. how would i use makemv so that unique counts from the pivot will not be unique combinations of a value?

0 Karma

linu1988
Champion

it seems like you have space delimited value field. Why don't you use regex to extract value rather than using mvexpand? Use two differnt searches to get the count instead of single search and join them

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