### Track Abnormal Service Creation in KQL

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query monitors DeviceEvents for service installations within the last hour. It specifically looks for services installed in common temporary or user-specific directories like 'temp', 'appdata', 'users', or 'public', flagging potentially malicious activity.

```kusto
DeviceEvents
| where Timestamp > ago(1h)
| where ActionType == "ServiceInstalled"
| extend ServiceName = tostring(parse_json(AdditionalFields).ServiceName)
| extend ServicePath = tostring(parse_json(AdditionalFields).ServiceFileName)
| where ServicePath has_any ("temp", "appdata", "users", "public")
| project Timestamp, DeviceName, ServiceName, ServicePath, AccountName
```

--------------------------------

### Get File Event Information (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query retrieves file event information for devices running a specific client version. It filters devices with a client version starting with '20.1' from the last day and then joins with DeviceFileEvents to get file event details.

```kql
DeviceInfo
| where Timestamp > ago(1d)
| where ClientVersion startswith "20.1"
| summarize by DeviceId
| join kind=inner (
    DeviceFileEvents
    | where Timestamp > ago(1d)
) on DeviceId
| take 10
```

--------------------------------

### Check Device Onboarding Status (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query checks the onboarding status of devices. It filters DeviceInfo for devices where the 'OnboardingStatus' is not 'Onboarded' and retrieves details for these devices.

```kql
DeviceInfo
| where Timestamp > ago(1d)
| where OnboardingStatus != "Onboarded"
| summarize by DeviceId
| join kind=inner (
    DeviceInfo
    | where Timestamp > ago(1d)
) on DeviceId
| take 10
```

--------------------------------

### Get Network Event Information (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query retrieves network event information for devices running a specific client version. It filters devices with a client version starting with '20.1' from the last day and then joins with DeviceNetworkEvents to get network event details.

```kql
DeviceInfo
| where Timestamp > ago(1d)
| where ClientVersion startswith "20.1"
| summarize by DeviceId
| join kind=inner (
    DeviceNetworkEvents
    | where Timestamp > ago(1d)
) on DeviceId
| take 10
```

--------------------------------

### Efficient Join Operations

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Shows an example of an efficient inner join between email events and attachment information. It filters for suspicious emails and then joins with attachment data to find associated file details, optimizing data retrieval.

```kusto
let SuspiciousEmails = EmailEvents
| where Timestamp > ago(1h)
| where ThreatTypes != ""
| distinct NetworkMessageId, RecipientEmailAddress;
SuspiciousEmails
| join kind=inner (
    EmailAttachmentInfo
    | where Timestamp > ago(1h)
) on NetworkMessageId
| project NetworkMessageId, RecipientEmailAddress, FileName, SHA256
```

--------------------------------

### Optimize Large Dataset Queries

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Demonstrates how to optimize queries on large datasets by using `arg_min` to retrieve the earliest record for a specific identifier (SHA256). This reduces data processed and improves query performance.

```kusto
DeviceProcessEvents
| where Timestamp > ago(1h)
| where isnotempty(SHA256)
| summarize arg_min(Timestamp, *) by SHA256, DeviceId
| project-reorder Timestamp, DeviceName, FileName, SHA256
```

--------------------------------

### Basic KQL Sort Operator Example

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

A basic example demonstrating the KQL 'sort' operator to sort StormEvents data by State in ascending order and StartTime in descending order.

```kql
StormEvents
| sort by State asc, StartTime desc
```

--------------------------------

### Device Vulnerability Summary

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Provides a summary of vulnerabilities detected on devices, categorized by severity level. It helps in prioritizing patching and remediation efforts based on the impact of vulnerabilities.

```kusto
DeviceTvmSoftwareVulnerabilities
| summarize VulnerabilityCount = dcount(CveId) by DeviceName, VulnerabilitySeverityLevel
| order by VulnerabilityCount desc
```

--------------------------------

### Get Device Information for Compromised Account (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query retrieves alert information for devices associated with a compromised account. It first finds devices logged on by the specified account, then joins with alert evidence to get alert IDs, and finally retrieves details from the alert info.

```kql
DeviceInfo
| where LoggedOnUsers contains '<account-name>'
| distinct DeviceId
| join kind=inner AlertEvidence on DeviceId
| project AlertId
| join AlertInfo on AlertId
| project AlertId, Timestamp, Title, Severity, Category
```

--------------------------------

### KQL Data Types

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Demonstrates the declaration and usage of various KQL data types including Boolean, Numbers, Strings, DateTime, TimeSpan, Dynamic (JSON), and GUID. Includes examples of null handling.

