Skip to content

KQL.md

Latest commit

 

History

History
163 lines (145 loc) · 5.02 KB

KQL.md

File metadata and controls

163 lines (145 loc) · 5.02 KB

KQL Queries

Performance Queries

Total Bytes Received by computer

Perf
| where Computer contains @"uni1hpor"
| where (CounterName == "Bytes Total/sec" or CounterName == "Total Bytes Received")
| summarize BytesTotal = sum(CounterValue) by Computer
Perf
| where Computer contains @"uni1hpfs"
| where (CounterName == "Bytes Total/sec" or CounterName == "Total Bytes Received")
| where Computer == "uni1hpfs1"
Perf
| where Computer contains @"uni1hpfs"
| where (CounterName == "Bytes Total/sec" or CounterName == "Total Bytes Received")
| summarize BytesTotal = avg(CounterValue) by Computer
| sort by BytesTotal desc

CPU Utilisation

Perf 
|where Computer contains @"uni1hpap15" 
|where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"

Checking Disk Space

Perf | where ObjectName == "LogicalDisk" and CounterName == "% Free Space" and Computer == "uni1hpsp1.olc1.openlinkcloud.com"  
| summarize AggregatedValue = avg(CounterValue) by InstanceName

Checking Only C drive Space

Perf | 
where ObjectName == "LogicalDisk" and CounterName == "% Free Space" and Computer == "uni1hpsp1.olc1.openlinkcloud.com" and InstanceName == "C:" 
| summarize AggregatedValue = avg(CounterValue) by InstanceName

Check Diskspace on multiple computers

Perf 
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space" 
| where Computer contains "uni1hpsp1.olc1.openlinkcloud.com" or  Computer contains  "uni1hpsp2.olc1.openlinkcloud.com" or Computer contains  "uni1hpsp3.olc1.openlinkcloud.com" and InstanceName == "C:" 
| summarize AggregatedValue = avg(CounterValue) by Computer
Perf 
| where ObjectName == "LogicalDisk" and CounterName == "Free Megabytes" 
| where Computer contains "uni1hpsp1.olc1.openlinkcloud.com" and InstanceName == "C:" 
| summarize AggregatedValue = avg(CounterValue) by Computer

Memory checks with 3 different counters

Perf 
| where ObjectName == "Memory" and CounterName == "Available MBytes" 
| where Computer contains "uni1hpsp1.olc1.openlinkcloud.com"
| summarize AggregatedValue = avg(CounterValue) by Computer
Perf 
| where ObjectName == "Memory" and CounterName == "Committed Bytes" 
| where Computer contains "uni1hpsp1.olc1.openlinkcloud.com"
| summarize AggregatedValue = avg(CounterValue) by Computer
Perf 
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use" 
| where Computer contains "uni1hpsp1.olc1.openlinkcloud.com"
| summarize AggregatedValue = avg(CounterValue) by Compute

Memory Utilisation Report

Perf 
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use" 
| where Computer contains "uni2hpap33.olc1.openlinkcloud.com"
| summarize AggregatedValue = percentile(CounterValue, 90) by Computer, bin(TimeGenerated, 1h)

CPU Utilization Report

Perf 
|where Computer contains @"uni2hpap33" 
|where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
|summarize avg(CounterValue) by Computer, bin(TimeGenerated, 1h)

Disk Read/Writes per second Report

Perf
| where Computer contains @"uni2hpap33"
| where CounterName == "Disk Reads/sec"
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 1h)
| render timechart
Perf
| where Computer contains @"uni2hpap33"
| where CounterName == "Disk Writes/sec"
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 1h)
| render timechart

Network Bytes Total/Sec

Perf
| where Computer contains @"uni2hpap33"
| where ObjectName == "Network Interface" and CounterName == "Bytes Total/sec"
| summarize BytesTotal = avg(CounterValue) by Computer, bin(TimeGenerated, 1h)
| render timechart

Resource Graph Query

Check power status of filtered vms

resources
| where type == "microsoft.compute/virtualmachines"
| where name hasprefix "ewe1npxa"
| project name, resourceGroup, State=properties['extended']['instanceView']['powerState']['code']
resources
| where type == "microsoft.compute/virtualmachines"
| extend statestring = properties['extended']['instanceView']['powerState']['code']
| project name, resourceGroup, State=split(statestring,"/")[-1]

Power status of any particular vm

resources
| where type == "microsoft.compute/virtualmachines"
| where name == "ewe1hosp2"
| project name, resourceGroup, State=properties['extended']['instanceView']['powerState']['code']

List VM sizes

$query = @"
resources
| where type == "microsoft.compute/virtualmachines"
| project name, resourceGroup, VMSize=properties['hardwareProfile']['vmSize']
"@
Search-AzGraph -Query $query

With OS Disk Name

resources
| where type == "microsoft.compute/virtualmachines"
| where name startswith "$vm_pattern"
| project name, resourceGroup, osDisk=properties['storageProfile']['osDisk']['name'],State=properties['extended']['instanceView']['powerState']['code']