Nov 03 2019
06:59 PM
- last edited on
Apr 08 2022
10:11 AM
by
TechCommunityAP
Nov 03 2019
06:59 PM
- last edited on
Apr 08 2022
10:11 AM
by
TechCommunityAP
Hi
I am trying to get the count of unique RoleInstance per 10 min
MetricsLogs
| distinct RoleInstance
| summarize TimeGenerated, AggregatedValue = count(RoleInstance) by bin(TimeGenerated, 10m)
It does not work with the following error 'summarize' operator: Failed to resolve scalar expression named 'TimeGenerated'
Then, I tried this, but it returns distinct rows.
MetricsLogs
| distinct RoleInstance, TimeGenerated
| summarize TimeGenerated, AggregatedValue = count(RoleInstance) by bin(TimeGenerated, 10m)
Sorry, I am sure it is easy to fix :(
Nov 04 2019 01:20 AM
Solution
How about, either of these?
Perf
| where CounterName =="% Used Space"
| summarize AggregatedValue = count(CounterName) by bin(TimeGenerated, 10m), CounterName
// shows unique ConunterValues per CounterName
// source: https://docs.microsoft.com/en-us/azure/kusto/query/dcount-aggfunction
Perf
| where CounterName =="% Used Space"
| summarize AggregatedValue = dcount(CounterValue) by bin(TimeGenerated, 10m), CounterName
Nov 04 2019 06:22 PM - edited Nov 04 2019 06:22 PM
@CliveWatson Thanks Clive. I am not sure what is the most impressive. The fact that you understood my issue although my explanation was messy, incomplete and not well formatted or the accuracy of your answer.
The answer you provided works well for my case, I am keen to understand the explanation. Shall I use distinct when looking for distinct rows rather than values ?
I suspect the key is dcount(), i read about it but I was reluctant to use it because of this: returns an estimate for the number. The dcount() aggregation function is primarily useful for estimating the cardinality of huge sets. It trades performance for accuracy
In my case, the accuracy is important, but I am analyzing a small set...
Nov 04 2019 01:20 AM
Solution
How about, either of these?
Perf
| where CounterName =="% Used Space"
| summarize AggregatedValue = count(CounterName) by bin(TimeGenerated, 10m), CounterName
// shows unique ConunterValues per CounterName
// source: https://docs.microsoft.com/en-us/azure/kusto/query/dcount-aggfunction
Perf
| where CounterName =="% Used Space"
| summarize AggregatedValue = dcount(CounterValue) by bin(TimeGenerated, 10m), CounterName