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.

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

niketn
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...