Forum Discussion
Help me with these queries
Thank you a lot for all the answers, they really helped.
I made these two queries for the AVG:
Perf | where (CounterName == "% Committed Bytes In Use" or CounterName == "% Used Memory") | where CounterName == "% Committed Bytes In Use" | summarize AggregatedValue = avg(CounterValue) by Computer | sort by AggregatedValue asc
Perf
| where CounterName == "% Processor Time"
| summarize AggregatedValue = avg(CounterValue) by Computer
| sort by AggregatedValue desc
I know that in the OMS portal you can filter the results.
However, is there any way to combine these two queries into one and then filter them so I can only see the Computer objects that have both CPU and RAM % less than X value?
So if one Computer has less than 10% of CPU but more than 10% of RAM, it will not show, but if another computer has less than 10% of CPU AND less than 10% of RAM, it will show.
Thank you in advance.
MicrosoftHey Dante,
Great question. I think the most elegant way in this case is to use "join":
Perf
| where CounterName == "% Committed Bytes In Use"
| summarize average_committed_bytes_percent = avg(CounterValue) by Computer
| where average_committed_bytes_percent < 10
| join (Perf
| where CounterName == "% Processor Time"
| summarize average_cpu_percent = avg(CounterValue) by Computer
| where average_cpu_percent < 10
) on Computer
| project-away Computer1
This is in fact a combination of 2 queries - the first part retrieves perf records of computers with low committed bytes, the second part retrieves perf records of computers with low cpu usage. The join is defined to match "on Computer" so it would match by the computer names (the "project-away" part is just to remove a redundant column created by the join).
Side comment - your first example included 2 conditions:
Perf
| where (CounterName == "% Committed Bytes In Use" or CounterName == "% Used Memory")
| where CounterName == "% Committed Bytes In Use" | summarize ...
but as you can see the second condition makes the first one redundant, since it excludes all records that don't refer to 'committed bytes in use'.
HTH,
Noa