# Kusto Query Language (KQL)

This guide provides Mindflow users with a framework for using Kusto Query Language (KQL) within API endpoints where they can run KQL queries.

For instance, the MsGraph Security API endpoint `/security/microsoft.graph.security.runHuntingQuery` allow users to trigger searches across the tenant to detect potential malicious behavior and/or IOCs.

The following guide focuses on Efficiency (reducing cost/latency) and Hardening (minimizing false positives).

## 1. Core Operators and Performance Hardening

To prevent query timeouts and ensure accurate results, follow the **"Filter Left"** principle: keep high-cardinality filters at the top.

| **Operator**       | **Usage**                | **Hardening Tip**                                                                                      |
| ------------------ | ------------------------ | ------------------------------------------------------------------------------------------------------ |
| **`where`**        | Filters the dataset.     | Use `has` or `has_any` instead of `contains`. `has` looks for full tokens and is significantly faster. |
| **`summarize`**    | Aggregates data.         | Use `dcount()` to find unique occurrences (e.g., unique IPs) rather than listing them all.             |
| **`arg_max()`**    | Finds the latest record. | Use `summarize arg_max(TimeGenerated, *) by Entity` to remove duplicate noise from logs.               |
| **`parse_json()`** | Extracts nested data.    | Always extend into a specific column before filtering to avoid repeated overhead.                      |
| **`search`**       | Scans across tables.     | **Avoid in production.** It is extremely resource-heavy. Explicitly name your tables instead.          |

## 2. Email Hunting (EmailEvents ans EmailAttachmentInfo)

### A. Identifying Mass Phishing Campaigns

This query identifies a single Sender IP delivering the same subject line to multiple recipients, a common sign of a coordinated campaign.

```jsx
EmailEvents
| where TimeGenerated > ago(24h)
| where DeliveryAction !in ("Blocked", "Dropped")
| summarize RecipientCount = dcount(RecipientEmailAddress), SubjectList = make_set(Subject)
    by SenderIPv4, SenderFromAddress
| where RecipientCount > 5
| sort by RecipientCount desc
```

Where you are already in possession of IOCs, you can also use queries based on hardcoded parameters, like senderFromAddress, SenderIPv4 or Subject, and use the OR operator to match either IOCs.

```jsx
EmailEvents
| where Timestamp > ago(7d)
| where SenderFromAddress =~ "sender_email_address"
or Subject has "malicious_email_subject"
| summarize Recipients = make_set(RecipientEmailAddress) by NetworkMessageId, Subject, SenderFromAddress
| project NetworkMessageId, Subject, SenderFromAddress, Recipients
```

## B. Dangerous Attachment Hunting

This query will correlate email metadata with attachment info to find high-risk file types arriving from external sources.

```jsx
EmailEvents
| where TimeGenerated > ago(12h)
| where SenderFromDomain !has "yourdomain.com"
| join kind=inner (EmailAttachmentInfo) on NetworkMessageId
| where FileType in ("exe", "vbs", "ps1", "js", "htm", "html")
| project TimeGenerated, SenderFromAddress, RecipientEmailAddress, FileName, FileType, SHA256
```

## 3. Hardened Security Queries

### A. Lateral Movement (Identity Correlation)

Detects when a user signs in and immediately performs sensitive actions, such as adding a credential to an application.

```jsx
let timeframe = 1h;
SigninLogs
| where TimeGenerated > ago(timeframe)
| where ResultType == 0
| join kind=inner (
    AuditLogs
    | where OperationName has_any ("Add service principal", "Add member to role")
) on $left.UserPrincipalName == $right.InitiatedBy.user.userPrincipalName
| project TimeGenerated, UserPrincipalName, OperationName, TargetResources[0].displayName
```

### B. Process Masquerading (Persistence)

Hardened query to find processes running from suspicious temporary directories—a common tactic for malware.

```jsx
DeviceProcessEvents
| where TimeGenerated > ago(1d)
| where FolderPath has_any ("\\Users\\Public\\", "\\AppData\\Local\\Temp\\", "\\Windows\\Temp\\")
| where FileName !in ("expected_updater.exe", "known_tool.exe") // Whitelist known-good binaries
| summarize FirstSeen = min(TimeGenerated), LastSeen = max(TimeGenerated) by DeviceName, FileName, FolderPath, ProcessCommandLine
```

## 4. Automation & API Best Practices

As best practice always try to follow the following when calling queries:

- **Limit Output:** Always end your query with `| take 100` or `| limit 100` to prevent the API response from being truncated or timing out.
- **Timestamp Alignment:** Use `bin(TimeGenerated, 1h)` to group events; this makes trend analysis and deduplication much easier for downstream logic apps.
- **Variable Injection:** Use the `let` statement to define thresholds at the top of the script. This makes it easier to tune the query "hardness" without digging through the logic.
- Finally, when you look to trigger queries, mind that you may have to update your supporting Azure Entra Application with additional permissions. For instance, to trigger a KQL on `EmailEvents`, your service principal should have the `Email.Read.All` or equivalent MsGraph permissions to see metadata, as some fields may be masked by default privacy settings.