SC‐200_KQL_v3 - itnett/FTD02H-N GitHub Wiki

Learning Kusto Query Language (KQL) from 0% to 100%

Contents

Learning Kusto Query Language (KQL) from 0% to 100%

Step 1: Introduction to KQL

Kusto Query Language (KQL) is a powerful query language used for querying large datasets in Azure Data Explorer and Log Analytics. Here, we will take a step-by-step approach to mastering KQL from basics to advanced concepts. Each section will build on the previous one, so it's essential to follow along sequentially.

Step 2: Basic Syntax and Operations

Let's start with basic KQL syntax and operations.

2.1. Simple Query

Run your first query to fetch all records from the SecurityEvent table.

SecurityEvent
2.2. Filtering Data

Use the where operator to filter data.

SecurityEvent
| where TimeGenerated > ago(1h)
2.3. Searching Text

Search for a specific term across all columns.

search "location"

Step 3: Aggregations and Summarizations

Next, learn how to aggregate and summarize data.

3.1. Counting Rows

Count the number of rows in a specific time frame.

SecurityEvent
| where TimeGenerated > ago(1h)
| summarize count() by EventID
3.2. Summarizing Data

Use summarize to group by and aggregate data.

SecurityEvent
| where TimeGenerated > ago(1h)
| summarize count() by Account

Step 4: Advanced Filtering and Data Transformation

Enhance your queries with advanced filtering and transformations.

4.1. Using let Statements

Declare and use variables within queries.

let timeOffset = 1h;
SecurityEvent
| where TimeGenerated > ago(timeOffset)
4.2. Dynamic Lists

Create and use dynamic lists in queries.

let suspiciousAccounts = datatable(account: string) [
  @"NA\timadmin", 
  @"NT AUTHORITY\SYSTEM"
];
SecurityEvent
| where Account in (suspiciousAccounts)
4.3. Creating Calculated Columns

Use extend to add calculated columns.

SecurityEvent
| where TimeGenerated > ago(1h)
| extend StartDir = substring(ProcessName, 0, string_size(ProcessName)-string_size(Process))

Step 5: Data Visualization

Visualize your data using the render operator.

5.1. Bar Chart
SecurityEvent
| where TimeGenerated > ago(1h)
| summarize count() by Account
| render barchart
5.2. Time Series Chart
SecurityEvent
| where TimeGenerated > ago(1h)
| summarize count() by bin(TimeGenerated, 1m)
| render timechart

Step 6: Multi-Table Queries

Combine data from multiple tables.

6.1. Union Operator
SecurityEvent
| union SigninLogs
6.2. Join Operator
SecurityEvent
| where EventID == 4624
| join kind=inner (
  SigninLogs
  | where EventID == 4624
) on Account

Step 7: String Operations

Manipulate and extract information from string data.

7.1. Extract Function
print extract("x=([0-9.]+)", 1, "hello x=45.6|wo") == "45.6"
7.2. Parse Operator
let Traces = datatable(EventText:string) [
  "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)"
];
Traces
| parse EventText with * "resourceName=" resourceName ", totalSlices=" totalSlices:long * "sliceNumber=" sliceNumber:long * "lockTime=" lockTime ", releaseTime=" releaseTime:date "," * "previousLockTime=" previousLockTime:date ")" *
| project resourceName, totalSlices, sliceNumber, lockTime, releaseTime, previousLockTime

Step 8: Handling Dynamic Fields

Work with dynamic and JSON-like data.

8.1. Dynamic Fields
SigninLogs
| extend OS = DeviceDetail.operatingSystem
8.2. Expanding JSON Arrays
SigninLogs
| mv-expand AuthDetails = parse_json(AuthenticationDetails)
| project AuthDetails

Visualizations and Mind Maps in Markdown

Here are some visualizations and mind maps to help you understand the relationships between different KQL concepts:

KQL Concepts Mind Map

graph TD;
    A[KQL Basics] --> B[Filtering Data]
    A --> C[Aggregations]
    A --> D[Data Transformations]
    A --> E[Data Visualization]
    B --> F[where]
    C --> G[summarize]
    D --> H[let Statement]
    D --> I[extend]
    E --> J[render]
    J --> K[Bar Chart]
    J --> L[Time Series Chart]

KQL Data Flow

graph TD;
    A[Data Source] --> B[SecurityEvent]
    B --> C[Filter]
    C --> D[Aggregate]
    D --> E[Visualize]
    E --> F[Output]

Step 9: Practice and Application

Use the interactive lab simulation available at Microsoft Learning GitHub to practice writing KQL queries in a real environment. Ensure to clear

previous statements or open new query windows as needed.

By following these steps, you'll gradually build up your KQL skills from basic queries to complex data analysis and visualization. Happy querying!

KQL Syntax Bundles Explained with Graph TD

Below are detailed explanations of various KQL syntax bundles using graph TD for better understanding.

Basic Queries and Filtering

