Is it possible to get sum of Top 5 values per field?

[ISP8055]

Hi there, 

So, I have table with Server Names and their load values

Server Load capacity
G1      10
G1      80
G2      6
G2      25
G1      50
G3      15
G2      5  
G4      20
G5      30 

and so on...

Is there a way to get sum of top 3 fields by Server?

I can do that if I limit it to just one server by:

my search | search "Server"="G1" |  sort- Load | head 3  | stats sum(Load)

But I want to know for all servers to see which one is getting highest loads on average.

[kamlesh_vaghela]

@ISP8055 

Can you please try this?

YOUR_SEARCH
| sort - Server Load
| streamstats count by Server 
| where count<=3
| stats sum(Load) As Load  by Server

My Sample Search :

| makeresults count=100
| fields - _time
| eval Server="G".((random() % 5)+1)
| eval Load=random() % 100
| rename comment as "Upto now is for sample data only. Your search logic will start after this"
| sort - Server Load
| streamstats count by Server 
| where count<=3
| stats sum(Load) As Load  by Server

I hope this will help you.

 Thanks
KV


If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[ISP8055]

Sorry, I'm getting no results found using this syntax.

[ITWhisperer]

| sort Server -Load
| streamstats count by Server
| eval Load=if(count<=3,Load,null())
| stats sum(Load) as Load by Server

[bowesmana]

This example shows you how you can filter 3 events from each server, which follows your example search

| makeresults count=1000
| fields - _time
| eval Server="G".((random() % 5)+1)
| eval Load=random() % 100
| streamstats c global=f by Server
| where c<=3
| stats sum(Load) as Load by Server
| sort - Load

Like your example, it is not sorting any Load value so will just return the first 3 load figures for each server

If you want to find the highest 3 load values per server then you need to add in the sort command before streamstats, i.e.

| makeresults count=1000
| fields - _time
| eval Server="G".((random() % 5)+1)
| eval Load=random() % 100
| sort - Load
| streamstats c global=f by Server
| where c<=3
| stats sum(Load) as Load by Server
| sort - Load

and then of course if you want to take time into account and find the most recent, highest load values, then you would need them in time descending order (default after search).

Hopefully this helps. You can just paste these examples into a search window. They create 1000 random data points for 5 servers and then random Load values for each. 

[ISP8055]

| eval Server="G".((random() % 5)+1)
Will I need this syntax if my all of my actual server names don't start with G? What does this command do?


| eval Load=random() % 
Could you explain what does this syntax do?

[bowesmana]

This part of those searches

| makeresults count=1000
| fields - _time
| eval Server="G".((random() % 5)+1)
| eval Load=random() % 100

is just code to create 1000 random events with a field 'Server' that has one of 5 different values, in order to present a solution, as I do not have access to your data.