Using "bin" with dcount to get the distinct number of computers

[Samuel White]

I’ve recently started implementing OMS for my organization and I’ve been making decent progress thanks to the online docs. I was happy to come across this forum.

I’ve hit a block and was hoping if the community here can help out.

I have a decent working query to return a list of computers with a processor utilization over 90% over a 10 minute period.

It looks like this:

Perf
| where CounterName == "% Processor Time"
| summarize AggregatedValue = avg(CounterValue) by Computer, bin(TimeGenerated, 10m)
| sort by AggregatedValue desc
| where AggregatedValue > 90

What I’d like to do, is get a summary report of the number of computers using dcount that matches the above. My intention is to put this onto the “Overview” tile in a dashboard.

The closest I can get is a query like this… but the issue is that because this doesn’t “bin” the results into 10 minute increments as was done above, the number of computers doesn’t match. i.e. some computer pegged over 90% for a period shorter than 10 minutes.

Perf
| where (CounterName == "% Processor Time" and CounterValue  > 90)
| summarize AggregatedValue = dcount(Computer,3)

Does anyone have a suggestion on how to use “bin” with dcount in some way or perhaps some other solution?

Thanks

[#2 · Accepted solution]

Hi

I am not sure if I understand what exactly you want to achieve but wouldn't something like this be solution to your request:

Perf
| where CounterName == "% Processor Time"
| summarize AggregatedValue = avg(CounterValue) by Computer, bin(TimeGenerated, 10m)
| sort by AggregatedValue desc
| where AggregatedValue > 90 | summarize AggregatedValue = dcount(Computer)

If this is not what you want can you explain how you want to use dcount as the above uses both bin and dcount.

[#3]

Hi Stansislav and thanks for your help.

I hadn't thought to pipe the first results through summarize again, but that's exactly what I needed.

Just to explain a little clearer, what I am doing is creating a metric for my dashboard and I want the summary view (on the card) to be a simple number of how many things there are to investigate.

If I see a "0" then I know, nothing bad to see here... don't bother clicking though. 

-Samuel

[#4]

Hi

Glad that the proposed solution worked. I thought it obvious initially so I was wondering if I am wrong with my assumption.