# Advanced Hunting & KQL Reference Advanced Hunting & KQL Reference

Referenz für KQL-Grundlagen, wichtige Tabellen, Identitäts-, E-Mail- und Datenabfluss-Hunting in Microsoft 365 Defender und Sentinel. Reference for KQL basics, key tables, and identity, email, and data exfiltration hunting in Microsoft 365 Defender and Sentinel.

🧠 KQL Grundlagen 🧠 KQL basics

Syntax, Operatoren und Query-Muster. Syntax, operators, and query patterns.

🗂️ Wichtige Tabellen 🗂️ Important tables

Schlüsselspalten, Filter und Beispielqueries. Key columns, filters, and example queries.

👤 Identity Hunting 👤 Identity hunting

Password spray, MFA fatigue, consent phishing und mehr. Password spray, MFA fatigue, consent phishing, and more.

📧 Email Hunting 📧 Email hunting

Phishing, BEC, Attachments und Forwarding. Phishing, BEC, attachments, and forwarding.

📤 Data Exfiltration 📤 Data exfiltration

Massendownloads, USB, Cloud Uploads und E-Mail. Mass downloads, USB, cloud uploads, and email.

⚡ Optimierung ⚡ Optimization

Schneller und genauer mit KQL arbeiten. Work faster and more accurately with KQL.

## KQL Basics KQL basics

Konstrukt Construct | Beschreibung Description | Beispiel Example
--- | --- | ---
where where | Filtert Zeilen früh nach Zeit, Benutzer, Gerät oder Aktion Filters rows early by time, user, device, or action | | where Timestamp > ago(24h) | where Timestamp > ago(24h)
project project | Wählt nur benötigte Spalten aus Keeps only needed columns | | project Timestamp, AccountUpn, IPAddress | project Timestamp, AccountUpn, IPAddress
summarize summarize | Aggregiert Daten mit count, dcount, sum oder make_set Aggregates data with count, dcount, sum, or make_set | | summarize count() by AccountUpn | summarize count() by AccountUpn
extend extend | Berechnet neue Spalten oder normalisiert Werte Calculates new columns or normalizes values | | extend Country=tostring(LocationDetails.countryOrRegion) | extend Country=tostring(LocationDetails.countryOrRegion)
join join | Korreliert Daten aus mehreren Tabellen Correlates data across tables | | join kind=inner (...) on AccountUpn | join kind=inner (...) on AccountUpn
let let | Speichert Zwischenergebnisse oder Parameter Stores intermediate results or parameters | let lookback = 7d; let lookback = 7d;
render render | Visualisiert Ergebnisse in Workbooks oder Notebooks Visualizes results in workbooks or notebooks | | render timechart | render timechart

Hilfsfunktion Helper function | Nutzen Purpose | Hinweis Note
--- | --- | ---
ago() ago() | Relative Zeitfilter für Performance und Relevanz Relative time filter for performance and relevance | Immer möglichst früh anwenden Apply as early as possible
bin() bin() | Zeit oder numerische Werte gruppieren Bucket time or numeric values | Nützlich für Trend- und Burst-Erkennung Useful for trend and burst detection
has has | Schnellerer String-Match auf tokenisierte Inhalte Faster string match on tokenized content | Meist besser als contains Usually better than contains
mv-expand mv-expand | Dynamische Arrays in Zeilen auflösen Expand dynamic arrays into rows | Nur bei Bedarf wegen Datenvolumen Use only when needed due to data volume
make_set() make_set() | Kompakte Darstellung mehrerer Werte je Entity Compact representation of multiple values per entity | Mit Limit verwenden Use with a limit

KQL KQL

    let lookback = 7d;
    AADSignInEventsBeta
    | where Timestamp > ago(lookback)
    | where ResultType != 0
    | extend Country = tostring(LocationDetails.countryOrRegion)
    | summarize Failures=count(), IPs=dcount(IPAddress) by AccountUpn, Country
    | order by Failures desc

## Important Tables Important tables

### IdentityLogonEvents IdentityLogonEvents

