topic Re: How can I select the maximum X values in a field based on another field? in Splunk Search

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

paulkrier — Tue, 24 Jul 2018 16:54:15 GMT

I have a data set that looks like this:

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

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,

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

pradeepkumarg — Tue, 24 Jul 2018 17:26:39 GMT

 ...| sort X Y | dedup 3 X

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

thambisetty — Tue, 24 Jul 2018 17:36:26 GMT

...| sort X, - Y | dedup 3 X

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

woodcock — Tue, 24 Jul 2018 17:36:37 GMT

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

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

somesoni2 — Tue, 24 Jul 2018 17:51:39 GMT

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

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

thambisetty — Tue, 24 Jul 2018 17:56:36 GMT

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

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

paulkrier — Tue, 24 Jul 2018 18:02:34 GMT

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

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

paulkrier — Tue, 24 Jul 2018 18:04:17 GMT

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.

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

paulkrier — Tue, 24 Jul 2018 18:09:44 GMT

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

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

thambisetty — Tue, 24 Jul 2018 18:14:47 GMT

yes you are right.

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

woodcock — Tue, 24 Jul 2018 18:17:32 GMT

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))