Splunk Search

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

tjago11
Communicator

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
0 Karma
1 Solution

tjago11
Communicator

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.

View solution in original post

Amulya888
Explorer

How to add _time to the final output?

0 Karma

tjago11
Communicator

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.

kmaron
Motivator

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?

0 Karma

tjago11
Communicator

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.

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