Anmeldeereignisse aus AD- und Identitätsquellen. Logon events from AD and identity sources.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
Timestamp Timestamp | Zeitpunkt Timestamp | Filter, Project oder Join Filter, project, or join
AccountName AccountName | Konto Account | Filter, Project oder Join Filter, project, or join
FailureReason FailureReason | Fehlergrund Failure reason | Filter, Project oder Join Filter, project, or join

- Letzte 24 Stunden Last 24 hours
- Fehlercodes und Domänencontroller Failure codes and domain controllers

KQL KQL

    IdentityLogonEvents
    | where Timestamp > ago(24h)
    | summarize Events=count() by AccountName, LogonType, ActionType
    | order by Events desc

### IdentityQueryEvents IdentityQueryEvents

Directory- und LDAP-Abfragen für Reconnaissance. Directory and LDAP queries for reconnaissance.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
QueryType QueryType | Abfragetyp Query type | Filter, Project oder Join Filter, project, or join
InitiatingProcessAccountName InitiatingProcessAccountName | Akteur Actor | Filter, Project oder Join Filter, project, or join
DestinationDeviceName DestinationDeviceName | Zielsystem Target system | Filter, Project oder Join Filter, project, or join

- Hohe Eventzahlen High event counts
- Sensible OUs oder Admin-Gruppen Sensitive OUs or admin groups

KQL KQL

    IdentityQueryEvents
    | where Timestamp > ago(7d)
    | summarize Queries=count() by InitiatingProcessAccountName, QueryType
    | order by Queries desc

### IdentityDirectoryEvents IdentityDirectoryEvents

Verzeichnisänderungen wie Rollen- oder Gruppenmitgliedschaften. Directory changes such as role or group memberships.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
ActionType ActionType | Aktion Action | Filter, Project oder Join Filter, project, or join
InitiatingAccountUpn InitiatingAccountUpn | Auslöser Initiator | Filter, Project oder Join Filter, project, or join
TargetAccountUpn TargetAccountUpn | Zielobjekt Target object | Filter, Project oder Join Filter, project, or join

- Rollenänderungen Role changes
- Gruppenmitgliedschaften Group memberships

KQL KQL

    IdentityDirectoryEvents
    | where Timestamp > ago(7d)
    | where ActionType has 'role'
    | project Timestamp, ActionType, InitiatingAccountUpn, TargetAccountUpn, TargetRoleName

### CloudAppEvents CloudAppEvents

Zentrale Aktivitätstabelle für Microsoft 365 und Cloud Apps. Primary activity table for Microsoft 365 and cloud apps.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
Application Application | Workload Workload | Filter, Project oder Join Filter, project, or join
ActivityType ActivityType | Aktivität Activity | Filter, Project oder Join Filter, project, or join
AccountDisplayName AccountDisplayName | Akteur Actor | Filter, Project oder Join Filter, project, or join

- Application exakt filtern Filter by exact Application
- ActivityType einschränken Restrict ActivityType

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(24h)
    | summarize Events=count() by Application, ActivityType
    | order by Events desc

### EmailEvents EmailEvents

E-Mail-Metadaten für Routing, Zustellung und Kampagnenanalyse. Email metadata for routing, delivery, and campaign analysis.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
SenderFromAddress SenderFromAddress | Absender Sender | Filter, Project oder Join Filter, project, or join
RecipientEmailAddress RecipientEmailAddress | Empfänger Recipient | Filter, Project oder Join Filter, project, or join
Subject Subject | Betreff Subject | Filter, Project oder Join Filter, project, or join

- Externe Zustellung External delivery
- Absenderdomänen Sender domains

KQL KQL

    EmailEvents
    | where Timestamp > ago(24h)
    | summarize Messages=count() by SenderFromAddress, DeliveryLocation
    | order by Messages desc

### EmailAttachmentInfo EmailAttachmentInfo

Details zu Anhängen wie Typ, Größe und Hash. Attachment details such as type, size, and hash.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
FileName FileName | Datei File | Filter, Project oder Join Filter, project, or join
FileType FileType | Typ Type | Filter, Project oder Join Filter, project, or join
SHA256 SHA256 | Hash Hash | Filter, Project oder Join Filter, project, or join

