KQL Intermediate Level: Level Up Your KQL Skills

Take your KQL expertise to the next level with Intermediate Exploration and Visualization Techniques. This week focuses on mastering advanced text functions, datetime manipulation, and scalar transformations. You’ll learn to create stunning visualizations, analyze real-world datasets, and consolidate your skills with practical challenges. Get ready to turn data into actionable insights!

Kusto documentation - Kusto | Microsoft Learn

1: Advanced Functions - Part 1

Text Functions

KQL provides text functions to manipulate and filter string data. Here are some commonly used ones:

contains

Checks if a text field contains a specific substring (case-insensitive by default).

StormEvents 
| where EventType contains "Flood"

startswith

Finds rows where a column starts with a specific substring.

StormEvents 
| where State startswith "Cal"

endswith

Finds rows where a column ends with a specific substring.

StormEvents 
| where State endswith "nia"

regex

Matches rows using regular expressions.

StormEvents 
| where EventType matches regex @"^T.*o$"

(This matches EventType values starting with "T" and ending with "o", e.g., Tornado.)

2: Advanced Functions - Part 2

Datetime Functions

KQL provides powerful functions to analyze and manipulate datetime fields, which are essential for time-series data.

bin()

Groups data into bins (time intervals) for aggregation.

StormEvents 
| summarize EventCount = count() by bin(StartTime, 1h)

(This groups events into 1-hour intervals.)

format_datetime(): Converts a datetime value into a specific format.

StormEvents
| extend FormattedDate = format_datetime(StartTime, "yyyy-MM-dd HH:mm:ss")

datetime_add(): Adds days, months, etc., to a datetime value.

StormEvents 
| extend NextEventTime = datetime_add('hour', 1, StartTime)

Scalar Functions:

Scalar functions operate on single values to transform data:

tolower(), toupper(): Changes text to lower/uppercase.

StormEvents
| extend LowerCaseState = tolower(State) // Converts State column to lowercase
| extend UpperCaseEventType = toupper(EventType) // Converts EventType column to uppercase

round(): Rounds numerical values.

StormEvents 
| extend RoundedDamage = round(DamageProperty, 2)

Day 3: Data Visualization

The render Operator

KQL allows you to visualize data directly in tools like Azure Data Explorer or Log Analytics using the render operator.

Timechart:

StormEvents 
| summarize EventCount = count() by bin(StartTime, 1h) 
| render timechart

Pie Chart:

StormEvents 
| summarize TotalDamage = sum(DamageProperty) by EventType 
| render piechart

Column Chart:

StormEvents 
| summarize EventCount = count() by State 
| render columnchart

Day 4: Real-World Scenarios

Apply KQL to Logs and Telemetry Data

Analyze real-world datasets like logs, performance telemetry, or error data. Here’s an example:

Filter and Analyze Logs:

PerformanceLogs 
| where CounterName == "CPU Usage" 
| summarize AvgCPU = avg(CounterValue) by bin(Timestamp, 1h) 
| render timechart

Error Detection:

ErrorLogs 
| where Severity == "Critical" 
| summarize ErrorCount = count() by bin(Timestamp, 1d) 
| render columnchart

Summary

• Text Functions:

  • Learn to filter and manipulate text fields with:

    • contains: Find rows with a specific substring.

    • startswith: Filter rows where text starts with a substring.

    • endswith: Filter rows where text ends with a substring.

    • regex: Perform pattern matching using regular expressions.

• Datetime Functions:

  • Analyze time-series data using:

    • bin(): Group data into time intervals.

    • datetime_add(): Add time units to a datetime field.

• Scalar Functions:

  • Transform data with functions like tolower() (convert text to lowercase), toupper() (convert text to uppercase), and round() (round numerical values).

• Visualization:

  • Use the render operator to create visualizations, such as:

    • Timecharts (trends over time).

    • Pie charts (distribution of values).

    • Column charts (bar-like visualizations for categories).

  
  

// Filter rows where EventType contains "Flood" and State starts with "Cal"
// Also match EventType using a regex pattern (starts with "T" and ends with "o")
StormEvents
| where EventType contains "Flood" or State startswith "Cal" or EventType matches regex @"^T.*o$"

// Create a calculated column for TotalDamage (sum of DamageProperty and DamageCrop) and round it
| extend TotalDamage = round(DamageProperty + DamageCrop, 2)

// Convert State to lowercase and EventType to uppercase for uniform formatting
| extend LowerCaseState = tolower(State), UpperCaseEventType = toupper(EventType)

// Group data into daily intervals and calculate total damage and event count for each day
| summarize TotalDamageByDay = sum(TotalDamage), EventCount = count() by bin(StartTime, 1d)

// Add 1 day to the bin datetime to calculate the "NextDay" column
| extend NextDay = datetime_add('day', 1, bin(StartTime, 1d))

// Visualize total damage over time using a timechart
| project StartTime, NextDay, TotalDamageByDay, EventCount, LowerCaseState, UpperCaseEventType
| render timechart