Splunk Enterprise

Reverse the order of values for a multivalue field

ejwade
Contributor

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

Fumbles
Explorer

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

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
Explorer

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

0 Karma

rafaelsalazar
Path Finder

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?

0 Karma

ejwade
Contributor

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

0 Karma

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
Contributor

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
Contributor

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
Contributor

Awesome - thanks again for the info!

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Contributor

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
Get Updates on the Splunk Community!

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...

Thank You for Celebrating CX Day with Splunk!

Yesterday the entire team at Splunk &#43; Cisco joined the global celebration of CX Day - celebrating our ...