- Riskante Erweiterungen Risky extensions
- Größe und Sender Size and sender

KQL KQL

    EmailAttachmentInfo
    | where Timestamp > ago(7d)
    | summarize Count=count() by FileType
    | order by Count desc

### EmailUrlInfo EmailUrlInfo

URLs aus E-Mails mit Host- und Domänenkontext. URLs extracted from email with host and domain context.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
Url Url | URL URL | Filter, Project oder Join Filter, project, or join
Domain Domain | Domäne Domain | Filter, Project oder Join Filter, project, or join
NetworkMessageId NetworkMessageId | Message-ID Message ID | Filter, Project oder Join Filter, project, or join

- Verdächtige TLDs Suspicious TLDs
- Neue Hosts New hosts

KQL KQL

    EmailUrlInfo
    | where Timestamp > ago(7d)
    | summarize UrlCount=count() by Domain
    | order by UrlCount desc

### UrlClickEvents UrlClickEvents

Safe-Links-Klicks und Benutzerreaktionen. Safe Links clicks and user reactions.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
ActionType ActionType | Ergebnis Outcome | Filter, Project oder Join Filter, project, or join
Url Url | Geklickte URL Clicked URL | Filter, Project oder Join Filter, project, or join
AccountUpn AccountUpn | Benutzer User | Filter, Project oder Join Filter, project, or join

- Geblockte Klicks Blocked clicks
- Mehrfachklicks pro Benutzer Multiple clicks per user

KQL KQL

    UrlClickEvents
    | where Timestamp > ago(7d)
    | summarize Clicks=count() by AccountUpn, Url, ActionType
    | order by Clicks desc

### DeviceEvents DeviceEvents

Breite Geräteaktivitäten als generische Ereignistabelle. Broad device activity as a generic event table.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
ActionType ActionType | Aktion Action | Filter, Project oder Join Filter, project, or join
DeviceName DeviceName | Gerät Device | Filter, Project oder Join Filter, project, or join
InitiatingProcessFileName InitiatingProcessFileName | Prozess Process | Filter, Project oder Join Filter, project, or join

- Spezifischer ActionType Specific ActionType
- Bestimmte Geräteklassen Specific device classes

KQL KQL

    DeviceEvents
    | where Timestamp > ago(24h)
    | summarize Events=count() by ActionType
    | order by Events desc

### DeviceLogonEvents DeviceLogonEvents

Interaktive und nicht interaktive Logons auf Endpunkten. Interactive and non-interactive endpoint logons.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
AccountName AccountName | Konto Account | Filter, Project oder Join Filter, project, or join
DeviceName DeviceName | Gerät Device | Filter, Project oder Join Filter, project, or join
LogonType LogonType | Logontyp Logon type | Filter, Project oder Join Filter, project, or join

- RemoteInteractive RemoteInteractive
- Admin-Konten Admin accounts

KQL KQL

    DeviceLogonEvents
    | where Timestamp > ago(24h)
    | summarize Logons=count() by AccountName, LogonType
    | order by Logons desc

### DeviceFileEvents DeviceFileEvents

Dateioperationen wie Erstellen, Kopieren, Löschen. File operations such as create, copy, and delete.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
FileName FileName | Datei File | Filter, Project oder Join Filter, project, or join
FolderPath FolderPath | Pfad Path | Filter, Project oder Join Filter, project, or join
ActionType ActionType | Aktion Action | Filter, Project oder Join Filter, project, or join

- Löschen/Kopieren Delete/copy
- Sensible Verzeichnisse Sensitive directories

KQL KQL

    DeviceFileEvents
    | where Timestamp > ago(24h)
    | summarize Events=count() by ActionType, InitiatingProcessAccountName
    | order by Events desc

### DeviceProcessEvents DeviceProcessEvents

Prozessstarts für Script-, LOLBin- und Malware-Analysen. Process creation for script, LOLBin, and malware analysis.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
FileName FileName | Prozessname Process name | Filter, Project oder Join Filter, project, or join
ProcessCommandLine ProcessCommandLine | Kommandozeile Command line | Filter, Project oder Join Filter, project, or join
SHA256 SHA256 | Hash Hash | Filter, Project oder Join Filter, project, or join

