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

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

Contents

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"]

These visualizations should help in understanding how different KQL syntax bundles fit together and build on each other for effective data querying and manipulation.