Determining the max value of a multivalue field containing comma delimited text and number

[mydog8it]

I have a comma delimited multivalue field that contains text and a digit in each value pair that I am trying to find the maximum digit and return the text and digit to the results. 

My multivalue field contains the following values:

Linked to Historical Cyber Exploit,1
Historically Linked to Malware,1
Historically Linked to Penetration Testing Tools,1
Exploited in the Wild by Recently Active Malware,5

If I just do a simple max(fieldName) it returns the following:

Linked to Historical Cyber Exploit,1

Which seems to be based off of the alphabetic interpretation of 'max'. What I want to return is:

Exploited in the Wild by Recently Active Malware,5

I think I need to do an mvexpand() followed by a rex of the resulting field but I am at a loss of how to return anything other than a "5" instead of the whole line.

Thanks in advance for any help!

[gabriel_vasseur]

I had a similar issue where I needed to find the longest string in a multivalue, but I didn't want to mess with my results by using mvexpand and then gathering them back with a stats, because in some cases the multivalue could be empty. So I found this solution instead.

It's using the newish mvmap command to massage the multivalue and then the min/max statistical function that works with strings using alphabetical order. The use of printf ensures alphabetical and numerical order are the same.

| makeresults 
| eval test=split("abc,defgh,a,asdfasdfasdfasdf,igasfasd", ",")
| eval test2=mvmap(test, printf("%05d", len(test) ) . " - " . test)
| eval shortest=min(test2), longest=max(test2)
| eval shortest=replace(shortest, "^\d+ - ", "" ), longest=replace(longest, "^\d+ - ", "" )

Hope this helps.

[mydog8it]

I figured out a way to use makeresults to emulate my data, its ugly. If there is a better way to do it, I would love to hear that too! 

| makeresults 
| eval test="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,1;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,5" 
| eval test2="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test3= "Linked to Historical Cyber Exploit,1;Historically Linked to Malware,4;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test4="Linked to Historical Cyber Exploit,2;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,2" 
| table test* 
| transpose 
| table "row 1" 
| rename "row 1" as data 
| makemv delim=";" data

[ITWhisperer]

| makeresults 
| eval test="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,1;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,5" 
| eval test2="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test3= "Linked to Historical Cyber Exploit,1;Historically Linked to Malware,4;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test4="Linked to Historical Cyber Exploit,2;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,2" 
| table test* 
| transpose 
| table "row 1" 
| rename "row 1" as data 
| makemv delim=";" data
| streamstats count as row
| mvexpand data
| rex field=data ",(?<value>\d+)$"
| eventstats max(value) as largest by row
| where value=largest
| fields - largest row value

[mydog8it]

This returns the field values great, but as separate events. Do you know how to pull them back together as a mv field? 

[ITWhisperer · Accepted solution]

| makeresults 
| eval test="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,1;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,5" 
| eval test2="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test3= "Linked to Historical Cyber Exploit,1;Historically Linked to Malware,4;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test4="Linked to Historical Cyber Exploit,2;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,2" 
| table test* 
| transpose 
| table "row 1" 
| rename "row 1" as data 
| makemv delim=";" data
| streamstats count as row
| mvexpand data
| rex field=data ",(?<value>\d+)$"
| eventstats max(value) as largest by row
| where value=largest
| fields - largest value
| stats values(data) as data by row
| fields - row

[mydog8it]

This is great, except it is dropping the peripheral data in my real search due to the use of stats to recreate the multivalue field. Do you know how to recreate the field without the use of stats?

[ITWhisperer]

| makeresults 
| eval test="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,1;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,5" 
| eval test2="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test3= "Linked to Historical Cyber Exploit,1;Historically Linked to Malware,4;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test4="Linked to Historical Cyber Exploit,2;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,2" 
| table test* 
| transpose 
| table "row 1" 
| rename "row 1" as data 
| makemv delim=";" data
| streamstats count as row
| mvexpand data
| rex field=data ",(?<value>\d+)$"
| eventstats max(value) as largest by row
| where value=largest
| fields - largest value
| stats values(*) as * by row
| fields - row

[ITWhisperer]

| mvexpand yourfield
| rex field=yourfield ",(?<value>\d+)$"
| sort - value
| head 1

[mydog8it]

This is only returning one result out of all my events. I added some SPL to emulate my data, can you take another look?

[thambisetty]

Can you split and create two new fields.

use eventstats to get max digit by text.

and then Where condition to match digit with max digit.

let me know if you have problem of using split and mvexpand.

[mydog8it]

I tried to use this process but couldn't get it to work. I've added some SPL to emulate my data, can you give it another look?

[to4kawa]

index=_internal | head 1 | fields _raw _time 
| eval test="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,1;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,5" 
| appendpipe [eval test="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| appendpipe [eval test= "Linked to Historical Cyber Exploit,1;Historically Linked to Malware,4;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| appendpipe [eval test="Linked to Historical Cyber Exploit,2;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,2" ]]]
| makemv delim=";" test
| mvexpand test
| rename test as _raw
| rename COMMENT as "this is sample. from here, the logic." 
| rex "(?<num>\d+$)"
| eventstats max(num) as max_num
| stats values(eval(if(like(_raw,"%".max_num),_raw,NULL))) as result

[mydog8it]

I do not have the ability to search _indexes, so I adapted your solution to my makeresults method like this:

| makeresults 
| eval test="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,1;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,5" 
| eval test2="Linked to Historical Cyber Exploit,1;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test3= "Linked to Historical Cyber Exploit,1;Historically Linked to Malware,4;Historically Linked to Penetration Testing Tools,1;Exploited in the Wild by Recently Active Malware,4" 
| eval test4="Linked to Historical Cyber Exploit,2;Historically Linked to Malware,2;Historically Linked to Penetration Testing Tools,2" 
| table test* 
| transpose 
| table "row 1" 
| rename "row 1" as data 
| makemv delim=";" data
| mvexpand data
| rename data as _raw
| rename COMMENT as "this is sample. from here, the logic." 
| rex "(?<num>\d+$)"
| eventstats max(num) as max_num
| stats values(eval(if(like(_raw,"%".max_num),_raw,NULL))) as result

This solution identifies one field with the highest value, but does not return the value for each field. I should have updated my desired results, because my original description is incomplete. The results I would like to get from this dataset would look similar to this:

The problem I am facing now is the proposed solutions to recreate the multivalue field use stats which drops the other fields in my data that I didn't include in the example. 

[to4kawa]

If digit is same, what's displayed?