CODEWITHSUNDEEP
azuredata-visualizationvisualizationazure-log-analyticskql
Gonçalo
Gonçalo

Reputation: 35

KQL - Limit amount of data based on a specific column

I have a log analytics workspace to which I feed VM metrics to. I want to render a timechart based on the data there, however I've run into a few issues.

The first issue is that I have a lot of machines and a lot of records, meaning they overlap and it's not very useful, therefore, I want to keep it at 10, as in, the 10 VMs with the highest average % Used Memory.

That said, using top or limit will literally limit the amount of data displayed, as it'll just put 10 points over the chart instead of limiting the amount of VMs I want displayed. How can I limit the data shown by the number of VMs?

Here's my query:

Perf 
| where ObjectName == "Memory" and CounterName == "% Used Memory"
| summarize UsedMemory = avg(CounterValue), any(Computer) by bin(TimeGenerated,15m)
| limit 10
| render timechart

Thank you.

Upvotes: 1

Views: 3986

Answers (2)

Avnera
Avnera

Reputation: 7618

An alternative way would be to first calculate the list of the top 10 computers and then plot them, for example:

let Top10Computers = Perf 
| where ObjectName == "Memory" and CounterName == "% Used Memory"
| summarize max(CounterValue) by Computer
| top 10 by max_CounterValue
| project Computer;
Perf
| where Computer in (Top10Computers)
| summarize avg(CounterValue) by bin(TimeGenerated, 5m), Computer
| render timechart

Upvotes: 1

Ivan Glasenberg
Ivan Glasenberg

Reputation: 29985

You can use the top operator and partition operator.

The sample query should look like below:

Perf 
| where ObjectName == "Memory" and CounterName == "% Used Memory"
| summarize UsedMemory = avg(CounterValue) by bin(TimeGenerated,15m),Computer
| partition by Computer
(
    top 1 by UsedMemory desc //fetch the highest value for each vm
)
| top 10 by UsedMemory desc //select the top 10 vm with the UsedMemory by descending

Upvotes: 1

Related Questions