Learn about ways to use Query Log Analytics Data in Azure

Querying Log Analytics data in Azure is done using Kusto Query Language (KQL), a powerful and flexible query language designed to analyze large volumes of structured, semi-structured, and unstructured data. Here’s how you can query Log Analytics data and best practices for doing so:

Accessing Log Analytics Workspace

Before you start querying, ensure that:

1. You have access to the Log Analytics workspace where your data is stored.

2. You have the required permissions (e.g., Reader or Contributor roles).

To access the workspace:

1. Go to the Azure Portal.

2. Navigate to Azure Monitor.

3. Under Log Analytics, select Workspaces.

4. Choose the workspace that holds the data you want to query.

Opening the Logs Blade

In the Log Analytics workspace, go to Logs. This opens the query editor where you can run your KQL queries.

You’ll see the query editor and a list of data tables from various data sources (e.g., Azure Activity Logs, VM performance metrics, Application Insights logs, etc.).

Writing Basic KQL Queries

KQL allows you to query and manipulate log data. Here are some basic examples:

1. Select all records from a table:

xxxxxxxxxx
1
1
Heartbeat

2. Filtering data: To filter records based on a condition:

xxxxxxxxxx
2
1
Heartbeat
2
| where Computer == "myvm01"

3. Select specific columns: To select specific columns, use the project operator:

xxxxxxxxxx
3
1
Heartbeat
2
| where Computer == "myvm01"
3
| project TimeGenerated, Computer, OSName

4. Sorting the data: To sort the data based on a column (e.g., TimeGenerated):

xxxxxxxxxx
4
1
Heartbeat
2
| where Computer == "myvm01"
3
| project TimeGenerated, Computer, OSName
4
| order by TimeGenerated desc

5. Aggregating data: To perform aggregations (e.g., count, sum, average), use operators like summarize:

xxxxxxxxxx
2
1
Heartbeat
2
| summarize Count = count() by Computer

Using Time-Based Queries

1. Filtering by time range: You can use the TimeGenerated column (or any time-based column) to filter records by a specific time range:

xxxxxxxxxx
2
1
Heartbeat
2
| where TimeGenerated > ago(1d)

2. Using bin() for time bucketing: You can group records into time intervals, such as hours, days, or weeks:

xxxxxxxxxx
2
1
Heartbeat
2
| summarize Count = count() by bin(TimeGenerated, 1h)

Advanced Query Examples

1. Joining data from different tables: You can join data from multiple tables using the join operator:

xxxxxxxxxx
2
1
Heartbeat
2
| join kind=inner (AzureActivity) on Computer

2. Using extend to create new columns: You can add new columns based on existing data:

xxxxxxxxxx
3
1
Heartbeat
2
| extend DayOfWeek = dayofweek(TimeGenerated)
3
| summarize Count = count() by DayOfWeek

3. Top N values: To get the top N records based on a certain column:

xxxxxxxxxx
3
1
Heartbeat
2
| summarize Count = count() by Computer
3
| top 5 by Count desc

Using Operators in KQL

KQL provides a wide range of operators for advanced filtering, aggregation, and analysis:

1. project: Select specific columns.

2. summarize: Perform aggregation operations.

3. extend: Create new columns based on existing data.

4. where: Filter the data based on a condition.

5. join: Combine data from different tables.

6. order by: Sort the results.

7. limit: Limit the number of rows returned.

8. mv-expand: Expand multi-valued fields.

9. parse: Extract values from a string field.

Querying Data with a Time Range

1. Fixed time range: You can specify a fixed time range for your queries using datetime values:

xxxxxxxxxx
2
1
Heartbeat
2
| where TimeGenerated between (datetime(2024-12-01) .. datetime(2024-12-10))

2. Relative time range: Use the ago() function to query data relative to the current time (e.g., past 1 hour, past 30 minutes):

xxxxxxxxxx
2
1
Heartbeat
2
| where TimeGenerated > ago(1h)

Visualizing Query Results

After running a query, you can visualize the results:

Use the "Chart" option to view the results in a graph, such as bar charts, pie charts, or time-series plots.

Use Workbooks to create more detailed and interactive dashboards based on your query results.

Saving and Sharing Queries

1. Save queries: You can save commonly used queries for future use. Click on the "Save" button in the query editor and provide a name for the query.

2. Sharing queries: You can share saved queries with team members via Workbooks or by exporting queries.

Using Query Templates

Azure provides predefined query templates to help you get started with common log analytics tasks, such as:

1. Monitoring performance metrics.

2. Analyzing security events.

3. Troubleshooting application issues.

Advanced Query Tips

1. Use let for defining variables: You can define intermediate results using let to simplify complex queries:

xxxxxxxxxx
2
1
let recent_heartbeat = Heartbeat | where TimeGenerated > ago(1d);
2
recent_heartbeat | summarize Count = count() by Computer

2. Optimizing Queries: To avoid scanning large datasets, try to limit the time range (ago()) and avoid unnecessary joins or large result sets. Always filter early and project only the columns you need.

Querying Data from Multiple Sources

Azure Log Analytics allows you to query logs across multiple Azure services (e.g., Azure Activity Logs, VM Performance Metrics, Network Logs) by referencing different tables.

Example of querying Activity Logs alongside Heartbeat logs:

xxxxxxxxxx
3
1
Heartbeat
2
| join kind=inner (AzureActivity) on Computer
3
| where TimeGenerated > ago(1d)

Using Logs in Azure Sentinel (Security)

Security Data: If you have Azure Sentinel integrated with your Log Analytics workspace, you can query security events and use advanced threat detection queries like anomaly detection, network traffic analysis, and security alerts.

Summary

Querying Log Analytics data in Azure using KQL allows you to unlock powerful insights from your logs. By using the Log Analytics query editor, you can filter, aggregate, and analyze log data from across your Azure environment. Optimize your queries for performance and cost by limiting data and focusing on what matters most. Additionally, you can visualize the results and set up alerts to automate responses to critical events.