topic Re: How to get the top 10 max values for each field value? in Splunk Search

How to get the top 10 max values for each field value?

tjago11 — Tue, 07 Aug 2018 16:01:16 GMT

Trying to analyze some windows perfmon data. The data looks like this:
counter -> name of performance metric (ie. % Privileged Time)
instance -> name of process that has metric (ie. svchost.exe)
Value -> value of performance metric (ie. 75.00)

Looking for a way to find the top ten instances that have the highest value for each of the counters. I can easily do this for a single counter:

index=perfmon sourcetype=perfmon:process counter="% Privileged Time"
| fields counter, instance, Value
| stats max(Value) as counterInstanceMax by counter, instance
| sort 5 counter - counterInstanceMax

I can append all the individual counter results together but I'm looking for a search that does it for me. So this works but I hate it:

index=perfmon sourcetype=perfmon:process counter="% Privileged Time" 
| fields counter, instance, Value 
| stats max(Value) as counterInstanceMax by counter, instance 
| sort 5 counter - counterInstanceMax 
| append 
    [ search index=perfmon sourcetype=perfmon:process counter="% Processor Time" 
    | fields counter, instance, Value 
    | stats max(Value) as counterInstanceMax by counter, instance 
    | sort 5 counter - counterInstanceMax] 
| append 
    [ search index=perfmon sourcetype=perfmon:process counter="% User Time" 
    | fields counter, instance, Value 
    | stats max(Value) as counterInstanceMax by counter, instance 
    | sort 5 counter - counterInstanceMax]

Here are the results of the big append query, which is what I want but would prefer a different method:

1   % Privileged Time   Idle    100
2   % Privileged Time   _Total  100
3   % Privileged Time   ccSvcHst#1  100.00000000000000000
4   % Privileged Time   csrss#3 100
5   % Privileged Time   vmtoolsd    100
6   % Processor Time    Idle    100
7   % Processor Time    SCNotification  100.0000000000000000
8   % Processor Time    SmcGui  100.0000000000000000
9   % Processor Time    _Total  100
10  % Processor Time    ccSvcHst#1  100
11  % User Time ccSvcHst#1  100.0000000000000000
12  % User Time vmtoolsd    100.000000000000000000
13  % User Time _Total  60.6052378440438100
14  % User Time csrss#3 40.895846326026990
15  % User Time explorer    11.308819241280776000

Re: How to get the top 10 max values for each field value?

kmaron — Tue, 07 Aug 2018 20:26:13 GMT

have you tried just dropping the counter= from the search and let it do all of them?

 index=perfmon sourcetype=perfmon:process
 | fields counter, instance, Value
 | stats max(Value) as counterInstanceMax by counter, instance

that might be closer and you can go from there?

Re: How to get the top 10 max values for each field value?

tjago11 — Wed, 08 Aug 2018 17:08:24 GMT

That works but there are 100's of "instance" values which makes it hard to view the data in a table and impossible to view in a chart. Ideally I would get the top 10 counter + instance combinations.

Re: How to get the top 10 max values for each field value?

tjago11 — Wed, 08 Aug 2018 17:17:15 GMT

Ended up using two lists, one with the instance name and one with the value. Then used mvindex to trim them down to just the top ones:

index=perfmon sourcetype=perfmon:process host=SCUBIP10
| stats max(Value) as maxCounterInstance by counter, instance
| sort counter - maxCounterInstance
| stats list(instance) as instances, list(maxCounterInstance) as maxValues by counter
| eval topValue = mvindex(maxValues, 0, 4)
| eval topInstance = mvindex(instances, 0, 4)
| fields - instances, - maxValues

Here is a sample result:

% Privileged Time       Idle                100
                        _Total              100 
                        System              6.06
                        XDMService#1        3.03
                        WmiPrvSE#6          1.92
% Processor Time        Idle                100
                        _Total              100
                        ccSvcHst            33.33
                        XDMService#1        12.12
                        System              6.06

Works well, thanks.

Re: How to get the top 10 max values for each field value?

Amulya888 — Fri, 14 Dec 2018 00:14:39 GMT

How to add _time to the final output?