Learn about the Structure Log Analytics Queries in Azure

Log Analytics queries in Azure are written using a query language called Kusto Query Language (KQL). The structure of Log Analytics queries typically involves the following components:

Data Sources (Tables)

Azure Log Analytics stores data in various tables based on the data type, such as AzureDiagnostics, Perf, SecurityEvent, Heartbeat, etc.

The query starts by specifying the table you want to query from. Each table contains data collected from different sources like logs, metrics, or activity.

Operators

Operators are used to manipulate or filter data. They define how data is retrieved, processed, and displayed.

Common operators include:

1. where – Filters data based on conditions.

2. project – Selects specific columns from the data.

3. summarize – Aggregates data, like counting, averaging, etc.

4. extend – Creates new columns derived from existing ones.

5. join – Combines data from different tables.

6. order by – Sorts data by specified columns.

Columns

Data returned from Log Analytics queries consists of columns, which are either raw log data or computed values.

Columns can be selected using operators like project, extend, or summarize.

Filters

Filters are used to limit the data returned by the query.

The where clause is typically used to apply filters based on conditions, such as date ranges or specific log values.

Aggregation

Aggregation involves grouping data and performing calculations like count(), avg(), sum(), etc. This can be done using the summarize operator.

Example:

summarize count() by bin(TimeGenerated, 1h) aggregates events by hour.

Time Range

Azure Log Analytics allows you to specify a time range for the data. For example, you can limit your query to the last 24 hours, the last 30 days, or a custom time range.

Time can be specified using TimeGenerated, which is typically the timestamp column for most log data.

Visualization

Once the query results are returned, you can visualize the data in different formats like tables, charts, maps, or time series graphs, depending on the nature of the data.

This is particularly useful in the Azure Monitor or Azure Sentinel dashboards for quick insights.

Example Query Structure

xxxxxxxxxx
6
1
AzureDiagnostics
2
| where TimeGenerated > ago(1d)  // Filter to get records from the last 24 hours
3
| where Resource == "myResource"  // Filter based on resource name
4
| project TimeGenerated, Resource, Message  // Select specific columns
5
| summarize Count = count() by bin(TimeGenerated, 1h)  // Group by 1 hour time bins and count the records
6
| order by TimeGenerated desc  // Sort by time in descending order

Key Concepts

1. Projection: You can select which columns to display with project.

2. Time Binning: bin() is useful to group data by specific time intervals.

3. Joins: You can join multiple tables if necessary (e.g., join performance data with security events).

4. Result Formatting: The result of the query is typically displayed in a tabular format, though it can be visualized in different ways.

Summary

By using KQL, you can create powerful queries to analyze logs, track metrics, and monitor your Azure environment effectively.