Part 4: Querying Azure Log Analytics Workspace

Understanding Azure Resource Logs

In my experience managing Azure infrastructure, understanding the various log types and table schemas is essential for effective troubleshooting and monitoring. In this part, I'll share what I've learned about querying specific Azure resources.

Common Log Analytics Tables

Let me walk you through the tables I query most frequently and what they contain.

AzureActivity - Control Plane Operations

This table contains Azure Resource Manager operations - essentially a record of who did what in your Azure subscription.

Schema Overview:

AzureActivity
| getschema
| project ColumnName, ColumnType

Useful queries I run daily:

// Track resource deployments
AzureActivity
| where TimeGenerated > ago(24h)
| where OperationNameValue contains "write"
| where ActivityStatusValue == "Success"
| project 
    TimeGenerated,
    Caller,
    OperationNameValue,
    ResourceGroup,
    Resource,
    Level
| order by TimeGenerated desc

// Find failed operations
AzureActivity
| where TimeGenerated > ago(24h)
| where ActivityStatusValue in ("Failed", "Error")
| summarize 
    FailureCount = count(),
    SampleError = any(ActivityStatusValue),
    LastFailure = max(TimeGenerated)
    by Caller, OperationNameValue, ResourceGroup
| order by FailureCount desc

// Who deleted resources?
AzureActivity
| where TimeGenerated > ago(7d)
| where OperationNameValue contains "delete"
| project TimeGenerated, Caller, Resource, ResourceGroup, OperationNameValue
| order by TimeGenerated desc

AzureDiagnostics - Data Plane Logs

AzureDiagnostics contains diagnostic logs from various Azure services. The schema varies by ResourceType and Category.

Key pattern I use - always filter by ResourceType:

// Application Gateway access logs
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where ResourceType == "APPLICATIONGATEWAYS"
| where Category == "ApplicationGatewayAccessLog"
| project 
    TimeGenerated,
    ClientIP = clientIP_s,
    RequestUri = requestUri_s,
    HttpStatus = httpStatus_d,
    TimeTaken = timeTaken_d,
    UserAgent = userAgent_s
| where HttpStatus >= 400
| order by TimeGenerated desc

// Key Vault access audit
AzureDiagnostics
| where TimeGenerated > ago(24h)
| where ResourceType == "VAULTS"
| where Category == "AuditEvent"
| project 
    TimeGenerated,
    Caller = identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s,
    OperationName = OperationName,
    ResultType = ResultSignature,
    Resource = Resource
| where ResultType != "OK"

// SQL Database query performance
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where ResourceType == "SERVERS/DATABASES"
| where Category == "QueryStoreRuntimeStatistics"
| extend 
    QueryId = column_ifexists("query_hash_s", ""),
    Duration = column_ifexists("duration_d", 0.0),
    CpuTime = column_ifexists("cpu_time_d", 0.0)
| where Duration > 5000  // Queries taking more than 5 seconds
| project TimeGenerated, QueryId, Duration, CpuTime, ResourceGroup
| order by Duration desc

Heartbeat - Agent Health and Inventory

Tracks health and inventory of machines with Log Analytics agent installed.

My standard health checks:

// Find offline agents
Heartbeat
| summarize LastHeartbeat = max(TimeGenerated) by Computer
| extend MinutesSinceLastHeartbeat = datetime_diff('minute', now(), LastHeartbeat)
| where MinutesSinceLastHeartbeat > 5
| project Computer, LastHeartbeat, MinutesSinceLastHeartbeat
| order by MinutesSinceLastHeartbeat desc

// Inventory with OS information
Heartbeat
| where TimeGenerated > ago(24h)
| summarize LastSeen = max(TimeGenerated) by Computer, OSType, OSMajorVersion, ComputerEnvironment
| extend Status = case(
    datetime_diff('minute', now(), LastSeen) < 5, "Online",
    datetime_diff('minute', now(), LastSeen) < 60, "Recently Seen",
    "Offline"
)
| project Computer, OSType, OSMajorVersion, ComputerEnvironment, Status, LastSeen
| order by Status asc, Computer asc

// Agent version tracking
Heartbeat
| where TimeGenerated > ago(24h)
| summarize arg_max(TimeGenerated, *) by Computer
| project Computer, Version, Category, OSType
| summarize ComputerCount = count() by Version
| order by ComputerCount desc

Perf - Performance Counters

Contains performance metrics from Windows and Linux machines.

CPU monitoring queries I use:

