Essential KQL Queries for Azure with and without Log Analytics

When you’re working with Azure, speed matters. Whether you’re troubleshooting an outage, validating a configuration change, or simply trying to answer the question “what just happened?”, the difference between minutes and seconds can be critical. This is where Kusto Query Language (KQL) really shines.

KQL is the query language behind Azure Monitor, Log Analytics, Application Insights, Microsoft Sentinel, and even Azure Resource Graph. It allows you to ask complex questions about your environment in a concise, readable way and get answers almost instantly. Once you start using it regularly, it becomes one of the most valuable tools in an Azure engineer’s toolbox.

This article focuses on why KQL is so powerful, when to use it instead of traditional tools like PowerShell or Azure CLI, and how you can even run certain KQL queries without having Log Analytics enabled.

Why KQL is so powerful and often faster than PowerShell or CLI

PowerShell and Azure CLI are fantastic tools, they’re flexible, scriptable, and essential for automation. I use them daily. But when it comes to exploration, investigation, and correlation, they have some natural limitations.

KQL is optimized for:

• Large-scale data analysis (millions of records in seconds)
• Ad-hoc investigation without writing full scripts
• Time-based queries (patterns, spikes, trends)
• Correlation across datasets (logs, metrics, activity, telemetry)

With PowerShell or CLI, you often need to:

• Call multiple APIs
• Handle pagination
• Parse JSON manually
• Store intermediate results
• Loop and filter in code
• Process each individual resource from which you are trying to pull information

With KQL, the data is already indexed and optimized for querying. Filtering, aggregating, joining, and summarizing are native operations. Not something you have to build yourself.

In practice, this means:

• Faster answers
• Less code
• Less cognitive overhead
• More consistent results across environments

KQL is not a replacement for PowerShell or CLI. It complements them. PowerShell is great for doing things. KQL is great for understanding things.

Using Azure Copilot to Accelerate KQL Creation

Azure Copilot adds another layer of productivity on top of KQL. Instead of starting from a blank query window, you can describe what you’re trying to find in natural language and let Copilot generate a starting KQL query for you. Any copilot / AI tool can do that, but Azure one is especially useful because you can ask about specific context in your Azure Portal.

This is especially useful when:

• You’re exploring a new dataset
• You don’t remember exact table or column names
• You want to quickly validate an idea
• You need a rough query that you can refine
• Getting cost information about specific resource or time period. This isn’t natively available in the GUI.

Copilot won’t replace understanding KQL, but it dramatically shortens the time from question to working query. For many engineers, it removes the initial friction and helps them focus on interpreting results rather than fighting syntax.

How to use KQL

One of the reasons KQL is so powerful is that it’s available across multiple Azure services and tools, often without requiring any local setup. Depending on what data you want to query, KQL can run in different places.

You can run KQL directly in the Azure Portal through experiences like Azure Monitor Logs, Application Insights, Microsoft Sentinel, and Azure Resource Graph Explorer. In these cases, no installation is required, access is entirely browser-based.

For local or automated workflows, KQL can also be executed via PowerShell and Azure CLI. This typically requires installing the relevant Azure modules (for example, Az.ResourceGraph for Resource Graph queries) and authenticating to Azure. Once authenticated, queries can be embedded into scripts, scheduled jobs, or reporting pipelines.

The key difference is where the data comes from: some KQL queries run against ingested log data in a Log Analytics workspace, while others query live resource metadata directly from Azure Resource Manager. Understanding this distinction is essential before choosing where and how to run your queries.

Running KQL Without Log Analytics

Not all KQL queries require data to be ingested into a Log Analytics workspace.

Azure Resource Graph also uses KQL and allows you to query the current state of your Azure resources directly from Azure Resource Manager. This means you can answer many governance, inventory, and configuration questions without enabling Log Analytics at all.

Typical use cases include:

• Inventory and discovery
• Governance and compliance checks
• Identifying exposed resources
• Finding orphaned or unused assets
• Validating configuration drift

Where and how to run these queries

You can run Azure Resource Graph queries in several places:

• Azure Portal (Resource Graph Explorer)
• Azure CLI
• PowerShell (most automation-friendly)

Below is the recommended PowerShell approach using the Az modules.

1
2
3
4
5
6
7
8
9

Install-Module Az.ResourceGraph -Scope CurrentUser
Connect-AzAccount

$query = @"
Resources
| take 5
"@

Search-AzGraph -Query $query -First 1000

KQL Examples without Log Analytics Workspace

1. Resources missing any of the required tags

1
2
3
4
5
6
7

let required = dynamic(['owner','env','costCenter']);
Resources
| extend missing = required
| mv-expand k = missing
| where isnull(tags[tostring(k)]) or tostring(tags[tostring(k)]) == ''
| summarize MissingTags=make_set(tostring(k)) by subscriptionId, resourceGroup, name, type, location
| order by array_length(MissingTags) desc

2. Exposed management ports (NSG allows 22/3389 from Internet)

1
2
3
4
5
6
7
8
9
10
11

Resources
| where type =~ 'microsoft.network/networksecuritygroups'
| mv-expand r = properties.securityRules
| extend dir=tostring(r.properties.direction), access=tostring(r.properties.access),
         src=tostring(r.properties.sourceAddressPrefix),
         dport=tostring(r.properties.destinationPortRange),
         nameRule=tostring(r.name)
| where dir =~ 'Inbound' and access =~ 'Allow'
| where src in ('*','Internet','0.0.0.0/0')
| where dport in ('22','3389')
| project subscriptionId, resourceGroup, nsg=name, nameRule, src, dport

3. Public IP info

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

// Get Public IPs
Resources
| where type =~ 'microsoft.network/publicipaddresses'
| extend ip=tostring(properties.ipAddress)
| project subscriptionId, resourceGroup, name, location, ip, sku=tostring(sku.name)
| order by isnotempty(ip) desc

//VMs with public IP exposure via NIC IP configs

Resources
| where type =~ 'microsoft.network/networkinterfaces'
| mv-expand ipconf = properties.ipConfigurations
| extend pipId = tostring(ipconf.properties.publicIPAddress.id)
| where isnotempty(pipId)
| project nic=name, resourceGroup, subscriptionId, pipId

4. Public network access enabled

1
2
3
4
5
6
7
8
9
10
11
12
13

// Storage accounts with public network access enabled
Resources
| where type =~ 'microsoft.storage/storageaccounts'
| extend pna=tostring(properties.publicNetworkAccess)
| where isempty(pna) or pna !~ 'Disabled'
| project subscriptionId, resourceGroup, name, location, pna, sku=tostring(sku.name)

//Key Vaults with public network access enabled
Resources
| where type =~ 'microsoft.keyvault/vaults'
| extend pna=tostring(properties.publicNetworkAccess)
| where isempty(pna) or pna !~ 'Disabled'
| project subscriptionId, resourceGroup, name, location, pna, sku=tostring(sku.name)

5. Get Orphaned managed disks and NICs

1
2
3
4
5
6
7
8
9
10
11
12
13
14

// Orphaned NICs
Resources
| where type =~ 'microsoft.network/networkinterfaces'
| where isnull(properties.virtualMachine)
| project name, resourceGroup, subscriptionId, location

//Unattached managed disks
Resources
| where type =~ 'microsoft.compute/disks'
| where isempty(tostring(properties.managedBy))
| project subscriptionId, resourceGroup, name, location,
         diskSizeGB=todouble(properties.diskSizeGB),
         sku=tostring(sku.name), tier=tostring(sku.tier)
| order by diskSizeGB desc

6. List everything with SKU/tier to help finding spend risk

1
2
3
4

Resources
| project type, name, resourceGroup, subscriptionId, location, sku=tostring(sku.name)
| summarize Count=count() by type, sku
| order by Count desc

Running KQL with Log Analytics (LAW)

When you need visibility into logs, metrics, events, and telemetry over time, KQL runs against data stored in a Log Analytics workspace (LAW). This is the most common and powerful way to use KQL, as it enables deep troubleshooting, correlation, and historical analysis.