- PowerShell/cmd PowerShell/cmd
- EncodedCommand EncodedCommand

KQL KQL

    DeviceProcessEvents
    | where Timestamp > ago(24h)
    | where FileName in~ ('powershell.exe','cmd.exe','rundll32.exe')
    | project Timestamp, DeviceName, FileName, ProcessCommandLine

### DeviceNetworkEvents DeviceNetworkEvents

Netzwerkverbindungen mit Ziel-URL, IP und Port. Network connections with destination URL, IP, and port.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
RemoteUrl RemoteUrl | Ziel-URL Remote URL | Filter, Project oder Join Filter, project, or join
RemoteIP RemoteIP | Ziel-IP Remote IP | Filter, Project oder Join Filter, project, or join
RemotePort RemotePort | Zielport Remote port | Filter, Project oder Join Filter, project, or join

- Cloud-Speicher-Domänen Cloud storage domains
- Seltene Ziele Rare destinations

KQL KQL

    DeviceNetworkEvents
    | where Timestamp > ago(24h)
    | summarize Connections=count() by RemoteUrl, InitiatingProcessFileName
    | order by Connections desc

### DeviceRegistryEvents DeviceRegistryEvents

Registry-Änderungen für Persistenz und Policy-Manipulation. Registry changes for persistence and policy manipulation.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
RegistryKey RegistryKey | Schlüssel Key | Filter, Project oder Join Filter, project, or join
RegistryValueName RegistryValueName | Wertname Value name | Filter, Project oder Join Filter, project, or join
ActionType ActionType | Aktion Action | Filter, Project oder Join Filter, project, or join

- Run Keys Run keys
- Sicherheitsrelevante Einstellungen Security-sensitive settings

KQL KQL

    DeviceRegistryEvents
    | where Timestamp > ago(7d)
    | summarize Events=count() by RegistryKey, ActionType
    | order by Events desc

### AlertInfo AlertInfo

Alert-Metadaten wie Severity, Kategorie und Quelle. Alert metadata such as severity, category, and source.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
AlertId AlertId | Alert-ID Alert ID | Filter, Project oder Join Filter, project, or join
Severity Severity | Schweregrad Severity | Filter, Project oder Join Filter, project, or join
ServiceSource ServiceSource | Quelle Source | Filter, Project oder Join Filter, project, or join

- High Severity High severity
- Bestimmtes Produkt Specific product

KQL KQL

    AlertInfo
    | where Timestamp > ago(7d)
    | summarize Alerts=count() by Severity, ServiceSource
    | order by Alerts desc

### AlertEvidence AlertEvidence

Beweisdaten zu Alerts mit Konten, Geräten, Dateien und URLs. Alert evidence with accounts, devices, files, and URLs.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
AlertId AlertId | Referenz Reference | Filter, Project oder Join Filter, project, or join
EntityType EntityType | Entitätstyp Entity type | Filter, Project oder Join Filter, project, or join
EntityName EntityName | Entitätsname Entity name | Filter, Project oder Join Filter, project, or join

- EntityType filtern Filter by EntityType
- Mit AlertInfo joinen Join with AlertInfo

KQL KQL

    AlertEvidence
    | where Timestamp > ago(7d)
    | summarize Entities=count() by EntityType
    | order by Entities desc

### AADSignInEventsBeta AADSignInEventsBeta

Aktuelle Entra-Anmeldeereignisse mit CA- und Risikokontext. Current Entra sign-ins with CA and risk context.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
AccountUpn AccountUpn | Benutzer User | Filter, Project oder Join Filter, project, or join
IPAddress IPAddress | Quell-IP Source IP | Filter, Project oder Join Filter, project, or join
ConditionalAccessStatus ConditionalAccessStatus | CA-Ergebnis CA result | Filter, Project oder Join Filter, project, or join

- ResultType == 0 ResultType == 0
- RiskLevelAggregated RiskLevelAggregated

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(24h)
    | summarize SignIns=count() by AccountUpn, ResultType
    | order by SignIns desc

