Understand the essential information about the built-in Log Queries in Azure

Azure offers a variety of built-in log queries that can be accessed via Azure Monitor and Log Analytics. These queries are predefined to help users quickly gain insights from their Azure resources and services. Built-in queries typically focus on common use cases such as monitoring infrastructure, security, performance, and diagnostics. Here’s an overview of key built-in log queries in Azure:

Activity Log Queries

These queries are used to access the Azure Activity Log, which records all management events in Azure, such as resource creation, modification, and deletion.

1. Failed Login Attempts:

xxxxxxxxxx
4
1
AzureActivity
2
| where ActivityStatus == "Failure" and OperationName == "Sign-in"
3
| project TimeGenerated, Caller, OperationName, ActivityStatus, Resource, ResourceGroup
4
| order by TimeGenerated desc

2. Resource Deletions:

xxxxxxxxxx
4
1
AzureActivity
2
| where OperationName == "Delete Resource"
3
| project TimeGenerated, Resource, ResourceGroup, Caller
4
| order by TimeGenerated desc

Security Log Queries

Azure Sentinel provides built-in queries that can help monitor and detect security-related events. These queries are often used to detect suspicious behavior, such as unauthorized access or potential threats.

1. Failed Sign-ins (Security):

xxxxxxxxxx
4
1
SecurityEvent
2
| where EventID == 4625 // Failed logon
3
| project TimeGenerated, Account, IPAddress, WorkstationName, FailureReason
4
| order by TimeGenerated desc

2. Unusual User Activity:

xxxxxxxxxx
4
1
SecurityEvent
2
| where EventID == 4624 // Logon event
3
| summarize Count = count() by Account, bin(TimeGenerated, 1h)
4
| order by Count desc

3. Suspicious PowerShell Commands:

xxxxxxxxxx
4
1
SecurityEvent
2
| where EventID == 4104 // PowerShell command
3
| where Message contains "Invoke-Expression"
4
| project TimeGenerated, Account, Message

Performance Log Queries

These queries monitor the performance of virtual machines, applications, and other resources in your Azure environment.

1. CPU Utilization on Virtual Machines:

xxxxxxxxxx
5
1
Perf
2
| where ObjectName == "Processor" and CounterName == "% Processor Time"
3
| where Computer startswith "VM"  // Filtering for VMs
4
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 5m)
5
| order by TimeGenerated desc

2. Memory Usage on Virtual Machines:

xxxxxxxxxx
5
1
Perf
2
| where ObjectName == "Memory" and CounterName == "Available MBytes"
3
| where Computer startswith "VM" // Filter for VMs
4
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 10m)
5
| order by TimeGenerated desc

3. Disk I/O Performance:

xxxxxxxxxx
4
1
Perf
2
| where ObjectName == "LogicalDisk" and CounterName == "Disk Write Bytes/sec"
3
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 5m)
4
| order by TimeGenerated desc

Azure Resource Usage Queries

These queries focus on the usage metrics of various Azure resources, like storage accounts, networking, etc.

1. Storage Account Activity:

xxxxxxxxxx
4
1
AzureDiagnostics
2
| where ResourceType == "STORAGEACCOUNTS" and Resource == "myStorageAccount"
3
| summarize count() by bin(TimeGenerated, 1h)
4
| order by TimeGenerated desc

2. Azure Network Traffic:

xxxxxxxxxx
4
1
AzureDiagnostics
2
| where ResourceType == "NETWORK" and OperationName == "NetworkSecurityGroupFlowEvent"
3
| summarize Count = count() by SourceIP, DestinationIP, bin(TimeGenerated, 1h)
4
| order by TimeGenerated desc

Application Insights Queries

Built-in queries are also available for analyzing application performance, traces, and requests for applications monitored by Application Insights.

1. Request Failure Rate:

xxxxxxxxxx
4
1
requests
2
| where success == "False"
3
| summarize Count = count() by bin(timestamp, 1h), name
4
| order by Count desc

2. Top Exceptions by Count:

xxxxxxxxxx
3
1
exceptions
2
| summarize Count = count() by type, bin(timestamp, 1h)
3
| order by Count desc

3. Slowest Requests:

xxxxxxxxxx
4
1
requests
2
| where duration > 1000  // Requests taking longer than 1 second
3
| project timestamp, name, duration
4
| order by duration desc

Azure Kubernetes Service (AKS) Logs

For Azure Kubernetes Service (AKS), there are built-in queries to monitor Kubernetes clusters and containers.

1. AKS Cluster Health:

xxxxxxxxxx
5
1
ContainerLog
2
| where ClusterName == "myAKSCluster"
3
| where LogEntry contains "error"
4
| project TimeGenerated, LogEntry
5
| order by TimeGenerated desc

2. Pod Restarts:

xxxxxxxxxx
4
1
KubePodInventory
2
| where ClusterName == "myAKSCluster"
3
| summarize Restarts = sum(RestartCount) by PodName, bin(TimeGenerated, 1h)
4
| order by Restarts desc

Azure Firewall Logs

For monitoring Azure Firewall and network security, there are queries that help track activity logs, traffic analytics, and other key metrics.

1. Firewall Drop Log:

xxxxxxxxxx
4
1
AzureDiagnostics
2
| where ResourceType == "AZUREFIREWALL" and Action_s == "Deny"
3
| summarize count() by SourceIP_s, DestinationIP_s, bin(TimeGenerated, 1h)
4
| order by TimeGenerated desc

2. Firewall Allowed Traffic:

xxxxxxxxxx
4
1
AzureDiagnostics
2
| where ResourceType == "AZUREFIREWALL" and Action_s == "Allow"
3
| summarize count() by SourceIP_s, DestinationIP_s, bin(TimeGenerated, 1h)
4
| order by TimeGenerated desc

Alert and Diagnostic Queries

Built-in diagnostic queries help you view alerts triggered by various Azure services.

1. Active Alerts:

xxxxxxxxxx
5
1
Alerts
2
| where Severity == "Critical" or Severity == "Warning"
3
| where State == "Active"
4
| project TimeGenerated, AlertName, Severity, Resource
5
| order by TimeGenerated desc

2. Triggered Alerts for Resource:

xxxxxxxxxx
4
1
Alerts
2
| where Resource == "myResource"
3
| project TimeGenerated, AlertName, Severity, State
4
| order by TimeGenerated desc

Custom Logs and Application Data Queries

Custom queries can be used to search custom log data that you have configured in Log Analytics.

1. Custom Log Search:

xxxxxxxxxx
4
1
CustomLogs_CL
2
| where LogLevel == "Error"
3
| project TimeGenerated, Message, LogLevel
4
| order by TimeGenerated desc

How to Use Built-in Queries

To use built-in queries:

1. Go to Azure Portal.

2. Navigate to Azure Monitor or Log Analytics.

3. Select Logs.

4. In the query window, you’ll find the "Built-in" queries under the "Query Explorer" section.

5. Click on the desired query to run it, or modify it to fit your needs.

Summary

These built-in queries are a great starting point for common monitoring, performance, security, and diagnostic tasks in Azure. You can adapt and expand on them based on your specific use cases.