// Real-time CPU usage
Perf
| where TimeGenerated > ago(15m)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| where InstanceName == "_Total"
| summarize AvgCpu = avg(CounterValue) by Computer, bin(TimeGenerated, 1m)
| render timechart

// Identify servers with sustained high CPU
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| where InstanceName == "_Total"
| summarize AvgCpu = avg(CounterValue), MaxCpu = max(CounterValue) by Computer
| where AvgCpu > 80
| extend Severity = case(
    AvgCpu > 95, "Critical",
    AvgCpu > 90, "High",
    AvgCpu > 80, "Medium",
    "Low"
)
| order by AvgCpu desc

// CPU trend over time
Perf
| where TimeGenerated > ago(7d)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| where InstanceName == "_Total"
| summarize AvgCpu = avg(CounterValue) by Computer, bin(TimeGenerated, 1h)
| render timechart

Memory monitoring:

// Available memory tracking (Windows)
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Memory" and CounterName == "Available MBytes"
| extend AvailableGB = CounterValue / 1024
| where AvailableGB < 1
| summarize 
    AvgAvailableGB = avg(AvailableGB),
    MinAvailableGB = min(AvailableGB)
    by Computer, bin(TimeGenerated, 5m)
| order by MinAvailableGB asc

// Memory usage percentage (Linux)
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Memory" and CounterName == "% Used Memory"
| summarize AvgMemUsed = avg(CounterValue) by Computer, bin(TimeGenerated, 5m)
| where AvgMemUsed > 90
| render timechart

Disk performance:

// Disk I/O latency
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "LogicalDisk" and CounterName in ("Avg. Disk sec/Read", "Avg. Disk sec/Write")
| extend LatencyMs = CounterValue * 1000
| where LatencyMs > 15  // High latency threshold
| summarize 
    AvgLatency = avg(LatencyMs),
    MaxLatency = max(LatencyMs)
    by Computer, InstanceName, CounterName, bin(TimeGenerated, 5m)
| order by MaxLatency desc

// Disk space monitoring
Perf
| where TimeGenerated > ago(5m)
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space"
| where InstanceName !in ("_Total", "HarddiskVolume1")  // Exclude meta disks
| summarize FreeSpace = avg(CounterValue) by Computer, InstanceName
| where FreeSpace < 20
| extend Severity = case(
    FreeSpace < 10, "Critical",
    FreeSpace < 15, "Warning",
    "Info"
)
| order by FreeSpace asc

Syslog - Linux System Logs

For Linux machines, system logs appear in the Syslog table.

// Authentication failures
Syslog
| where TimeGenerated > ago(24h)
| where Facility == "auth" or Facility == "authpriv"
| where SyslogMessage contains "Failed password"
| extend SourceIP = extract(@"from (\d+\.\d+\.\d+\.\d+)", 1, SyslogMessage)
| summarize FailureCount = count() by Computer, SourceIP
| where FailureCount > 5
| order by FailureCount desc

// System errors and critical events
Syslog
| where TimeGenerated > ago(1h)
| where SeverityLevel in ("err", "crit", "alert", "emerg")
| project TimeGenerated, Computer, Facility, SeverityLevel, SyslogMessage
| order by TimeGenerated desc

// Service status monitoring
Syslog
| where TimeGenerated > ago(24h)
| where SyslogMessage contains "systemd" and (SyslogMessage contains "Started" or SyslogMessage contains "Stopped")
| extend ServiceName = extract(@"(Started|Stopped) (.+?)(\.|$)", 2, SyslogMessage)
| extend Action = extract(@"(Started|Stopped)", 1, SyslogMessage)
| project TimeGenerated, Computer, Action, ServiceName
| order by TimeGenerated desc

Event - Windows Event Logs

Windows event logs for Application, System, and Security logs.

// Application errors
Event
| where TimeGenerated > ago(24h)
| where EventLog == "Application"
| where EventLevelName == "Error"
| summarize 
    ErrorCount = count(),
    SampleError = any(RenderedDescription)
    by Computer, Source, EventID
| order by ErrorCount desc

// System events - service failures
Event
| where TimeGenerated > ago(24h)
| where EventLog == "System"
| where Source == "Service Control Manager"
| where EventID in (7034, 7036)  // Service crash or state change
| project TimeGenerated, Computer, EventID, RenderedDescription
| order by TimeGenerated desc

// Security auditing - logon events
Event
| where TimeGenerated > ago(24h)
| where EventLog == "Security"
| where EventID in (4624, 4625)  // Successful and failed logons
| extend LogonType = case( EventID == 4624, "Success", "Failure")
| summarize Count = count() by Computer, LogonType, bin(TimeGenerated, 1h)
| render timechart

