Splunk Enterprise

Reverse the order of values for a multivalue field

ejwade
Communicator

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?

Labels (1)
Tags (1)
0 Karma
1 Solution

rafaelsalazar
Path Finder

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"

 

 

 

 

 

View solution in original post

rafaelsalazar
Path Finder

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
Communicator

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!

0 Karma

rafaelsalazar
Path Finder

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
Communicator

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?

0 Karma

rafaelsalazar
Path Finder

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
Communicator

Awesome - thanks again for the info!

0 Karma

PickleRick
Ultra Champion

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)

0 Karma

vhharanpositka
Path Finder

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

0 Karma

ejwade
Communicator

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

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

 

Tags (1)
0 Karma