```kql
// Boolean
let flag = true;
let disabled = false;

// Numbers
let integer = 42;
let longNum = 9223372036854775807;
let realNum = 3.14159;
let decimalNum = decimal(123.456);

// Strings
let text = "Hello, World!";
let multiline = @"Line 1
Line 2";

// DateTime and TimeSpan
let timestamp = datetime(2023-01-01 12:00:00);
let currentTime = now();
let duration = 2d + 3h + 45m + 30s;

// Dynamic (JSON-like)
let jsonObj = dynamic({"name":"Alice", "age":30});
let jsonArray = dynamic([1,2,3,4,5]);

// GUID
let uniqueId = guid(74be27de-1e4e-49d9-b579-fe0b331d3642);
let newId = new_guid();

// Null handling
let nullValue = real(null);
```

--------------------------------

### Process Creation Frequency

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Identifies processes that are created more than 10 times per hour, along with their folder paths. This can help detect unusual or excessive process activity that might indicate malicious behavior.

```kusto
DeviceProcessEvents
| where Timestamp > ago(1h)
| summarize ProcessCount = count() by FileName, FolderPath
| where ProcessCount > 10
| order by ProcessCount desc
```

--------------------------------

### KQL Query Optimization Examples

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Demonstrates best practices for optimizing KQL queries, including early filtering, combining conditions, using materialized views, employing shuffle hints for large joins, and limiting scanned data with time ranges.

```kql
// Good: Filter early
Table | where TimeGenerated > ago(1d) | where Status == "Error" 

// Better: Combine filters
Table | where TimeGenerated > ago(1d) and Status == "Error"

// Use materialized views for frequently accessed aggregations
materialized_view("DailyStats")

// Use shuffle for large joins
Table1 | join kind=inner hint.strategy=shuffle (Table2) on Key

// Limit data scanned
Table | where TimeGenerated between (starttime .. endtime)
```

--------------------------------

### Detect Registry Persistence Mechanisms

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Identifies suspicious modifications to common registry run keys used for persistence. This query helps detect malware or unauthorized applications that aim to survive reboots.

```kusto
DeviceRegistryEvents
| where Timestamp > ago(1h)
| where RegistryKey has @"CurrentVersion\Run" 
    or RegistryKey has @"CurrentVersion\RunOnce"
    or RegistryKey has @"CurrentVersion\RunServices"
| where ActionType == "RegistryValueSet"
| project Timestamp, DeviceName, RegistryKey, RegistryValueName, RegistryValueData, InitiatingProcessFileName, AccountName
```

--------------------------------

### KQL Sort with Take Operator Example

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

An example combining the KQL 'sort' and 'take' operators to sort StormEvents data and then take a specified number of records.

```kql
StormEvents
| sort by State asc, StartTime desc
| take 10
```

--------------------------------

### Detect Privilege Escalation

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Identifies privilege escalation attempts by looking for processes that run with elevated privileges (TokenElevationTypeFull) while being initiated by a process that does not have the same level of elevation. Excludes common system processes.

```kusto
DeviceProcessEvents
| where Timestamp > ago(1h)
| where ProcessTokenElevation == "TokenElevationTypeFull"
| where InitiatingProcessTokenElevation != "TokenElevationTypeFull"
| where InitiatingProcessFileName !in~ ("services.exe", "svchost.exe", "wininit.exe")
| project Timestamp, DeviceName, FileName, ProcessCommandLine, 
    InitiatingProcessFileName, InitiatingProcessCommandLine, AccountName
```

--------------------------------

### Email Threat Summary by Day

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Summarizes the count of different email threat types detected over the past 30 days, visualized as a time chart. This helps in understanding the trends and types of email-based threats.

```kusto
EmailEvents
| where Timestamp > ago(30d)
| where ThreatTypes != ""
| summarize ThreatCount = count() by ThreatTypes, bin(Timestamp, 1d)
| render timechart
```

--------------------------------

### Monitor Suspicious PowerShell Activities

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Detects suspicious PowerShell command-line arguments, such as encoded commands, bypass execution policies, or download/execute commands. This query helps identify potentially malicious script execution.

```kusto
DeviceProcessEvents
| where Timestamp > ago(1h)
| where FileName =~ "powershell.exe"
| where ProcessCommandLine has_any ("-EncodedCommand", "-enc", "-e", 
    "bypass", "hidden", "noprofile", "downloadstring", "invoke-expression")
| project Timestamp, DeviceName, ProcessCommandLine, AccountName, InitiatingProcessFileName
```

--------------------------------

### Get User Accounts from Email Addresses (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query extracts user account names from email addresses found in EmailEvents. It filters events from the last 7 days and projects the recipient's email address and the extracted account name.

```kql
EmailEvents
| where Timestamp > ago(7d)
| project RecipientEmailAddress, AccountName = tostring(split(RecipientEmailAddress, "@")[0])
```

--------------------------------

### Get Logon Attempts After Credential Theft (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query identifies logon attempts following potential credential theft events. It filters alerts categorized as 'CredentialAccess' from the last 30 days, joins them with alert evidence to extract account SID, and then correlates with identity logon events.