### AADSpnSignInEventsBeta AADSpnSignInEventsBeta

Anmeldeereignisse für Service Principals und Workload Identities. Sign-in events for service principals and workload identities.

Spalte Column | Bedeutung Meaning | Typischer Einsatz Typical use
--- | --- | ---
ServicePrincipalName ServicePrincipalName | Dienstprinzipal Service principal | Filter, Project oder Join Filter, project, or join
AppId AppId | App-ID App ID | Filter, Project oder Join Filter, project, or join
ResultType ResultType | Ergebnis Result | Filter, Project oder Join Filter, project, or join

- Fehlerhafte Anmeldungen Failed sign-ins
- Neue Geografien New geographies

KQL KQL

    AADSpnSignInEventsBeta
    | where Timestamp > ago(24h)
    | summarize SignIns=count() by ServicePrincipalName, ResultType
    | order by SignIns desc

## Identity Threat Hunting Identity threat hunting

### Password spray nach IP Password spray by IP

Viele fehlgeschlagene Ziele von einer Quelle. Many failed targets from one source.

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(6h)
    | where ResultType != 0
    | summarize Failed=count(), Targets=dcount(AccountUpn) by IPAddress
    | where Failed > 25 and Targets > 10

### Credential Stuffing mit Erfolg Credential stuffing success

Erfolg kurz nach vielen Fehlern von derselben IP. Success shortly after many failures from the same IP.

KQL KQL

    let failures = AADSignInEventsBeta | where Timestamp > ago(12h) | where ResultType != 0 | summarize Failed=count() by IPAddress, bin(Timestamp, 15m);
    AADSignInEventsBeta
    | where Timestamp > ago(12h)
    | where ResultType == 0
    | summarize Success=count() by IPAddress, bin(Timestamp, 15m)
    | join kind=inner failures on IPAddress, Timestamp
    | where Failed > 20 and Success > 0

### MFA-Fatigue MFA fatigue

Mehrere MFA-Aufforderungen und Fehlversuche in kurzer Zeit. Multiple MFA prompts and failures in a short period.

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(24h)
    | where AuthenticationRequirement == 'multiFactorAuthentication'
    | summarize Attempts=count(), Failed=countif(ResultType != 0) by AccountUpn, bin(Timestamp, 30m)
    | where Attempts >= 8 and Failed >= 5

### Token Replay Token replay

Ähnliche Sessions mit schnellem Wechsel von IP und User-Agent. Similar sessions with rapid IP and user-agent changes.

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(24h)
    | summarize IPs=make_set(IPAddress, 10), Agents=make_set(UserAgent, 10) by AccountUpn
    | where array_length(IPs) > 3 and array_length(Agents) > 2

### Consent Phishing Consent phishing

Riskante App-Einwilligungen mit weitreichenden Berechtigungen. Risky app consents with broad permissions.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType has_any ('Consent to application','Add delegated permission grant')
    | where RawEventData has_any ('Mail.Read','Files.ReadWrite.All','offline_access')

### Admin Privilege Escalation Admin privilege escalation

Neue oder aktivierte privilegierte Rollen. New or activated privileged roles.

KQL KQL

    IdentityDirectoryEvents
    | where Timestamp > ago(7d)
    | where ActionType has_any ('Add member to role','Activate eligible assignment')
    | project Timestamp, InitiatingAccountUpn, TargetAccountUpn, TargetRoleName

### Dormant Account Usage Dormant account usage

Ruhende Konten mit neuer Aktivität. Dormant accounts with new activity.

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(30d)
    | summarize LastSeen=max(Timestamp) by AccountUpn
    | where LastSeen > ago(1d)

### Guest User Abuse Guest user abuse

Gastkonten mit ungewöhnlicher Aktivität. Guest accounts with unusual activity.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where AccountType =~ 'Guest'
    | summarize Activities=count(), Apps=make_set(Application, 10) by AccountDisplayName
    | where Activities > 50

### Impossible Travel Impossible travel