Container and Kubernetes Monitoring

For AKS and container workloads, these tables are essential.

ContainerLog - Container stdout/stderr

// Application errors in containers
ContainerLog
| where TimeGenerated > ago(1h)
| where LogEntry contains "error" or LogEntry contains "exception"
| project 
    TimeGenerated,
    Computer,
    ContainerID,
    Name,
    LogEntry
| order by TimeGenerated desc

// High-volume log producers
ContainerLog
| where TimeGenerated > ago(1h)
| summarize LogCount = count() by Name, Namespace = Computer
| order by LogCount desc
| take 20

KubePodInventory - Pod Metadata

// Pod status overview
KubePodInventory
| where TimeGenerated > ago(5m)
| summarize arg_max(TimeGenerated, *) by Name, Namespace
| project 
    Name,
    Namespace,
    PodStatus,
    PodCreationTimeStamp,
    ContainerRestartCount,
    ClusterName = Computer
| where PodStatus != "Running"
| order by ContainerRestartCount desc

KubeEvents - Kubernetes Events

// Warning and error events
KubeEvents
| where TimeGenerated > ago(1h)
| where Type != "Normal"
| project 
    TimeGenerated,
    Namespace,
    Name,
    Kind = Kind,
    Type,
    Reason,
    Message
| order by TimeGenerated desc

// Image pull errors
KubeEvents
| where TimeGenerated > ago(24h)
| where Reason in ("Failed", "BackOff", "ErrImagePull", "ImagePullBackOff")
| summarize Count = count(), LastSeen = max(TimeGenerated) by Namespace, Name, Reason, Message
| order by Count desc

Perf - Container Performance

// Container CPU usage
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "K8SContainer"
| where CounterName == "cpuUsageNanoCores"
| extend CpuCores = CounterValue / 1000000000
| summarize AvgCpuCores = avg(CpuCores) by InstanceName, bin(TimeGenerated, 5m)
| where AvgCpuCores > 1
| render timechart

// Container memory usage
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "K8SContainer"
| where CounterName == "memoryWorkingSetBytes"
| extend MemoryMB = CounterValue / 1024 / 1024
| summarize AvgMemoryMB = avg(MemoryMB) by InstanceName, bin(TimeGenerated, 5m)
| render timechart

Application Insights Tables

For application monitoring, Application Insights provides rich telemetry.

AppRequests - HTTP Requests

// Request volume and success rate
AppRequests
| where TimeGenerated > ago(1h)
| summarize 
    RequestCount = count(),
    SuccessCount = countif(Success == true),
    AvgDuration = avg(DurationMs),
    P95Duration = percentile(DurationMs, 95)
    by OperationName, ResultCode, bin(TimeGenerated, 5m)
| extend SuccessRate = 100.0 * SuccessCount / RequestCount
| order by TimeGenerated desc

// Slowest endpoints
AppRequests
| where TimeGenerated > ago(24h)
| summarize 
    AvgDuration = avg(DurationMs),
    P99Duration = percentile(DurationMs, 99),
    RequestCount = count()
    by OperationName
| order by P99Duration desc
| take 20

AppExceptions - Application Exceptions

// Top exceptions
AppExceptions
| where TimeGenerated > ago(24h)
| summarize 
    ExceptionCount = count(),
    AffectedUsers = dcount(UserId),
    SampleMessage = any(OuterMessage)
    by ExceptionType = Type
| order by ExceptionCount desc

// Exception rate over time
AppExceptions
| where TimeGenerated > ago(24h)
| summarize ExceptionCount = count() by bin(TimeGenerated, 1h)
| render timechart

AppDependencies - External Dependencies

// Dependency health dashboard
AppDependencies
| where TimeGenerated > ago(1h)
| summarize 
    CallCount = count(),
    FailureCount = countif(Success == false),
    AvgDuration = avg(DurationMs),
    P95Duration = percentile(DurationMs, 95)
    by Target, Type
| extend 
    SuccessRate = 100.0 * (CallCount - FailureCount) / CallCount,
    HealthStatus = case(
        SuccessRate < 95, "Unhealthy",
        SuccessRate < 99, "Degraded",
        "Healthy"
    )
| order by SuccessRate asc

Resource-Specific Query Patterns

Azure Storage Account Monitoring

