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.
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
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
Awesome! Thanks for the detailed answer. This works perfectly.
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?
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