Splunk Search

How can I select the maximum X values in a field based on another field?

paulkrier
Engager

I have a data set that looks like this:

X     Y
1     5
1     4
1     3
1     2
1     1
2     10
2      9
2      8
2      4

I would like to select the maximum 3 values in Y for each value of X:

X     Y
1     5
1     4
1     3
2     10
2     9
2     8

I'm looking at sort and top, sort allows me to sort on each field, but the count argument seems to only work on the total number of results returned. Top is looking for the most common values, not the maximum values. Am I missing something?

Thanks,

pk

0 Karma
1 Solution

thambisetty
SplunkTrust
SplunkTrust
...| sort X, - Y | dedup 3 X
————————————
If this helps, give a like below.

View solution in original post

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval raw="1:5 1:4 1:3 1:2 1:1 2:10 2:9 2:8 2:4"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<X>[^:]+):(?<Y>[^:]+)$"
| table X Y

| rename COMMENT "Everything above generates sample event data; everything below is your solution"

| stats values(Y) AS Y BY X
| eval Y=mvindex(Y, -3, mvcount(Y))
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Just add sort 3 -Y by X to the end of your current search.

0 Karma

paulkrier
Engager

I don't think the sort command supports the by keyword. At least not in 6.5.4 which is what I am on.

0 Karma

thambisetty
SplunkTrust
SplunkTrust

sort by is not working, I had tried this actually.

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

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval raw="1:5 1:4 1:3 1:2 1:1 2:10 2:9 2:8 2:4"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<X>[^:]+):(?<Y>[^:]+)$"
| table X Y

| rename COMMENT "Everything above generates sample event data; everything below is your solution"

| top 3 Y BY X
0 Karma

paulkrier
Engager

top returns the most common values not the max values. If you add additional 2:4 to the test data then 2:4 replaces 2:8 in the results. Thanks though. The code to create the test table is really useful.

0 Karma

thambisetty
SplunkTrust
SplunkTrust

yes you are right.

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

thambisetty
SplunkTrust
SplunkTrust
...| sort X, - Y | dedup 3 X
————————————
If this helps, give a like below.

paulkrier
Engager

Brilliant! I didn't know dedup took the number of dups to keep. Thanks.

0 Karma

pradeepkumarg
Influencer
 ...| sort X Y | dedup 3 X 
0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...