```kql
AlertInfo
| where Timestamp > ago(30d)
| where Category == "CredentialAccess"
| join AlertEvidence on AlertId
| extend IsJoined=(parse_json(AdditionalFields).Account.IsDomainJoined)
| extend TargetAccountSid=tostring(parse_json(AdditionalFields).Account.Sid)
| where IsJoined has "true"
| join kind=inner IdentityLogonEvents on $left.TargetAccountSid == $right.AccountSid
| project AccountDisplayName, TargetAccountSid, Application, Protocol, DeviceName, LogonType
```

--------------------------------

### KQL GUID Operations

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/data-types.md

Shows how to generate new GUIDs, parse GUID strings, convert GUIDs to strings, and filter data based on GUID values.

```kql
print 
    new_guid = new_guid(),
    parsed = guid("12345678-1234-1234-1234-123456789012"),
    as_string = tostring(guid(74be27de-1e4e-49d9-b579-fe0b331d3642))

datatable(id:guid, name:string)
[
    guid(74be27de-1e4e-49d9-b579-fe0b331d3642), "Item1",
    guid(c49d8b3a-4b5e-4c7a-9f8a-2e3456789abc), "Item2"
]
| where id == guid(74be27de-1e4e-49d9-b579-fe0b331d3642)
```

--------------------------------

### Find Suspicious Process Creation (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query finds suspicious process creation events. It filters DeviceProcessEvents for processes with 'Low' integrity level and 'TokenElevationTypeFull', indicating potential privilege escalation.

```kql
DeviceProcessEvents
| where Timestamp > ago(1h)
| where ProcessIntegrityLevel == "Low" and ProcessTokenElevation == "TokenElevationTypeFull"
| project Timestamp, DeviceName, FileName, ProcessCommandLine, AccountName, InitiatingProcessFileName
```

--------------------------------

### KQL Window Functions

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Demonstrates the use of KQL window functions for performing calculations across sets of table rows related to the current row. Examples include accessing previous/next values, row numbering, running sums, and ranking.

```kql
TableName
| sort by Timestamp asc
| extend PrevValue = prev(Value)
| extend NextValue = next(Value)
| extend RowNum = row_number()
| extend RunningSum = row_cumsum(Value)
| extend Rank = row_rank()
```

--------------------------------

### List Logon Activities After Failed ZAP (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query lists logon activities that occurred within 24 hours after a failed ZAP (Zero-hour Auto Purge) action in email events. It joins EmailPostDeliveryEvents with IdentityLogonEvents to correlate these activities.

```kql
EmailPostDeliveryEvents
| where Timestamp > ago(7d)
| where ActionType has "ZAP" and ActionResult == "Error"
| project ZapTime = Timestamp, ActionType, NetworkMessageId , RecipientEmailAddress
| join kind=inner IdentityLogonEvents on $left.RecipientEmailAddress == $right.AccountUpn
| where Timestamp between ((ZapTime-24h) .. (ZapTime+24h))
| project ZapTime, ActionType, NetworkMessageId , RecipientEmailAddress, AccountUpn,
LogonTime = Timestamp, AccountDisplayName, Application, Protocol, DeviceName, LogonType
```

--------------------------------

### Detect Lateral Movement

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Identifies potential lateral movement by correlating logon events with process execution of tools commonly used for remote access and execution, such as psexec or wmic. It flags suspicious activity across devices.

```kusto
IdentityLogonEvents
| where Timestamp > ago(4h)
| where LogonType in ("RemoteInteractive", "Network")
| where AccountName !endswith "$"
| join kind=inner (
    DeviceProcessEvents
    | where Timestamp > ago(4h)
    | where FileName in~ ("psexec.exe", "wmic.exe", "winrs.exe", "mstsc.exe")
) on DeviceName
| project Timestamp, AccountName, DeviceName, LogonType, FileName, ProcessCommandLine
```

--------------------------------

### Failed Logon Attempts by Account

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Lists accounts with more than 5 failed logon attempts in the last day, along with the reason for failure. This query is useful for detecting brute-force attacks or account compromise attempts.

```kusto
IdentityLogonEvents
| where Timestamp > ago(1d)
| where ActionType == "LogonFailed"
| summarize FailedAttempts = count() by AccountName, AccountDomain, FailureReason
| where FailedAttempts > 5
| order by FailedAttempts desc
```

--------------------------------

### Top Targeted Users by Email Threats

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Identifies the top 20 users who have been targeted by phishing or malware emails in the last 7 days. This helps in prioritizing user awareness training and incident response.

```kusto
EmailEvents
| where Timestamp > ago(7d)
| where ThreatTypes has "Phish" or ThreatTypes has "Malware"
| summarize AttemptCount = count() by RecipientEmailAddress
| top 20 by AttemptCount desc
```

--------------------------------

### Find Devices Running Outdated macOS (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query identifies devices running outdated macOS versions. It filters DeviceInfo for macOS devices that do not contain specific version numbers (e.g., '10.15', '11.') and then joins to ensure accurate device identification.

