How to find the first alphabetical value?

[samwatson45]

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.

[maciep · Accepted solution]

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.

[samwatson45]

Yes this works perfectly, thank you!

[samwatson45]

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?

[niketn]

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

[maciep]

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

[BearMormont]

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

 | sort field
 | head 1
 | table field

[richgalloway]

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