Where you can run KQL with LAW

KQL queries against Log Analytics data can be executed in several places:

• Azure Portal → Azure Monitor → Logs
• Application Insights (workspace-based)
• Microsoft Sentinel (built on top of Log Analytics)
• PowerShell using Invoke-AzOperationalInsightsQuery
• Azure CLI using az monitor log-analytics query

All of these ultimately query the same Log Analytics workspace data.

Prerequisites for using Log Analytics

Before KQL queries return results, data must be actively ingested into the workspace. A Log Analytics workspace on its own is empty by default.

At a minimum, you need:

• A Log Analytics workspace created in Azure
• Proper RBAC permissions (Log Analytics Reader or higher)
• Data sources configured to send data into the workspace

Common ingestion methods include:

• Azure Activity Log exported via Diagnostic Settings (subscription-level)
• Azure Monitor Agent (AMA) collecting VM logs and metrics through Data Collection Rules (DCRs)
• Resource diagnostic settings sending service logs (e.g., Key Vault, NSG, Firewall) to LAW
• Application Insights configured to use a workspace-based model
• Custom data ingestion for logs and metrics that Azure does not collect out of the box

Custom data ingestion is especially powerful when your most important signals are outside native Azure telemetry. This can include:

• Custom application logs
• Results of scheduled scans or health checks
• Inventory snapshots or compliance data
• Output from scripts, APIs, or external systems

Once ingested, custom tables behave like any other Log Analytics table. You can query them with KQL, join them with built-in tables, and correlate them with platform telemetry. This makes Log Analytics not just a monitoring tool, but a centralized data platform for operational insights.

Why Log Analytics matters

Log Analytics is what enables:

• Time-based analysis (trends, spikes, baselines)
• Cross-resource correlation
• Advanced joins across telemetry types
• Long-term retention and historical comparisons

In short, if you want to understand what happened, when it happened, and how different signals relate to each other, Log Analytics is the foundation that makes KQL truly powerful.

KQL examples with Log Analytics Workspace

7. What changed or failed recently? (resource changes across subscriptions)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

AzureActivity
| where TimeGenerated > ago(24h)
| where CategoryValue == "Administrative"
| where ActivityStatusValue in ("Succeeded","Accepted")
| project TimeGenerated, SubscriptionId, ResourceGroup, ResourceId,
          OperationNameValue, Caller, CorrelationId
| order by TimeGenerated desc


//What failed recently / top failing operations in last 24h
AzureActivity
| where TimeGenerated > ago(24h)
| where ActivityStatusValue !in ("Succeeded","Accepted")
| summarize Failures=count(), Latest=max(TimeGenerated), SampleOp=any(OperationNameValue)
  by SubscriptionId, ResourceGroup, OperationNameValue
| order by Failures desc, Latest desc

8. Who did what?

1
2
3
4
5
6

let window = 7d;
AzureActivity
| where TimeGenerated > ago(window)
| where CategoryValue == "Administrative"
| summarize Ops=count(), RGs=dcount(ResourceGroup), Resources=dcount(ResourceId) by Caller
| order by Ops desc

9. What is down right now?

1
2
3
4
5
6

let threshold = 15m;
Heartbeat
| summarize LastSeen=max(TimeGenerated) by Computer
| where LastSeen < ago(threshold)
| project Computer, LastSeen, MinutesSince=round(datetime_diff("minute", now(), LastSeen)*-1, 1)
| order by LastSeen asc

10. VM Performance

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

// CPU anomaly spike detection
// requires Perf counters
let host = "*";                 // set to a computer name to narrow
let counter = "% Processor Time";
Perf
| where TimeGenerated > ago(24h)
| where ObjectName == "Processor" and CounterName == counter and InstanceName == "_Total"
| where host == "*" or Computer == host
| summarize AvgCPU=avg(CounterValue) by Computer, bin(TimeGenerated, 5m)
| make-series AvgCPU=avg(AvgCPU) on TimeGenerated from ago(24h) to now() step 5m by Computer
| extend (anomalies, score, baseline) = series_decompose_anomalies(AvgCPU, 2.5, -1, 'linefit')
| mv-expand TimeGenerated, AvgCPU, anomalies, score, baseline
| where anomalies == 1
| project Computer, TimeGenerated, AvgCPU, baseline, score
| order by score desc

