CODEWITHSUNDEEP
azurekqlazure-log-analyticswvd
tehais
tehais

Reputation: 5

KQL query CPU and memory utilization based on users in a Azure AVD hostpool

The whole query is made up of 2 different parts WVDConnection, Perf processor and Perf memory. They're able to run individually but its unable to run as a whole. The error I've got "No results found from the last 24 hours Try selecting another time range". I've changed the time range to 30 min and the output is the same. The query is run on log analytics workspace. An example of the output (Username, CPUUtilization and MemoryUtilization) Am I missing something?

The queries and output are as the following: enter image description here

//list all users in a hostpool
WVDConnections
| where _ResourceId =~ 'resource id of hostpool'

//CPU and memory utilization
Perf
    | where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
    | summarize CPUUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| join kind=inner (
    Perf
    | where ObjectName == "Memory" and CounterName == "Available MBytes"
    | summarize MemoryUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| project TimeGenerated, CPUUtilization, MemoryUtilization

//The whole query after joining both altogether
WVDConnections
| where _ResourceId =~ 'resource id of hostpool'
| project CorrelationId, State, TimeGenerated, UserName
| summarize TimeGenerated=min(TimeGenerated) by CorrelationId, State, UserName
| join kind=inner (
    Perf
    | where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
    | summarize CPUUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| join kind=inner (
    Perf
    | where ObjectName == "Memory" and CounterName == "Available MBytes"
    | summarize MemoryUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| project TimeGenerated, UserName, CPUUtilization, MemoryUtilization

Upvotes: 0

Views: 1787

Answers (1)

Venkat V
Venkat V

Reputation: 7805

The whole query is made up of 2 different parts WVDConnection, Perf processor and Perf memory. They're able to run individually but its unable to run as a whole.

You can use below KQL query to get both Processor and Memory result in single query.

1st Method:

Perf
| where (ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total")
    or (ObjectName == "Memory" and CounterName == "Available MBytes")
| summarize avg(CounterValue) by TimeGenerated, ObjectName, CounterName, InstanceName
| project TimeGenerated, ObjectName, CounterName, InstanceName, CounterValue = avg_CounterValue

Output:

enter image description here

2nd Method:

I have used the join operator to combine the ProcessorData and MemoryData tables based on the TimeGenerated column.

let ProcessorData = Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
| summarize CPUUtilization = avg(CounterValue) by TimeGenerated;
let MemoryData = Perf
| where ObjectName == "Memory" and CounterName == "Available MBytes"
| summarize MemoryUtilization = avg(CounterValue) by TimeGenerated;
ProcessorData
| join kind=inner MemoryData on TimeGenerated
| project TimeGenerated, CPUUtilization, MemoryUtilization;

Output:

enter image description here

Upvotes: 0

Related Questions