```kql
DeviceInfo
| where Timestamp > ago(1d)
| where OSPlatform == "macOS" and  OSVersion !contains "10.15" and OSVersion !contains "11."
| summarize by DeviceId
| join kind=inner (
    DeviceInfo
    | where Timestamp > ago(1d)
) on DeviceId
| take 10
```

--------------------------------

### KQL 'sort' and 'order' Operators - Sort Results

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Demonstrates sorting query results in ascending or descending order using 'sort' and 'order' operators. Includes examples of limiting results with 'top' and 'top-nested' for hierarchical sorting.

```kql
TableName
| sort by Column desc
| order by Timestamp asc, Value desc
| top 10 by Value
| top-nested 5 of Category by sum(Value), top-nested 3 of Product by avg(Rating)
```

--------------------------------

### Time-Based Aggregation

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Performs time-based aggregation on network events, summarizing counts, data transferred, and unique remote IPs per hour for each device. This helps in analyzing network traffic patterns over time.

```kusto
DeviceNetworkEvents
| where Timestamp > ago(1d)
| summarize NetworkEvents = count(), 
            DataTransferred = sum(tolong(AdditionalFields)),
            UniqueRemoteIPs = dcount(RemoteIP)
    by bin(Timestamp, 1h), DeviceName
| order by Timestamp desc
```

--------------------------------

### Track File Modifications by Process

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Monitors file modifications made by specific processes like PowerShell, cmd, and script interpreters. It helps identify potentially malicious file activity initiated by common scripting tools.

```kusto
DeviceFileEvents
| where Timestamp > ago(1h)
| where ActionType == "FileModified"
| where InitiatingProcessFileName in~ ("powershell.exe", "cmd.exe", "wscript.exe", "cscript.exe")
| project Timestamp, DeviceName, FileName, FolderPath, InitiatingProcessFileName, InitiatingProcessCommandLine, AccountName
```

--------------------------------

### KQL 'evaluate' Operator - Advanced Analytics

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Provides examples of advanced analytics functions available through the 'evaluate' operator, such as pivoting data, unpacking dynamic bags, and performing time series decomposition.

```kql
// Pivot
TableName
| evaluate pivot(Category, sum(Value))

// Bag unpack
TableName
| evaluate bag_unpack(Properties)

// Time series analysis
TableName
| make-series Value = sum(Metric) on Timestamp step 1h
| evaluate series_decompose(Value)
```

--------------------------------

### KQL DateTime Functions

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Illustrates the usage of KQL functions for handling date and time values, including getting current time, creating dates, extracting date parts, performing date arithmetic, rounding dates, and formatting date/time strings.

```kql
// Current time
print now()
print now(-1d)  // Yesterday
print ago(1h)   // 1 hour ago

// Date creation
print datetime(2023-01-01)
print datetime(2023-01-01 12:30:45)
print make_datetime(2023, 1, 1, 12, 30, 45)

// Date parts
print year = datetime_part("year", now())
print month = datetime_part("month", now())
print day = datetime_part("day", now())
print hour = datetime_part("hour", now())
print dayofweek = dayofweek(now())
print dayofyear = dayofyear(now())

// Date arithmetic
print datetime_add("day", 7, now())
print datetime_add("hour", -3, now())
print datetime_diff("day", datetime(2023-01-01), now())

// Date rounding
print startofday(now())
print endofday(now())
print startofweek(now())
print startofmonth(now())
print startofyear(now())

// Date formatting
print format_datetime(now(), "yyyy-MM-dd HH:mm:ss")
print format_datetime(now(), "yy-MMM-dd")
```

--------------------------------

### Review PowerShell Activities After Suspicious Email (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query reviews PowerShell activities that occurred within 30 minutes after a user received an email from a malicious sender. It identifies suspicious emails and then correlates them with PowerShell process events on devices.

```kql
let EmailsFromBadSender=EmailEvents
| where SenderFromAddress =~ "MaliciousSender@example.com"
| project TimeEmail = Timestamp, Subject, SenderFromAddress, AccountName = tostring(split(RecipientEmailAddress, "@")[0]);
EmailsFromBadSender
| join (
DeviceProcessEvents
| where FileName =~ "powershell.exe"
| project TimeProc = Timestamp, AccountName, DeviceName, InitiatingProcessParentFileName, InitiatingProcessFileName, FileName, ProcessCommandLine
) on AccountName
| where (TimeProc - TimeEmail) between (0min.. 30min)
```

--------------------------------

### Monitor Network Connections to Suspicious IPs

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Detects network connections to public IP addresses on commonly exploited ports such as RDP, SSH, and SMB. This query helps identify potential unauthorized access or data exfiltration attempts.

```kusto
DeviceNetworkEvents
| where Timestamp > ago(1h)
| where RemoteIPType == "Public"
| where RemotePort in (3389, 22, 23, 445, 1433)
| project Timestamp, DeviceName, RemoteIP, RemotePort, InitiatingProcessFileName, AccountName
```