// Storage account transactions
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where ResourceType == "STORAGEACCOUNTS"
| where Category == "StorageRead" or Category == "StorageWrite"
| extend OperationType = case(
    Category == "StorageRead", "Read",
    "Write"
)
| summarize 
    TransactionCount = count(),
    AvgE2ELatency = avg(E2ELatencyMs),
    MaxE2ELatency = max(E2ELatencyMs)
    by AccountName, OperationType, bin(TimeGenerated, 5m)
| where MaxE2ELatency > 1000

Azure SQL Database

// SQL Database DTU usage
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where ResourceType == "SERVERS/DATABASES"
| where Category == "Basic"
| extend DtuPercent = column_ifexists("dtu_consumption_percent_s", 0.0)
| summarize AvgDtu = avg(todouble(DtuPercent)) by Resource, bin(TimeGenerated, 5m)
| where AvgDtu > 80
| render timechart

// Blocked queries
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where ResourceType == "SERVERS/DATABASES"
| where Category == "Blocks"
| project TimeGenerated, Resource, DatabaseName = database_name_s, WaitType = wait_type_s

Azure Functions

// Function execution monitoring
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where ResourceType == "SITES" and Category == "FunctionExecutionLogs"
| extend 
    FunctionName = column_ifexists("FunctionName", ""),
    Status = column_ifexists("Status", ""),
    Duration = column_ifexists("Duration", 0)
| summarize 
    ExecutionCount = count(),
    AvgDuration = avg(Duration),
    FailureCount = countif(Status != "Success")
    by FunctionName
| extend SuccessRate = 100.0 * (ExecutionCount - FailureCount) / ExecutionCount
| order by FailureCount desc

Cross-Resource Correlation

One of my most valuable patterns is correlating issues across different resource types:

// Correlate application errors with infrastructure issues
let errorPeriods = AppExceptions
| where TimeGenerated > ago(1h)
| summarize ErrorCount = count() by bin(TimeGenerated, 5m)
| where ErrorCount > 10
| project TimeGenerated;
Perf
| where TimeGenerated > ago(1h)
| where CounterName in ("% Processor Time", "% Used Memory")
| summarize AvgValue = avg(CounterValue) by Computer, CounterName, bin(TimeGenerated, 5m)
| join kind=inner (errorPeriods) on TimeGenerated
| where AvgValue > 80
| project TimeGenerated, Computer, CounterName, AvgValue
| order by TimeGenerated desc

Practical Monitoring Scenarios

Scenario 1: Complete Application Health Check

let timeRange = 5m;
// Frontend health
let FrontendHealth = AppRequests
| where TimeGenerated > ago(timeRange)
| summarize 
    RequestRate = count() / 5.0,
    SuccessRate = 100.0 * countif(Success) / count(),
    AvgLatency = avg(DurationMs);
// Backend dependencies
let DependencyHealth = AppDependencies
| where TimeGenerated > ago(timeRange)
| summarize 
    DependencyRate = count() / 5.0,
    DependencySuccessRate = 100.0 * countif(Success) / count();
// Exception rate
let ExceptionHealth = AppExceptions
| where TimeGenerated > ago(timeRange)
| summarize ExceptionRate = count() / 5.0;
// Combine metrics
FrontendHealth
| extend dummy = 1
| join kind=inner (DependencyHealth | extend dummy = 1) on dummy
| join kind=inner (ExceptionHealth | extend dummy = 1) on dummy
| project RequestRate, SuccessRate, AvgLatency, DependencySuccessRate, ExceptionRate

Scenario 2: Infrastructure Capacity Planning

// Analyze last 30 days for capacity trends
Perf
| where TimeGenerated > ago(30d)
| where CounterName in ("% Processor Time", "% Used Memory")
| summarize 
    P95Value = percentile(CounterValue, 95),
    P99Value = percentile(CounterValue, 99),
    MaxValue = max(CounterValue)
    by Computer, CounterName
| extend CapacityStatus = case(
    P95Value > 80, "Need Scaling",
    P95Value > 70, "Monitor Closely",
    "Adequate Capacity"
)
| order by P95Value desc

Key Takeaways

• Always filter by ResourceType and Category in AzureDiagnostics

• Use Heartbeat for agent inventory and health tracking

• Perf provides critical performance metrics across platforms

• Container tables (ContainerLog, KubePodInventory, KubeEvents) are essential for Kubernetes

• Application Insights tables provide full application telemetry

• Cross-resource correlation reveals relationship between infrastructure and application issues

• Understanding table schemas is crucial for effective queries

In Part 5, we'll use these querying skills to build comprehensive observability dashboards with Azure Workbooks and create visualizations that provide actionable insights.