Depending on the volume of data and other factors (ie lazy quotient) I might look at a join but only really if you are looking to get the avg duration per group and not per group and status. My guess is though what you might really want is the avg duration per status code though right? It might look something similar to this which is the avg and max memory usage by system over a series of weeks - assuming the table thing works ok
host | week of 10/04/15 | week of 10/18/15 | etc
system1 avg: 14.5 % avg: 21.0 %
max: 17.3 % max: 22.5 %
system2 avg: 64.6% avg: 67.1%
max: 65.9% max: 71.0%
If that is the case the query looks like this - at least for my example. You'd need to translate
sourcetype="Perfmon:Memory" counter="% Committed Bytes In Use" earliest=-4w@w latest=@w | bin span=1w _time | eval time = "Week of " .strftime(_time, "%m/%d/%y") | eval host=upper(host) | stats avg(Value) as avg max(Value) as max by host time | foreach avg max [eval <<FIELD>> = "<<FIELD>>: " .round(<<FIELD>>,1). " %"] | eval val = avg . "|" .max | makemv val delim="|" | xyseries host time val
The part to pick up on is at the stats command where I'm first getting a line per host and week with the avg and max values. The foreach bit adds the % sign instead of using 2 evals. Then I'm creating a new field to merge the avg and max values BUT with a delimiter to use to turn around and make it a multivalue field (so that the results show up on 2 lines). Then I'm using the xyseries command that I referenced above.
... View more