Benutzer melden sich aus mehreren Ländern in kurzer Zeit an. Users sign in from multiple countries in a short period.

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(24h)
    | summarize FirstSeen=min(Timestamp), LastSeen=max(Timestamp), Countries=make_set(tostring(LocationDetails.countryOrRegion), 5) by AccountUpn
    | where array_length(Countries) > 1 and datetime_diff('hour', LastSeen, FirstSeen) < 6

### Service Principal New Country Service principal new country

Service Principals mit Sign-ins aus mehreren Geografien. Service principals with sign-ins from multiple geographies.

KQL KQL

    AADSpnSignInEventsBeta
    | where Timestamp > ago(14d)
    | summarize Countries=make_set(tostring(LocationDetails.countryOrRegion), 10) by ServicePrincipalName
    | where array_length(Countries) > 1

### Legacy Auth Attempts Legacy auth attempts

Legacy-Protokolle nach Modernisierung. Legacy protocols after modernization.

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(14d)
    | where ClientAppUsed has_any ('IMAP','POP','SMTP','ActiveSync')
    | summarize Attempts=count() by AccountUpn, ClientAppUsed

### Disabled Account Attempts Disabled account attempts

Versuche gegen deaktivierte oder gesperrte Konten. Attempts against disabled or blocked accounts.

KQL KQL

    AADSignInEventsBeta
    | where Timestamp > ago(7d)
    | where ResultDescription has_any ('disabled','blocked')
    | summarize Attempts=count() by AccountUpn, IPAddress

### Risky Sign-in to Mail Activity Risky sign-in to mail activity

Riskanter Sign-in gefolgt von Mailaktivität. Risky sign-in followed by mail activity.

KQL KQL

    let risky = AADSignInEventsBeta | where Timestamp > ago(24h) | where RiskLevelAggregated has_any ('high','medium') | project AccountUpn, RiskTime=Timestamp;
    CloudAppEvents
    | where Timestamp > ago(24h)
    | where ActivityType has_any ('MailItemsAccessed','MessageBind')
    | join kind=inner risky on $left.AccountDisplayName == $right.AccountUpn
    | where Timestamp between (RiskTime .. RiskTime + 2h)

### Rollenaktivierung außerhalb der Zeiten Role activation outside hours

Privilegierte Aktivität nachts oder am Wochenende. Privileged activity at night or on weekends.

KQL KQL

    IdentityDirectoryEvents
    | where Timestamp > ago(7d)
    | where ActionType has_any ('Activate eligible assignment','Add member to role')
    | extend Hour=datetime_part('hour', Timestamp)
    | where Hour < 6 or Hour > 20

### Mehrere User stimmen derselben App zu Many users consent to same app

App-Einwilligungsmuster mit Kampagnencharakter. App-consent patterns with campaign characteristics.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType has_any ('Consent to application','Add delegated permission grant')
    | summarize Users=dcount(AccountDisplayName) by ObjectName, bin(Timestamp, 1d)
    | where Users > 5

## Email Threat Hunting Email threat hunting

### Phishing-Kampagnen nach Absender Phishing campaigns by sender

Absender mit vielen Empfängern und gleichen Betreffs. Senders targeting many recipients with similar subjects.

KQL KQL

    EmailEvents
    | where Timestamp > ago(24h)
    | summarize Recipients=dcount(RecipientEmailAddress), Messages=count() by SenderFromAddress, Subject
    | where Recipients > 10

### Bösartige Anhänge nach Typ Malicious attachments by type

Riskante Dateierweiterungen in Nachrichten. Risky file extensions in email.

KQL KQL

    EmailAttachmentInfo
    | where Timestamp > ago(7d)
    | where FileType in~ ('iso','img','js','vbs','exe','lnk','html')
    | summarize Count=count() by FileType, SenderFromAddress

### BEC Display-Name Spoofing BEC display-name spoofing

Führungskräfte-Namen mit abweichender Domäne. Executive display names with mismatched domains.

KQL KQL

    EmailEvents
    | where Timestamp > ago(7d)
    | where SenderDisplayName has_any ('CEO','CFO','Finance','Payroll')
    | where SenderFromDomain !endswith 'contoso.com'