--------------------------------

### Get All User Creation Events

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/sentinel/azure-monitor-auditlogs.md

Retrieves all user creation events from the AuditLogs table within the last 7 days. It projects the generation time, initiator, target resources, and the result of the operation.

```kusto
AuditLogs
| where TimeGenerated > ago(7d)
| where ActivityDisplayName == "Add user"
| project TimeGenerated, InitiatedBy, TargetResources, Result
```

--------------------------------

### Find Data Exfiltration Attempts

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

Detects potential data exfiltration by identifying large file creation events (over 1MB) that occur shortly before outbound network connections on common ports (21, 22, 443, 445). This helps uncover attempts to steal sensitive data.

```kusto
DeviceNetworkEvents
| where Timestamp > ago(1h)
| where RemotePort in (21, 22, 443, 445)
| join kind=inner (
    DeviceFileEvents
    | where Timestamp > ago(1h)
    | where ActionType == "FileCreated" and FileSize > 1048576
) on DeviceId
| where (Timestamp - Timestamp1) between (0min .. 5min)
| project Timestamp, DeviceName, FileName, FileSize, RemoteIP, RemotePort
```

--------------------------------

### Review Logon Attempts After Suspicious Emails (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query reviews logon attempts that occurred within 30 minutes after a user received a suspicious email. It first identifies suspicious emails and then joins with identity logon events to find subsequent logon activities on the same account.

```kql
let MaliciousEmails=EmailEvents
| where ThreatTypes has "Malware"
| project TimeEmail = Timestamp, Subject, SenderFromAddress, AccountName = tostring(split(RecipientEmailAddress, "@")[0]);
MaliciousEmails
| join (
IdentityLogonEvents
| project LogonTime = Timestamp, AccountName, DeviceName
) on AccountName
| where (LogonTime - TimeEmail) between (0min.. 30min)
| take 10
```

--------------------------------

### KQL Working with Dynamic Data

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/data-types.md

Illustrates how to access properties within dynamic data structures, perform operations on dynamic arrays like getting length and sum, and extract data from dynamic objects in a datatable.

```kql
let data = dynamic({"user":"alice", "score":95, "tags":["admin","power-user"]});
print 
    user = data.user,
    score = data.score,
    firstTag = data.tags[0]

let numbers = dynamic([1,2,3,4,5]);
print 
    length = array_length(numbers),
    sum = array_sum(numbers),
    reversed = array_reverse(numbers)

datatable(id:int, properties:dynamic)
[
    1, dynamic({"name":"Server1", "cpu":80}),
    2, dynamic({"name":"Server2", "cpu":65})
]
| extend name = properties.name, cpu = properties.cpu
```

--------------------------------

### KQL 'summarize' Operator - Aggregate Data

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Shows how to aggregate data in KQL using the 'summarize' operator. Examples include counting rows, calculating sums, averages, minimums, maximums, percentiles, distinct counts, and collecting values into lists or sets.

```kql
TableName
| summarize Count = count() by Category
| summarize Sum = sum(Value), Avg = avg(Value) by bin(Timestamp, 1h)
| summarize Min = min(Value), Max = max(Value), Percentile = percentile(Value, 95)
| summarize DistinctCount = dcount(UserId)
| summarize Values = make_list(Value), UniqueValues = make_set(Value)
```

--------------------------------

### Detect Abnormal Service Creation (Defender)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

Detects the installation of services with suspicious file paths (e.g., temp, appdata) within the last hour. It parses additional fields to extract service name and path, then projects relevant details.

```kql
DeviceEvents
| where Timestamp > ago(1h)
| where ActionType == "ServiceInstalled"
| extend ServiceName = tostring(parse_json(AdditionalFields).ServiceName)
| extend ServicePath = tostring(parse_json(AdditionalFields).ServiceFileName)
| where ServicePath has_any ("temp", "appdata", "users", "public")
| project Timestamp, DeviceName, ServiceName, ServicePath, AccountName
```

--------------------------------

### Get All User Creation Events (Sentinel)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

Retrieves all user creation events from Azure Audit Logs within the last 7 days. It filters for the 'Add user' activity and projects the timestamp, initiator, target resources, and result.

```kql
AuditLogs
| where TimeGenerated > ago(7d)
| where ActivityDisplayName == "Add user"
| project TimeGenerated, InitiatedBy, TargetResources, Result
```

--------------------------------

### Check Files from Malicious Sender on Devices (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query checks for files associated with a known malicious sender that have been seen on devices. It filters email attachment information for a specific sender, extracts SHA256 hashes, and joins with device file events to find matching files on devices.

```kql
EmailAttachmentInfo
| where SenderFromAddress =~ "MaliciousSender@example.com"
| where isnotempty(SHA256)
| join (
DeviceFileEvents
| project FileName, SHA256, DeviceName, DeviceId
) on SHA256
| project Timestamp, FileName , SHA256, DeviceName, DeviceId,  NetworkMessageId, SenderFromAddress, RecipientEmailAddress
```

