Reverse the order of values for a multivalue field

[ejwade]

I'm trying to find a way to reverse the order of values for a multivalue field. Use the following SPL as the base search:

| makeresults 
    ``` Create string of characters, separated by comma ``` 
| eval mv_string = "banana,apple,orange,peach" 
    ``` Split string into multivalue, using comma as the delimiter ``` 
| eval mv_ascending = split(mv_string, ",")

My goal is to have a multivalue field that I can mvjoin() in this order:

"peach,orange,apple,banana"

In programming languages, like Python, you can use slicing to reverse the direction of a list (i.e., multivalue). However, it seems mvindex() is a watered down version of this. To my knowledge, this SPL function doesn't allow reversing the order. You can grab different index values with mvindex(), but it's always with the original list order. Anyone else come across this?

[Fumbles]

Posting the solution I have for Splunk 9.0+ using | foreach mode=multivalue. A lot of the solutions I saw sorted lexicographically but didn't reverse the order of a multivalue list OP specified. 

| makeresults 
| eval mv_string = "banana,apple,orange,peach", original_list= "banana,apple,orange,peach" 
| makemv delim="," mv_string
| makemv delim="," original_list
| foreach mode=multivalue mv_string [eval mv_count=mvcount(mv_string)-1, reversed_list=mvappend(reversed_list,mvindex(mv_string,-1)), mv_string=if(mvcount('mv_string')>=mv_count, mvappend(mvindex(mv_string,0,mv_count-1),''), mv_string)]
| table original_list reversed_list

[ITWhisperer]

Simpler to do this?

| makeresults 
| eval mv_string = "banana,apple,orange,peach", original_list= "banana,apple,orange,peach" 
| makemv delim="," mv_string
| makemv delim="," original_list
| foreach mode=multivalue mv_string [eval reversed_list=if(isnull(reversed_list),<<ITEM>>,mvappend(<<ITEM>>,reversed_list))]
| table original_list reversed_list

[Fumbles]

This is great, I had no idea you could swap <<ITEM>> and reversed_list in a mvappend(). Thanks for sharing!

[rafaelsalazar]

Those foreach solutions look nice. I wonder about the performance department tho, because foreach tends to be slow with big data. Have you tested the time and mem diff?

[ejwade]

@Fumblesthis is another great solution. Thank you for posting it!

[rafaelsalazar · Accepted solution]

So... here is a solution I came up today because I needed to reverse an mv field without mvexpanding it..

Its true that we at least need an mv sort descending, and in some cases like you point out, an mv reverse.. For now I think this would be easier with mvmap, but I only have 7.3 version.. 

Hope this helps.. 

| makeresults 
| eval mv_string = "banana,apple,orange,peach" 
| eval mv_ascending = split(mv_string, ",")
| eval id=mvrange(-2147483648,(2147483648-mvcount(mv_ascending))*(-1))
| rex field=id mode=sed "s/-//g"
| eval mv_descending=mvsort(mvzip(id,mv_ascending,"|"))
| rex field=mv_descending mode=sed "s/\d+[\r\n]*\|//g"

[ejwade]

Very impressive, @rafaelsalazar! This worked beautifully. I did have some questions (just out if curiosity):

• How did you come up with the number 2147483648, as your maximum counter? It appears to be 2^31.
• What validation led you to use mvdedup for the id field? It seemed to work without mvdedup, so I'm curious what happened in your testing to require this.

Thanks again!

[rafaelsalazar]

Glad this worked for you @ejwade !
Here my answers to your questions..

1.  Number -2147483648 is the minimum integer number.. but you don't need "that" exactly.. you just need a "big enough number" so that subtraction of the mvcount won't take a digit out.. this is critical, since the mvsort is a lexicographical sort and will work only if all the id's are the same length.
2. You are right, the mvdedup is not needed! Will update the query for that!

YW!

[ejwade]

Awesome - thanks again! I was talking about this with my colleague today, and we were trying to figure out the "[\r\n]*" in your second sed (rex). Since it matches zero or more, our results worked either way, but did you come across a situation where there was a carriage return or new line between the id an the pipe?

[rafaelsalazar]

Yeah! I built the MV from raw and stumbled into some unwanted new line characters at that step! Either was the mvzip or my MV was not clean enough!

[ejwade]

Awesome - thanks again for the info!

[PickleRick]

In general, I wouldn't trust the order of multivalued field too much and I wouldn't rely on it.

I suppose you could add a "counter" field (with streamstats), do mvexpand to split the field, add another "subcounter field", then sort reverse by the subcounter field an finally mvcombine back on this field but this is extremely ugly and inefficient (especially for a bigger result set)

[vhharanpositka]

| makeresults
| eval mv_string = "a,b,c,d"
| eval mv_ascending = split(mv_string, ",")
| mvexpand mv_ascending
| sort - mv_ascending
| table _time mv_ascending

[ejwade]

Hi @vhharanpositka.

Thank you for your reply. I updated the question to show that the values won't necessarily be lexicographically sorted. In other words, I want to reverse the order, and it won't always be lexicographically sorted ascending or descending.

[vhharanpositka]

Hi Ejwade

You can try this,

| makeresults
| eval mv_string = "a,b,c,d"
| eval mv_ascending = split(mv_string, ",")
| mvexpand mv_ascending
| sort - mv_ascending
| mvcombine mv_ascending