0

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
tehais
  • 5
  • 3

1 Answers1

0

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

Venkat V
  • 2,197
  • 1
  • 1
  • 10
  • Hey thanks for the input, is it possible if its more granular where its able to get the CPU and memory utilization based on the users in the host pool. For e.g (Username, CPUUtilization and MemoryUtilization). I have this query which list all the users within the host pool (WVDConnection) – tehais Jun 13 '23 at 06:08
  • let utilization = Perf | where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total" | where TimeGenerated > ago(15m) | summarize avg(CounterValue) by Username Try this Query. – Venkat V Jun 15 '23 at 07:30
  • The query didn't work. Error message "'summarize' operator: Failed to resolve scalar expression named 'Username'". I've used the "Username" parameter, but it belongs to WVDConnection similar to what i did initially. – tehais Jun 15 '23 at 08:13