Forum Discussion

Gamleur84's avatar
Gamleur84
Copper Contributor
Nov 04, 2019
Solved

How can I select distinct values in a column rather than distinc column ?

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 😞

Azure Log Analytics
azure monitor

2 Replies

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Nov 04, 2019

    Gamleur84 

     

    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

     

    • Gamleur84's avatar
      Gamleur84
      Copper Contributor
      Nov 05, 2019

      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...

Resources