### Internal Phishing Internal phishing

Interne Marke oder Firmenname, aber externer Absender. Internal brand or company name with external sender.

KQL KQL

    EmailEvents
    | where Timestamp > ago(7d)
    | where SenderDisplayName endswith 'Contoso'
    | where SenderFromDomain !endswith 'contoso.com'

### Auto-Forwarding nach extern Auto-forwarding to external

Mailbox-Regeln mit Weiterleitung an externe Ziele. Mailbox rules forwarding to external targets.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType has_any ('New-InboxRule','Set-InboxRule','Set-Mailbox')
    | where RawEventData has '@' and RawEventData !has 'contoso.com'

### Neue Inbox Rules New inbox rules

Kürzlich angelegte Regeln mit Delete, Move oder Redirect. Recently created rules with delete, move, or redirect actions.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType in~ ('New-InboxRule','Set-InboxRule')
    | where RawEventData has_any ('RedirectTo','DeleteMessage','MoveToFolder')

### Mail-Purge durch Benutzer Mass purge by user

Viele Lösch- oder HardDelete-Aktionen. Many delete or hard delete actions.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(24h)
    | where ActivityType has_any ('MoveToDeletedItems','SoftDelete','HardDelete')
    | summarize Events=count() by AccountDisplayName, bin(Timestamp, 1h)
    | where Events > 500

### Safe-Links-Klicks Safe Links clicks

Viele Klicks auf verdächtige Links. Multiple clicks on suspicious URLs.

KQL KQL

    UrlClickEvents
    | where Timestamp > ago(7d)
    | summarize Clicks=count(), Users=dcount(AccountUpn) by Url, ActionType
    | order by Clicks desc

### Transportregel-Änderungen Transport rule changes

Neue oder geänderte Regeln im Mailflow. New or changed mail-flow rules.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType has_any ('New-TransportRule','Set-TransportRule','Remove-TransportRule')

### Mailbox-Delegationen Mailbox delegate changes

Neue FullAccess-, SendAs- oder ähnliche Rechte. New FullAccess, SendAs, or similar rights.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType has_any ('Add-MailboxPermission','Add-RecipientPermission','Set-Mailbox')

## Data Exfiltration Hunting Data exfiltration hunting

### Massen-Downloads aus SharePoint Mass SharePoint downloads

Hohe Datei-Download-Zahlen aus SharePoint oder OneDrive. High file download volumes from SharePoint or OneDrive.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(24h)
    | where Application has_any ('SharePoint','OneDrive')
    | where ActivityType has_any ('FileDownloaded','FileSyncDownloadedFull')
    | summarize Downloads=count(), UniqueFiles=dcount(ObjectName) by AccountDisplayName, bin(Timestamp, 1h)
    | where Downloads > 200

### Spitzen bei externer Freigabe External sharing spikes

Viele Einladungen oder Links in kurzer Zeit. Many invitations or links in a short period.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType has_any ('AnonymousLinkCreated','SecureLinkCreated','SharingInvitationCreated')
    | summarize Events=count() by AccountDisplayName, bin(Timestamp, 1d)
    | where Events > 20

### USB-Datenübertragung USB data transfer

Dateikopien auf Wechseldatenträger. File copies to removable media.

KQL KQL

    DeviceFileEvents
    | where Timestamp > ago(7d)
    | where ActionType in~ ('FileCreated','FileCopied')
    | where AdditionalFields has 'RemovableMedia'
    | summarize Files=count(), TotalSize=sum(FileSize) by DeviceName, InitiatingProcessAccountName

### Cloud-Upload-Anomalien Cloud upload anomalies

Große Uploads zu Dropbox, Box oder Google Drive. Large uploads to Dropbox, Box, or Google Drive.

KQL KQL

    DeviceNetworkEvents
    | where Timestamp > ago(24h)
    | where RemoteUrl has_any ('dropbox','box.com','mega.nz','drive.google.com')
    | summarize Connections=count() by DeviceName, InitiatingProcessAccountName, RemoteUrl

