topic Re: how to get 1st 2nd and 3rd place results? - based on value of a field - not frequency. in Splunk Search

how to get 1st 2nd and 3rd place results? - based on value of a field - not frequency.

colineltringham — Thu, 30 Oct 2014 12:21:07 GMT

Hi,

can anyone help me change this MSSQL type problem, into something i can get from Splunk! :

if i have a table (Data) containing:

Server Counter Value
1 1 1
1 2 2
1 3 3
2 1 3
2 2 4
2 3 7
3 1 18
3 2 0
3 3 2

In MSSQL i would write:

SELECT * FROM ( SELECT * ,RN = ROW_NUMBER() OVER (PARTITION BY Counter ORDER BY Value DESC) FROM #DATA ) a WHERE RN<=2

RESULT:
Server Counter Value RN
3 1 18 1
2 1 3 2
2 2 4 1
1 2 2 2
1 3 3 2
2 3 7 1

Note - this only gives the first and second place - but the methodology is what i was showing. I can't find anything that allows groups and subsets of results to be returned from a search.

I'm not getting this data from SQL - it's just the only way i could think of to illustrate the problem. I'm looking to get a subset of perfmon values out of Splunk! based on what may be considered "badly performing machines" eg - 10 highest CPU percentages ,5 highest Disk Queues etc.

I'd prefer not to have to write separate searches for each counter also

Any ideas?

Thanks

Colin

Re: how to get 1st 2nd and 3rd place results? - based on value of a field - not frequency.

linu1988 — Thu, 30 Oct 2014 12:40:19 GMT

Hello ,
If you are looking for getting the high values or want to group them to find out the machines there are several ways to do it.

1st: This will give you the values with their counts and percentage in the whole dataset

source=CPU counter="% Proccessor Time" instance=_Total|top 5 Value by host

2nd: Use stats to get the values over a period of time

source=CPU counter="% Proccessor Time" instance=_Total|stats avg(Value) as Usage by host|sort 5 -Value, host

3rd: Use timechart to get the trend

source=CPU counter="% Proccessor Time" instance=_Total|timechart avg(Value) by host

Thanks,
L

Re: how to get 1st 2nd and 3rd place results? - based on value of a field - not frequency.

aholzer — Thu, 30 Oct 2014 12:53:58 GMT

@colineltringham focus on the last pipe of @linu1988's 2nd answer. Using the "sort" command with a numeric value specifies how many results you want after the sort occurs. That should be exactly what you are looking for.

Re: how to get 1st 2nd and 3rd place results? - based on value of a field - not frequency.

colineltringham — Thu, 30 Oct 2014 13:12:49 GMT

Hi,

Thanks for the suggestions, but it's not quite what i am after as it will only return 5 results total (the sort part). I'm after the 5 max values of each subset of data, similar to the results i listed in the SQL version of the problem, where i returned 2 values for each counter when there were 3 values for each in the source.

Thanks

Colin

Re: how to get 1st 2nd and 3rd place results? - based on value of a field - not frequency.

colineltringham — Thu, 30 Oct 2014 14:45:11 GMT

Hi,

I think i've found my own anser:

base search | dedup 5 object counter instance sortby object counter instance -Value

this gives 5 results per combination.

Thanks

Colin

Re: how to get 1st 2nd and 3rd place results? - based on value of a field - not frequency.

dave_vgc — Fri, 31 Oct 2014 13:44:59 GMT

Another solution my be something like

    base search | 
    sort server, -value |
    streamstats count as rank by server |
    where rank < 3 |
    table server counter value rank

I don't have sufficient data to run a meaningful comparison of the different methods, so don't know which is more performs better over a large dataset.