How to edit my search to find multiple averages from one field based based on the context provided by other fields?

[matthewjohnson]

When working with Windows performance counters, the Value field contains the interesting data for a given context. The context of Value is defined by other fields - the counter field for example.

What I'd like to do is something like this:

index="perfmon" collection="Free Disk Space" | stats avg(Value when counter == "% Free Space") as percent_free avg(Value when counter == "Free Megabytes") as mb_free by host

So I end up with something like this:

Host            | percent_free              | mb_free
--------------------------------------------------------
BigHost1        | 20                        | 3000
BigHost2        | 10                        | 1500
...

How do I accomplish this?

[somesoni2 · Accepted solution]

Try something like this

index="perfmon" collection="Free Disk Space" counter="% Free Space" OR counter="Free Megabytes" | chart avg(Value) over host by counter | rename "% Free Space" as percent_free "Free Megabytes" as mb_free

[matthewjohnson]

Exactly what I wanted - thank you!