graph TD;
    A[KQL Syntax] --> B[Basic Queries]
    B --> C[Fetch All Records]
    C --> D["SecurityEvent"]
    B --> E[Filtering Data]
    E --> F[Using `where`]
    F --> G["SecurityEvent | where TimeGenerated > ago(1h)"]
    E --> H[Search Text]
    H --> I["search 'location'"]

Aggregations and Summarizations

graph TD;
    A[KQL Syntax] --> J[Aggregations]
    J --> K[Count Rows]
    K --> L["SecurityEvent | where TimeGenerated > ago(1h) | summarize count() by EventID"]
    J --> M[Summarize Data]
    M --> N["SecurityEvent | where TimeGenerated > ago(1h) | summarize count() by Account"]

Advanced Filtering and Data Transformation

graph TD;
    A[KQL Syntax] --> O[Advanced Filtering]
    O --> P[Using `let` Statements]
    P --> Q["let timeOffset = 1h; SecurityEvent | where TimeGenerated > ago(timeOffset)"]
    O --> R[Dynamic Lists]
    R --> S["let suspiciousAccounts = datatable(account: string) [@'NA\timadmin', @'NT AUTHORITY\SYSTEM']; SecurityEvent | where Account in (suspiciousAccounts)"]
    O --> T[Calculated Columns]
    T --> U["SecurityEvent | where TimeGenerated > ago(1h) | extend StartDir = substring(ProcessName, 0, string_size(ProcessName)-string_size(Process))"]

Data Visualization

graph TD;
    A[KQL Syntax] --> V[Data Visualization]
    V --> W[Bar Chart]
    W --> X["SecurityEvent | where TimeGenerated > ago(1h) | summarize count() by Account | render barchart"]
    V --> Y[Time Series Chart]
    Y --> Z["SecurityEvent | where TimeGenerated > ago(1h) | summarize count() by bin(TimeGenerated, 1m) | render timechart"]

Multi-Table Queries

graph TD;
    A[KQL Syntax] --> AA[Multi-Table Queries]
    AA --> AB[Union Operator]
    AB --> AC["SecurityEvent | union SigninLogs"]
    AA --> AD[Join Operator]
    AD --> AE["SecurityEvent | where EventID == 4624 | join kind=inner ( SigninLogs | where EventID == 4624) on Account"]

String Operations

graph TD;
    A[KQL Syntax] --> AF[String Operations]
    AF --> AG[Extract Function]
    AG --> AH["print extract('x=([0-9.]+)', 1, 'hello x=45.6|wo') == '45.6'"]
    AF --> AI[Parse Operator]
    AI --> AJ["let Traces = datatable(EventText:string) [ 'Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)' ]; Traces | parse EventText with * 'resourceName=' resourceName ', totalSlices=' totalSlices:long * 'sliceNumber=' sliceNumber:long * 'lockTime=' lockTime ', releaseTime=' releaseTime:date ',' * 'previousLockTime=' previousLockTime:date ')' * | project resourceName, totalSlices, sliceNumber, lockTime, releaseTime, previousLockTime"]

Handling Dynamic Fields

graph TD;
    A[KQL Syntax] --> AK[Handling Dynamic Fields]
    AK --> AL[Dynamic Fields]
    AL --> AM["SigninLogs | extend OS = DeviceDetail.operatingSystem"]
    AK --> AN[Expanding JSON Arrays]
    AN --> AO["SigninLogs | mv-expand AuthDetails = parse_json(AuthenticationDetails) | project AuthDetails"]

Step 10: Advanced KQL Techniques for Security in Azure

10.1. Anomaly Detection

Detect anomalies in log data to identify potential security threats.

SecurityEvent
| where EventID == 4625  // Failed logon event
| summarize FailureCount = count() by bin(TimeGenerated, 1h), Account
| where FailureCount > 10

10.2. Machine Learning Integration

Integrate KQL with machine learning models to enhance security analytics.

let model = view () { ... };  // Example machine learning model
SecurityEvent
| where EventID == 4624  // Successful logon event
| extend Score = model(Account, IPAddress)
| where Score > 0.8  // High risk score

10.3. Advanced Time Series Analysis

Perform advanced time series analysis to detect unusual patterns over time.

SecurityEvent
| where EventID == 4688  // Process creation event
| summarize ProcessCount = count() by bin(TimeGenerated, 1m)
| extend MovingAvg = moving_avg(ProcessCount, 10)
| where ProcessCount > 2 * MovingAvg

10.4. Custom Functions

Create custom functions for reusable logic in KQL queries.

let HighRiskLogons = (timeframe: datetime) {
  SecurityEvent
  | where EventID == 4624
  | where TimeGenerated > ago(timeframe)
  | summarize count() by Account
  | where count_ > 5
};
HighRiskLogons(1h)

10.5. KQL Best Practices for Security

Implement best practices for writing efficient and effective KQL queries in security analytics.

  • Use specific table and column names to reduce the search scope.
  • Apply filters as early as possible in the query.
  • Use summarize to aggregate large datasets.
  • Leverage dynamic data types and JSON parsing for complex logs.
  • Regularly review and optimize queries based on performance metrics.

By following these advanced techniques and best practices, you'll be able to leverage KQL for comprehensive security analytics in Azure.