Interrogate logs using basic Kusto Query Language KQL queries

In this lesson, we explore how to interrogate logs using basic Kusto Query Language (KQL) queries in Log Analytics—a critical skill for DevOps engineers managing Azure DevOps pipelines and applications. Mastering these fundamental techniques in filtering, summarizing, and querying logs is essential for effective monitoring, troubleshooting, and security auditing.

Before diving into writing queries, here’s an overview of common log types in Azure DevOps:

• Pipeline logs – Capture critical details about your CI/CD pipelines and build/release processes.
• Build logs – Provide detailed insights into each step of the build process.
• Application logs – Record information directly generated by running applications.

Filtering Log Data in Log Analytics

Filtering is the first step in efficient log analysis. The following steps will guide you through accessing Log Analytics and filtering log data effectively:

1. Open the Azure portal and search for Log Analytics.
2. Select one of your Log Analytics workspaces.
3. Navigate to the "Logs" section to access the query hub.

Once inside your workspace, you are ready to start querying log data. The image below illustrates the Log Analytics workspace interface:

Querying Security Events

To begin, let’s query the SecurityEvent table to display security logs from the past day. This query filters events based on the time generated, projects key columns, and limits the results to 10 records:

SecurityEvent
| where TimeGenerated > ago(1d)
| project TimeGenerated, Account, EventID, IPAddress, Computer
| take 10

This query filters the SecurityEvent table for events generated within the last day, selecting the columns TimeGenerated, Account, EventID, IPAddress, and Computer. You can adjust the time range—for example, querying for the last 10 days:

SecurityEvent
| where TimeGenerated > ago(10d)
| project TimeGenerated, Account, EventID, IpAddress, Computer
| take 10

Filtering for Failed Login Attempts

To identify failed login attempts specifically, use event ID 4625. Begin by querying over the last day:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| project TimeGenerated, Account, EventID, IpAddress, Computer
| take 10

If no records are returned, expand the time range to the last 30 days:

SecurityEvent
| where TimeGenerated > ago(30d)
| where EventID == 4625
| project TimeGenerated, Account, EventID, IpAddress, Computer
| take 10

Summarizing Failed Login Attempts

To get a summarized view of failed login attempts by account, use the summarize operator to count occurrences and order the results in descending order:

SecurityEvent
| where EventID == 4625
| summarize FailedLogins = count() by Account
| order by FailedLogins desc

This query aggregates the data by account and orders accounts based on the number of failed login attempts. Note that the data shown in this demonstration is simulated and provided by Microsoft for demo purposes.

Querying Azure Resource Diagnostics Logs

Azure resource logs can be queried from the AzureDiagnostics table. The example below fetches operational logs from the past seven days:

AzureDiagnostics
| where TimeGenerated > ago(7d)
| project TimeGenerated, Resource, ResourceGroup, OperationName, ResultType
| take 10

To narrow the results to operations that have failed, add an additional filtering condition:

AzureDiagnostics
| where TimeGenerated > ago(7d)
| where ResultType == "Failed"
| project TimeGenerated, Resource, ResourceGroup, OperationName, ResultType
| take 10

This query demonstrates stacking filters in KQL by filtering based on time, result type, projecting the necessary columns, and finally limiting the output.

Navigating the Log Analytics User Interface

The updated Log Analytics interface offers flexibility between a simple drag-and-drop mode and KQL scripting mode. Follow these steps to efficiently navigate the interface:

1. Select a table from the categories on the left.
2. Apply filters to refine your log data (e.g., URL, HTTP method, or response code).
3. Set the desired time range and record limit—commonly 24 hours and up to 1,000 records.

The interface supports filtering options such as URL, client OS details, and more to enhance your log analysis experience.

Sharing and Exporting Queries

Once you have built effective queries, you can easily share them through Log Analytics. Options include:

• Copying a direct link to the query.
• Exporting results as CSV files.
• Copying displayed results for further analysis.

Sharing the query link is a more secure alternative since it requires authorized access to view the logs.

Conclusion

This lesson provided an in-depth overview of using KQL to effectively analyze various log types within Azure. We covered key techniques including basic filtering, identifying failed login attempts, summarizing data, and exploring the powerful features of the Log Analytics interface. These skills are essential for maintaining robust security audits, monitoring system health, and troubleshooting operational issues efficiently.

Thank you for reading this lesson on interrogating logs using basic Kusto Query Language (KQL) queries. For additional resources on Azure DevOps and log analysis, explore related guides and documentation.

Happy querying!