Splunk Search

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

mydog8it
Builder

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!

Labels (2)
1 Solution

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

View solution in original post

mydog8it
Builder

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

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

0 Karma

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

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?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
0 Karma

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

mydog8it
Builder

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

0 Karma

thambisetty
Super Champion

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.

————————————
If this helps, give a like below.
0 Karma

mydog8it
Builder

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?

0 Karma

to4kawa
Ultra Champion
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
Builder

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:

mydog8it_0-1601087846541.png

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. 

0 Karma

to4kawa
Ultra Champion

If digit is same, what's displayed?

0 Karma