--------------------------------

### Query Device Software Inventory

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/mde-table-schemas.md

This KQL query retrieves information about software installed on devices, including device details, software vendor, name, version, and support status. It helps in identifying software assets and their lifecycle status.

```kql
DeviceSoftwareInventory
| project DeviceId, DeviceName, OSPlatform, SoftwareVendor, SoftwareName, SoftwareVersion, EndOfSupportStatus, EndOfSupportDate
```

--------------------------------

### KQL 'extend' Operator - Add Calculated Columns

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Shows how to add new calculated columns to a table using the 'extend' operator. Examples include extracting parts of a datetime, creating boolean flags, parsing JSON, and accessing JSON fields.

```kql
TableName
| extend NewColumn = Expression
| extend Hour = datetime_part("hour", Timestamp)
| extend IsWeekend = dayofweek(Timestamp) in (0d, 6d)
| extend ParsedJson = parse_json(JsonColumn)
| extend Field = ParsedJson.field
```

--------------------------------

### Merge Identity Information with Email Events (KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/kql-query-examples.md

This KQL query merges identity information with email events to enrich security data. It filters email events for malware or phishing threats from the last 7 days and joins them with identity information based on the recipient's email address.

```kql
EmailEvents
| where Timestamp > ago(7d)
| where ThreatTypes has "Malware" or ThreatTypes has "Phish"
| join (IdentityInfo | distinct AccountUpn, AccountDisplayName, JobTitle,
Department, City, Country) on $left.RecipientEmailAddress == $right.AccountUpn
| project Timestamp, NetworkMessageId, Subject, ThreatTypes,
SenderFromAddress, RecipientEmailAddress, AccountDisplayName, JobTitle,
Department, City, Country
```

--------------------------------

### Analyze Disk I/O

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Examines disk input/output operations per second (IOPS) for logical disks, excluding the '_Total' instance. It identifies disks with an average IOPS exceeding 1000 over 5-minute intervals, indicating potential I/O bottlenecks.

```kql
Perf
| where ObjectName == "LogicalDisk" and CounterName == "Disk Transfers/sec"
| where InstanceName != "_Total"
| summarize AvgIOPS = avg(CounterValue) by Computer, InstanceName, bin(TimeGenerated, 5m)
| where AvgIOPS > 1000
```

--------------------------------

### KQL Common Patterns

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Illustrates common KQL patterns such as deduplication using `arg_max`, calculating running totals with `row_cumsum`, computing percentages, and performing conditional aggregation.

```kql
// Deduplication
Table | summarize arg_max(TimeGenerated, *) by UniqueId

// Running totals
Table | sort by TimeGenerated asc | extend RunningTotal = row_cumsum(Value)

// Percentage calculation
Table | summarize Count = count() by Category
| extend Percentage = round(100.0 * Count / toscalar(Table | count), 2)

// Conditional aggregation
Table | summarize 
    SuccessCount = countif(Status == "Success"),
    FailureCount = countif(Status == "Failure"),
    SuccessRate = round(100.0 * countif(Status == "Success") / count(), 2)
```

--------------------------------

### Resource Usage by Tag

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Calculates the total data usage in gigabytes, grouped by environment and project tags, for billable resources. It orders the results by total data usage in descending order to highlight the largest consumers.

```kql
Usage
| where IsBillable == true
| extend Tags = parse_json(Tags)
| extend Environment = Tags.Environment, Project = Tags.Project
| summarize TotalGB = sum(Quantity) / 1024 by Environment, Project, DataType
| order by TotalGB desc
```

--------------------------------

### Get Current Timestamp

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

A simple KQL query to display the current timestamp using the `now()` function.

```kql
print now()
```

--------------------------------

### Get Timestamp with Offset

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

This KQL query demonstrates using the `now()` function with a negative offset to retrieve a timestamp from the past.

```kql
print now(-2d)
```

--------------------------------

### KQL: Convert Source to Uppercase and Filter

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

Converts the 'Source' field from the 'Event' table to uppercase and then filters for events where the uppercase source starts with 'MICROSOFT'. This is a case-insensitive filtering for event sources.

```kql
Event
| extend SourceUpper = toupper(Source)
| where SourceUpper startswith "MICROSOFT"
```

--------------------------------

### KQL 'take' and 'limit' Operators - Limit Rows

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Shows how to limit the number of rows returned by a query using 'take', 'limit', and 'sample' operators. Includes 'sample-distinct' for sampling unique values from a specific column.

```kql
TableName
| take 100
| limit 50
| sample 10
| sample-distinct 5 of Category
```

--------------------------------

### KQL 'project' Operator - Select and Rename Columns

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Explains how to select, rename, reorder, and remove columns using the 'project' operator and its variations like 'project-away', 'project-keep', and 'project-rename'. Includes creating new calculated columns.