### E-Mail-basierter Datenabfluss Email-based exfiltration

Große oder wiederholte ausgehende Anhänge. Large or repeated outbound attachments.

KQL KQL

    EmailAttachmentInfo
    | where Timestamp > ago(7d)
    | summarize Attachments=count(), TotalSize=sum(FileSize) by SenderFromAddress, RecipientEmailAddress
    | where TotalSize > 50000000

### Teams-Download-Spitzen Teams download spikes

Viele Datei-Downloads in Teams-gebundenen Arbeitsbereichen. Large volumes of file downloads in Teams-backed workspaces.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(24h)
    | where Application == 'Microsoft Teams'
    | where ActivityType has_any ('FileDownloaded','FileAccessed')
    | summarize Events=count() by AccountDisplayName, bin(Timestamp, 1h)
    | where Events > 150

### SharePoint-Sync-Wellen SharePoint sync bursts

Sehr hohe Sync-Client-Aktivität. Very high sync client activity.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(24h)
    | where ActivityType has_any ('FileSyncDownloadedFull','FileSyncUploadedFull')
    | summarize SyncOps=count() by AccountDisplayName, bin(Timestamp, 1h)
    | where SyncOps > 500

### Verdächtige Graph-Export-App Suspicious Graph export app

Apps lesen viele Dateien oder Nachrichten in kurzer Zeit. Apps read many files or messages in a short time.

KQL KQL

    CloudAppEvents
    | where Timestamp > ago(7d)
    | where ActivityType has_any ('Read mail','Download file','List item accessed')
    | summarize Events=count() by Application, AccountDisplayName
    | where Events > 1000

### Massenlöschungen von Dateien Mass file deletions

Potenziell destruktives Verhalten auf Endpunkten oder Shares. Potential destructive behavior on endpoints or shares.

KQL KQL

    DeviceFileEvents
    | where Timestamp > ago(12h)
    | where ActionType in~ ('FileDeleted','FileRemoved')
    | summarize Deletes=count() by DeviceName, InitiatingProcessAccountName, bin(Timestamp, 30m)
    | where Deletes > 300

### Mail-Volumen zu externen Domänen Email volume to external domains

Spitzen im ausgehenden Mail-Volumen zu externen Empfängern. Spikes in outbound mail volume to external recipients.

KQL KQL

    EmailEvents
    | where Timestamp > ago(24h)
    | where DeliveryLocation == 'External'
    | summarize Messages=count() by SenderFromAddress, SenderFromDomain
    | where Messages > 100

## Query Optimization Query optimization

Best Practice Best practice | Warum Why | Beispiel Example
--- | --- | ---
Zeitfilter zuerst Time filters first | Reduziert Scanvolumen sofort Immediately reduces scan volume | | where Timestamp > ago(7d) | where Timestamp > ago(7d)
Spezifische Tabellen wählen Choose specific tables | Vermeidet unnötige Datenmenge Avoids unnecessary data volume | EmailEvents statt CloudAppEvents für Mail-Metadaten Use EmailEvents instead of CloudAppEvents for mail metadata
Kein * verwenden Avoid * | Project spart Ressourcen und Kontext Project saves resources and context | | project Timestamp, AccountUpn, IPAddress | project Timestamp, AccountUpn, IPAddress
has statt contains Use has over contains | Tokenisierte Suche ist schneller Tokenized search is faster | Subject has "invoice" Subject has "invoice"
Joins klein halten Keep joins small | Voraggregierte Tabellen joinen Join pre-aggregated tables | let suspicious = ... let suspicious = ...
Grenzwerte dokumentieren Document thresholds | Vermeidet spätere Fehlinterpretation Prevents later misinterpretation | Downloads > 200 muss tenant-spezifisch sein Downloads > 200 must be tenant-specific

Performance-Regel Performance rule

Starten Sie jede Jagd mit einem klaren Zeitfenster, der kleinstmöglichen Tabelle und wenigen Spalten. Optimierung ist Voraussetzung für reproduzierbare Ergebnisse. Start every hunt with a clear time window, the smallest possible table, and only a few columns. Optimization is required for reproducible results.