sum of average values based on two other columns

[stephen123]

hi, given the following data

time, hub, port, unique ip count

12:11:01 a 1 23

12:11:02 b 2 34

12:11:03 a 3 33

12:11:04 a 2 23

12:11:06 c 3 65

12:11:07 b 4 43

12:11:08 b 3 54

12:11:09 c 2 32

12:11:09 b 1 42

12:11:10 a 4 33

-- skipping all but a

12:11:15 a 1 43

12:11:34 a 2 64

12:11:39 a 3 43

12:11:50 a 4 32

I want to find the average of a1 to a4 per minute

so 122+182/2 =152 for 12:11

or

avg( (23+33+23+33) + (43+64+43+32) )

(note there will normally be more than two instances per minute and there can be any number of ports and hubs)

I also want to do this for b,c,d etc so I can plot them against each other over a given time period

or to put it another way the average number of unique ip address per hub per minute - even though the data only shows the unique ip per port

[martin_mueller · Accepted solution]

This should be possible with a two-step stat chain, something like this:

... | bucket span=1m _time | stats avg(unique ip count) as avg_uic by _time hub port | stats sum(avg_uic) as sum_uic by _time hub | xyseries _time hub sum_uic

That should first compute the average per hub-port combination bucketed per minute, and then sum up the averages per hub.