topic Re: Determining the max value of a multivalue field containing comma delimited text and number in Splunk Search

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

mydog8it — Fri, 25 Sep 2020 15:40:47 GMT

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!

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

thambisetty — Fri, 25 Sep 2020 15:55:35 GMT

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.

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

ITWhisperer — Fri, 25 Sep 2020 16:02:53 GMT

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

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

mydog8it — Fri, 25 Sep 2020 18:56:31 GMT

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

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

mydog8it — Fri, 25 Sep 2020 18:58:01 GMT

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

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

mydog8it — Fri, 25 Sep 2020 18:59:29 GMT

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?

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

ITWhisperer — Fri, 25 Sep 2020 21:08:19 GMT

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

mydog8it — Fri, 25 Sep 2020 22:35:56 GMT

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

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

ITWhisperer — Fri, 25 Sep 2020 23:32:15 GMT

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

to4kawa — Sat, 26 Sep 2020 01:15:01 GMT

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

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

mydog8it — Sat, 26 Sep 2020 02:40:46 GMT

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

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.

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

mydog8it — Sat, 26 Sep 2020 02:43:23 GMT

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?

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

to4kawa — Sat, 26 Sep 2020 06:14:18 GMT

If digit is same, what's displayed?

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

ITWhisperer — Sat, 26 Sep 2020 08:38:14 GMT

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

gabriel_vasseur — Thu, 08 Sep 2022 09:45:33 GMT

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.