//Disk free % trend and worst offenders (Windows + Linux friendly if you collect both)
//Requires VM Insights
InsightsMetrics
| where TimeGenerated > ago(24h)
| where Namespace in ("LogicalDisk","Disk") // depends on agent setup
| where Name in ("FreeSpacePercentage","FreeSpaceMB","FreeSpace")
| summarize Latest=arg_max(TimeGenerated, Val) by Computer, Name, Tags
| project Computer, Name, Free = todouble(Latest_Val), Tags
| order by Free asc

11. Windows and Linux suspicious logon patterns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

//Windows security password spraying signal
//Requires SecurityEvent ingestion
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID in (4625, 4624) // failed + success
| extend Account = strcat(TargetDomainName, "\\", TargetUserName)
| summarize
    Failed=countif(EventID==4625),
    Success=countif(EventID==4624),
    SrcIPs=dcount(IpAddress),
    Latest=max(TimeGenerated)
  by Account
| where Failed > 25 and Success > 0
| order by Failed desc, SrcIPs desc


//Linux SSH/sudo failures
//Requires Syslog
Syslog
| where TimeGenerated > ago(24h)
| where Facility in ("auth","authpriv") or ProcessName in ("sshd","sudo")
| where SyslogMessage has_any ("Failed password", "authentication failure", "sudo:", "PAM")
| summarize Count=count(), Latest=max(TimeGenerated), Sample=any(SyslogMessage) by Computer, ProcessName
| order by Count desc

12. PIM queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

//Requires Entra ID AuditLogs / SigninLogs and PIM audit events sent to LAW via Entra diagnostic settings

//PIM activations (role activated) – who, what role, when
AuditLogs
| where TimeGenerated > ago(30d)
| where Category has "RoleManagement" or OperationName has_any ("Activate eligible role", "Add member to role", "Add eligible member to role")
| project TimeGenerated, OperationName, Result, InitiatedBy, TargetResources, AdditionalDetails
| order by TimeGenerated desc

//PIM / privileged role changes (adds/removes eligibility or assignments)
AuditLogs
| where TimeGenerated > ago(30d)
| where Category has "RoleManagement"
| where OperationName has_any ("Add eligible member to role","Remove eligible member from role","Add member to role","Remove member from role")
| project TimeGenerated, OperationName, Result, InitiatedBy, TargetResources
| order by TimeGenerated desc

Query data with KQL and process it with PowerShell

Now that we understand that obtaining results with KQL can be faster than via PowerShell or CLI, we can use that to our advantage.

In this older article, I explained how you can obtain data with KQL, save it into PowerShell object, and further process data within your PowerShell script workflow: Accelerate Azure PowerShell with Kusto Query Language.

KQL is one of those tools that quietly changes how you work once you truly adopt it. Whether you’re querying live resource metadata with Azure Resource Graph or diving deep into telemetry stored in Log Analytics, KQL lets you move from questions to answers with remarkable speed. Combined with tools like Azure Copilot, it lowers the barrier to exploration while still rewarding those who invest time in understanding the language. Used well, KQL doesn’t replace PowerShell or Azure CLI. It complements them, helping you understand your environment faster and make better, more confident decisions.

Festive Tech Calendar 2025

This article is part of Festive Tech Calendar 2025, a community-driven initiative where engineers, architects, and technologists from around the world share practical, experience-based content throughout the holiday season. The goal is simple: spread knowledge, inspiration, and a bit of festive spirit by publishing short, high-quality technical articles that others can learn from and reuse in their own work.

Thanks for following along! Keep exploring like Jacques Cousteau, stay curious, and keep clouding around!

Vukasin Terzic