```kql
TableName
| project Column1, Column2, Column3
| project NewName = OldColumn, Calculated = Column1 + Column2
| project-away UnwantedColumn
| project-keep Column*
| project-rename NewName = OldName
| project-reorder Column3, Column1, Column2
```

--------------------------------

### Get User from Email (Defender KQL)

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

Extracts the user account name from an email address using the EmailEvents table. This query is useful for identifying the recipient's username from their email address.

```kql
EmailEvents
| where Timestamp > ago(7d)
| project RecipientEmailAddress, AccountName = tostring(split(RecipientEmailAddress, "@")[0])
```

--------------------------------

### KQL Schema: DeviceTvmSoftwareInventory

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/defender/mde-table-schemas.md

Describes the schema for DeviceTvmSoftwareInventory, which tracks software installed on devices, version information, and end-of-support status. This schema is essential for managing software assets and identifying potential risks.

```kql
print schema_devicetvmsoftwareinventory
// Columns include software inventory details like DeviceId, SoftwareName, SoftwareVersion, and EndOfSupportStatus.
```

--------------------------------

### KQL substring() - Extract Substring

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/string-functions.md

The substring() function extracts a portion of a string. It takes the source string, a starting index (0-based, negative counts from the end), and an optional length. If length is omitted, it extracts to the end of the string.

```KQL
substring("123456", 1)        // 23456
```

```KQL
substring("123456", 2, 2)     // 34
```

```KQL
substring("ABCD", 0, 2)       // AB
```

```KQL
substring("123456", -2, 2)    // 56
```

```KQL
SecurityEvent
| extend ProcessName = substring(Process, 0, 10)
| project Computer, ProcessName
```

```KQL
Event
| extend EventIDShort = substring(tostring(EventID), 0, 3)
```

--------------------------------

### KQL 'parse' Operator - Extract Structured Data

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Illustrates data extraction using the 'parse' operator, including simple string parsing, regex-based parsing, parsing JSON strings into dynamic objects, and parsing CSV formatted data.

```kql
// Simple parse
TableName
| parse Message with * "Error: " ErrorCode:int " - " ErrorMessage

// Regex parse
TableName
| parse Message with * @"(\d{3})-(\d{4})" AreaCode:string "-" LocalNumber:string *

// Parse JSON
TableName
| extend Parsed = parse_json(JsonColumn)
| extend Name = Parsed.name, Age = Parsed.age

// Parse CSV
TableName
| parse CsvData with Field1:string "," Field2:int "," Field3:datetime
```

--------------------------------

### KQL String Functions

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Demonstrates various string manipulation functions in KQL, including concatenation, substring extraction, case conversion, length checks, trimming, splitting, joining, pattern matching, replacement, and extraction.

```kql
// Concatenation
print strcat("Hello", " ", "World")
print strcat_array(dynamic(["A", "B", "C"]),
"-")

// Substring
print substring("Hello World", 0, 5)
print substring("Hello World", 6)

// Case conversion
print tolower("HELLO")
print toupper("hello")

// Length and emptiness
print strlen("Hello")
print isempty("")
print isnotempty("text")

// Trimming
print trim(" text ")
print trim_start(" text")
print trim_end("text ")

// Splitting and joining
print split("a,b,c", ",")
print strcat_delim(",", "a", "b", "c")

// Pattern matching
print "text" contains "ex"
print "text" startswith "te"
print "text" endswith "xt"
print "text" matches regex @"\w+"

// Replacement
print replace_string("Hello World", "World", "KQL")
print replace_regex("123-456", @"\d+", "X")

// Extraction
print extract(@"(\d+)", 1, "abc123def")
print extract_all(@"(\d+)", "a1b2c3")
```

--------------------------------

### KQL: Concatenate Process and CommandLine

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/context7-kql-catalog.html

Combines the 'Process' and 'CommandLine' fields from the 'SecurityEvent' table into a single 'FullCommand' field using 'strcat'. The result is then projected to show the computer and the combined command.

```kql
SecurityEvent
| extend FullCommand = strcat(Process, " ", CommandLine)
| project Computer, FullCommand
```

--------------------------------

### KQL Type Conversion Functions

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/data-types.md

Demonstrates how to convert strings and other data types to various KQL types like long, double, datetime, timespan, bool, and guid. It also shows how to check the type of a value using gettype().

```kql
print tolong("123")
print todouble("3.14")
print todatetime("2023-01-01")
print totimespan("1.02:03:04.5")
print tobool("true")
print toguid("74be27de-1e4e-49d9-b579-fe0b331d3642")

print todynamic('{"name":"John","age":30}')
print todynamic('[1,2,3,4,5]')

print gettype(123)           // int
print gettype(123.456)       // real
print gettype("text")        // string
print gettype(now())         // datetime
```

--------------------------------

