Average and Diff per host

[GadgetGeek]

Given I have multiple hosts, I'd like the host total within a bucketed time span, average of the totals across all hosts, and the percentage difference per host compared to the average. E.g.

For:
index=main "processed" | bucket _time span=5m | rex "processed\s(?[\d]+)\smessages" | eval processed=tonumber(NumProcessed) | stats sum(processed) by _time host

Giving:
_time | host1 | host2 | host3
2018-05-24 08:00:00 | 99 | 101 | 100
2018-05-24 08:05:00 | 100| 99| 101

I'd like to see:

_time               | host1 | host2 | host3 | Average | host1diff% | host2diff% | host3diff%
2018-05-24 08:00:00 | 99    | 101   | 100   | 100     | -x%        | +y%        | 0.0%
2018-05-24 08:05:00 | 100   | 99    | 101   | 100     | 0.0%       | -x%        | +y%

(whatever x and y would be...and the number of host diff columns will depend on the number of hosts returned in the search)

[niketn · Accepted solution]

@GadgetGeek, as per the details and sample data, please try the following run anywhere search. The command from | makeresults till | fields - data generate dummy data as per question. It uses foreach command to iterate through host columns to get count of hosts and calculate their difference %. The addtotals command is used to get the Total of Hosts for calculating average. Remaining part of the search can be plugged in to your existing search.

| makeresults 
| eval data="2018-05-24 08:00:00|99|101|100;2018-05-24 08:05:00|100|99|101" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="|" 
| eval _time=strptime(mvindex(data,0),"%Y-%m-%d %H:%M:%S"), host1=mvindex(data,1), host2=mvindex(data,2), host3=mvindex(data,3) 
| fields - data 
| rename "*" as "count*"
| rename "count_*" as "_*"
| addtotals row=t col=f labelfield=Total
| eval HostCount=0 
| foreach count* 
    [ eval HostCount=HostCount+1]
| eval Average=round(Total/HostCount,0) 
| foreach count* 
    [ eval "diff%<<MATCHSTR>>"=round(((Average-'<<FIELD>>')/Average)*100,0)]
| table _time "count*" "diff*"

Please try out and confirm!

[GadgetGeek]

My original statement wasn't quite right. The output from the stated query gives:

_time | host | sum(processed)
2018-05-24 08:00:00 | host1 | 99
2018-05-24 08:00:00 | host2 | 100

I should have put the following instead of the 'stats' command:

| chart sum(processed) over _time by host

The desired output IS as stated:
_time | host1 | host2 | host3 | Average | host1diff% | host2diff% | host3diff%

From your response, taking everything from (and including) "| fields - data" and adding 'Average' to the results seems to work.

1. What does the '| fields - data' do, and should I include it?
2. If one of the hosts has no events in the time bucket, zero is not assumed - giving blank entries and no diff %, how can I get this to work?

[niketn]

1) Yes fields - data was only for my run anywhere example not for your search. You can remove that.

2) Add following command after timechart command:

| fillnull value=0

[GadgetGeek]

Brilliant thanks.