Got problem with mvexpand and its 500MB memory limit? Try a reverse stats join!

[ClubMed]

Hey,

I had discovered you can emulate the mvexpand function to avoid its limitation configured by the limits.conf 

You just have to stats by the multivalue field you were trying to mvexpand, like so:

| stats values(*) AS * by <multivalue_field>

That's it, (edit:) assuming each value is a unique value such as a unique identifier. You can make values unique using methods like foreach to pre-append a row-based number to each value, reverse join it, then use split and mvindex to remove the row numbers afterwards. (/Edit.)

Stats splits up <multivalue_field> into its individual rows, and the use of values(*) copies data across all rows.

As an added measure, you can make sure to avoid unnecessary _raw data to reduce memory use with an explicit fields just for it.

It was in my experience, it turned out using | fields _time, * trick does not actually remove every single Splunk internal fields. Removing _raw had to be explicit.

| fields _time, xxx, yyy, zzz, <multivalue_field>
| fields - _raw
| stats values(*) AS * by <multivalue_field>

The above strategy minimizes your search's disk space as much as possible before expanding the multivalue field.

[ITWhisperer]

This solution only works if all the values in the multivalue field are unique across all instances of the field. For example:

| makeresults count=10
| eval mv=mvrange(0,(random()%5)+1)
| streamstats count as row
| stats values(*) as * by mv

This produces only 5 events instead of between 10 and 50 events which mvexpand  of mv would have done

[ClubMed]

Test post. Wasn't able to post?

Edit: Okay, it works. Yes that is an caveat to bring up. Fortunately, you can use a foreach with an iterator to make each value in the multivalue unique. I'm thinking it is something like the following. I'm sure its not impossible to add a custom unique identifier to each value in mv field nonetheless.

| eval iterator=0
| foreach <multivalue_field>
[eval iterator=iterator+1, <<ITEM>>=iterator."-".<<ITEM>>]
``` Warning: Did not test this yet ```

Then you can perform the reverse stats join, and use split() and mvindex() to parse out your actual values without needing regex!

You are correct, I was indeed working with a multivalue of unique identifiers which is why it worked for me.