Splunk Search

How to find the first alphabetical value?

samwatson45
Path Finder

Hi,

I have a field which returns values in the following format:

10.6.3319.19
10.7.2113.33
10.6.179.135
10.7.2025.200
10.6.361.425
10.7.2025.102
(As just a few examples)

There are listed as strings, not numbers (which is fine). What I would like to do is return the highest value from this field, that is the first in terms of alphabetical/alphanumeric ordering.
If I use max(field) it returns the value 10.7.3043.92, when in alphabetical order this would be 10.7.3043.328.

How do I return the first/highest value in terms of alphabetical ordering?

Many thanks in advance.

0 Karma
1 Solution

maciep
Champion

So i think the suggestion from bearmomont was to use sort instead of max(), because it handles those values the way you want. You can do something similar to what eventstats max() with sort and streamstats...not pretty but seems to get the job done

base search
| sort - version
| streamstats count
| eval latest_release= case(count=1,version)
| filldown latest_release

But also if your version strings follow a standard format, then I think you should be able to pad it like you're trying as well and then use max(). You'll just to be sure you're padding every part that you need to. Meaning, you need to know how that version can vary with respect to the length of each segment.

View solution in original post

maciep
Champion

So i think the suggestion from bearmomont was to use sort instead of max(), because it handles those values the way you want. You can do something similar to what eventstats max() with sort and streamstats...not pretty but seems to get the job done

base search
| sort - version
| streamstats count
| eval latest_release= case(count=1,version)
| filldown latest_release

But also if your version strings follow a standard format, then I think you should be able to pad it like you're trying as well and then use max(). You'll just to be sure you're padding every part that you need to. Meaning, you need to know how that version can vary with respect to the length of each segment.

View solution in original post

samwatson45
Path Finder

Yes this works perfectly, thank you!

0 Karma

samwatson45
Path Finder

What I would like to do is to return the top value of the whole set, and eventually I will be adding a filter to start by 10.7.3*, 10.7.2*, 10.6.2*, etc, so that I can find the highest value for each particular subcategory too.
The reason from this is that once I find the highest value I need a list of the values in this field less than the highest value, so that I know which ones those are (they also have an associated name field).

I have a couple of attempts at it so far but nothing has worked. For example:

base search
| dedup name sortby - _time (This just ensures that the latest version for each name is shown)
|eval first=substr(version, 1, 10)
|eval last=substr(version, 11)
| eval version2=if(len(version)=13, version, first+"0"+last)
|eventstats max(version2) as latest_release
|eval Latest=if(version2=latest_release, "Yes", "No")
| table name version Latest

This was meant to correct for the fact some of them are different lengths. So here it would turn 10.7.3043.92 into 10.7.3043.092.

Does this clarify my question somewhat?

0 Karma

niketnilay
Legend

@samwatson45, converted this to comment as I feel u wanted to add further details rather than posting an answer.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

maciep
Champion

Interesting. I think sort is treating these like IPs. If I replace the "." with a "|", its sorts like max().

sort

By default, sort tries to automatically determine what it is sorting. If the field takes on numeric values, the collating sequence is numeric. If the field takes on IP address values, the collating sequence is for IPs. Otherwise, the collating sequence is in lexicographical order.

max()

Returns the maximum value of the field X. If the values of X are non-numeric, the maximum value is found using lexicographical ordering.

You can manually sort these fields and see how it handles them differently.

| stats count 
| eval b=split("10.6.3319.19,10.7.2025.33,10.6.179.135,10.7.2025.200,10.6.361.425,10.7.2025.102",",")
| mvexpand b
| eval c = replace(b,"\.","|")
| fields b,c
0 Karma

BearMormont
Path Finder

What are you trying to do with this value? Can't you just:

 | sort field
 | head 1
 | table field

richgalloway
SplunkTrust
SplunkTrust

That can be shortened to ... | sort 1 field | ....

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.