### KQL 'join' Operator - Combine Tables

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Demonstrates different types of table joins in KQL, including inner, leftouter, time window joins, and multi-key joins. Specifies the join kind and the keys for combining tables.

```kql
// Inner join
Table1
| join kind=inner (Table2) on Key

// Left outer join
Table1
| join kind=leftouter (Table2) on Key

// Time window join
Table1
| join kind=inner (Table2) on UserId, $left.Time == $right.Time

// Multi-key join
Table1
| join kind=inner (Table2) on Key1, Key2
```

--------------------------------

### Track User Activity

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Summarizes user activity by counting unique users, total actions, and collecting a set of all action types per hour. It also calculates the average number of actions per user.

```kql
ActivityLogs
| summarize 
    UniqueUsers = dcount(UserId),
    TotalActions = count(),
    Actions = make_set(ActionType)
    by bin(TimeGenerated, 1h)
| extend ActionsPerUser = TotalActions * 1.0 / UniqueUsers
```

--------------------------------

### Analyze CPU Usage

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Analyzes CPU utilization by calculating average, maximum, and 95th percentile processor time over 5-minute intervals. It flags computers where the 95th percentile CPU usage exceeds 80%.

```kql
Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize 
    AvgCPU = avg(CounterValue),
    MaxCPU = max(CounterValue),
    P95CPU = percentile(CounterValue, 95)
    by Computer, bin(TimeGenerated, 5m)
| where P95CPU > 80
```

--------------------------------

### KQL Geospatial Analysis

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Provides examples of KQL queries for geospatial analysis, including converting points to H3 cells, calculating distances between points, checking if points are within a polygon, and converting points to S2 cells. This is valuable for location-based analytics.

```kql
TableName
| extend Point = geo_point_to_h3cell(Longitude, Latitude, 8)
| extend Distance = geo_distance_2points(Lon1, Lat1, Lon2, Lat2)
| where geo_point_in_polygon(Longitude, Latitude, PolygonGeoJson)
| extend S2Cell = geo_point_to_s2cell(Longitude, Latitude, 12)
```

--------------------------------

### KQL Aggregation Functions

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Lists and demonstrates various aggregation functions in KQL, categorized into count functions, statistical functions, percentile functions, collection functions, and advanced functions like arg_min, arg_max, hll, and tdigest.

```kql
TableName
| summarize 
    // Count functions
    TotalCount = count(),
    ConditionalCount = countif(Condition),
    DistinctCount = dcount(Column),
    DistinctCountIf = dcountif(Column, Condition),
    
    // Statistical functions
    Average = avg(Value),
    AverageIf = avgif(Value, Condition),
    Sum = sum(Value),
    SumIf = sumif(Value, Condition),
    Min = min(Value),
    Max = max(Value),
    StdDev = stdev(Value),
    Variance = variance(Value),
    
    // Percentiles
    Median = percentile(Value, 50),
    P90 = percentile(Value, 90),
    P95 = percentile(Value, 95),
    P99 = percentile(Value, 99),
    
    // Collection functions
    List = make_list(Value),
    Set = make_set(Value),
    Bag = make_bag(pack(Key, Value)),
    
    // Advanced
    ArgMin = arg_min(Value, *),
    ArgMax = arg_max(Value, *),
    HLL = hll(Column),
    TDigest = tdigest(Value)
```

--------------------------------

### KQL Machine Learning Patterns

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

Showcases KQL patterns for applying machine learning techniques directly within queries, such as automatic clustering, basket analysis for association rules, and outlier detection using series functions. These enable ML-driven insights without external tools.

```kql
// Clustering
TableName
| evaluate autocluster()

// Basket analysis
TableName
| evaluate basket()

// Outlier detection
TableName
| make-series Value = avg(Metric) on Timestamp step 1h
| extend outliers = series_outliers(Value, 3)
```

--------------------------------

### KQL Lateral Movement Detection

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/kql-complete-reference.md

This KQL query identifies potential lateral movement by tracking user accounts accessing multiple computers within an hour. It looks for successful logons (4624, 4648) and privileged assignments (4672), flagging accounts with access to more than 3 unique computers.

```kql
SecurityEvent
| where EventID in (4624, 4648, 4672)
| where AccountType == "User"
| summarize 
    UniqueComputers = dcount(Computer),
    ComputerList = make_set(Computer)
    by Account, bin(TimeGenerated, 1h)
| where UniqueComputers > 3
```

--------------------------------

### KQL count() Aggregation Function

Source: https://github.com/vitorallo/kql-sentinel-mdr-reference/blob/main/kql/aggregation-functions.md

The count() aggregation function in KQL counts the number of records within each summarization group. It is commonly used with the summarize operator to get counts based on specific criteria.

```KQL
StormEvents
| summarize Count=count() by State
```

```KQL
SecurityEvent
| where TimeGenerated > ago(1d)
| summarize EventCount=count() by EventID
```