Skip to content

Liberty91LTD/cti-skills / skills/kql-writing

kql-writing

Verified

Use when the user asks for a KQL query, a Microsoft Sentinel / Defender / Azure Log Analytics detection or hunt, or wants to translate a finding from `/hash-investigation` / `/malware-analysis` into KQL. Format spec + writing guide.

KQL Writing Guide — Microsoft Sentinel

KQL (Kusto Query Language) is used in Microsoft Sentinel, Microsoft Defender, and Azure Data Explorer for querying security logs and building detection rules.

Core Syntax

Table references

SecurityEvent                        // Windows Security Events
DeviceProcessEvents                  // Defender for Endpoint
DeviceNetworkEvents                  // Network connections
DeviceFileEvents                     // File operations
EmailEvents                         // Defender for Office 365
SigninLogs                           // Azure AD sign-ins
AuditLogs                           // Azure AD audit
CommonSecurityLog                   // CEF/Syslog
ThreatIntelligenceIndicator         // TI feed indicators

Operators

| where TimeGenerated > ago(24h)     // Time filter
| where EventID == 4688              // Exact match
| where ProcessCommandLine contains "-enc"  // Substring
| where ProcessCommandLine matches regex @".*-e(nc)?.*"  // Regex
| where SourceIP !in ("10.0.0.1", "10.0.0.2")  // Not in list
| where isnotempty(AccountName)       // Not null/empty
| extend NewColumn = extract(@"pattern", 1, SourceField)  // Extract
| project TimeGenerated, Account, Computer  // Select columns
| summarize count() by bin(TimeGenerated, 1h), Account  // Aggregate
| sort by TimeGenerated desc          // Sort
| take 100                            // Limit results
| join kind=inner (OtherTable) on CommonField  // Join

String operators

OperatorDescriptionCase-sensitive
==Exact matchYes
=~Exact matchNo
containsSubstringNo
contains_csSubstringYes
startswithStarts withNo
endswithEnds withNo
matches regexRegex matchYes
hasWord boundary matchNo
inIn listYes
in~In listNo

Common Detection Patterns

Suspicious process execution (T1059)

DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName in~ ("powershell.exe", "cmd.exe", "wscript.exe", "cscript.exe")
| where ProcessCommandLine contains_cs "-enc"
    or ProcessCommandLine contains "bypass"
    or ProcessCommandLine contains "downloadstring"
| project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine, InitiatingProcessFileName

Suspicious parent-child (T1566.001)

DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where InitiatingProcessFileName in~ ("outlook.exe", "winword.exe", "excel.exe", "powerpnt.exe")
| where FileName in~ ("cmd.exe", "powershell.exe", "wscript.exe", "mshta.exe", "certutil.exe")
| project TimeGenerated, DeviceName, AccountName, InitiatingProcessFileName, FileName, ProcessCommandLine

Outbound connection to IOC (C2)

let IOC_IPs = dynamic(["203.0.113.42", "198.51.100.10"]);
let IOC_Domains = dynamic(["evil.example.com", "c2.badactor.net"]);
DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where RemoteIP in (IOC_IPs) or RemoteUrl has_any (IOC_Domains)
| project TimeGenerated, DeviceName, RemoteIP, RemoteUrl, RemotePort, InitiatingProcessFileName

Failed sign-in brute force (T1110)

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedAttempts = count(), DistinctAccounts = dcount(UserPrincipalName)
    by IPAddress, bin(TimeGenerated, 15m)
| where FailedAttempts > 10
| sort by FailedAttempts desc

Lateral movement — PsExec (T1570)

DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName =~ "psexesvc.exe"
    or (FileName =~ "cmd.exe" and ProcessCommandLine contains "\\\\")
    or ProcessCommandLine contains "-accepteula -s cmd"
| project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine

DNS query for suspicious domain (T1071.004)

DeviceEvents
| where TimeGenerated > ago(24h)
| where ActionType == "DnsQueryResponse"
| extend DnsQuery = extractjson("$.DnsQueryString", AdditionalFields)
| where DnsQuery endswith ".onion.ws"
    or DnsQuery endswith ".tor2web.io"
    or DnsQuery matches regex @"^[a-z0-9]{20,}\."
| project TimeGenerated, DeviceName, DnsQuery

Sentinel Analytics Rule Format

// Rule name: [Descriptive title]
// Description: [What this detects and why]
// MITRE ATT&CK: [Technique IDs]
// Severity: High|Medium|Low|Informational
// Tactics: [InitialAccess, Execution, etc.]

// Query:
[KQL query here]

Best Practices

  • Always include a time filter (ago()) to limit query scope
  • Use has instead of contains when searching for whole words (faster)
  • Prefer in~ over multiple or conditions for case-insensitive list matching
  • Use let statements for IOC lists to keep queries readable
  • Add project to select only needed columns (reduces result size)
  • Test queries on small time windows first before expanding
  • Include comments explaining detection logic

Running against a live workspace

This skill authors queries. To run them against a real Microsoft Sentinel workspace — or to hunt interactively — chain /lookup-sentinel. Two rules apply the moment a query targets live data:

  1. Verify table availability first. The table references in this guide (and in any published hunting query) assume connectors the target environment may not have. /lookup-sentinel discovers which tables the workspace actually ingests (tables / ingestion / probe) and adapts — e.g. no DeviceProcessEvents (no Defender for Endpoint) means falling back to SecurityEvent EventID 4688, which itself requires command-line auditing. Never ship a hunt referencing unverified tables.
  2. A query that runs is not a query that saw. When a fallback table has weaker fidelity (or the preferred table is absent entirely), state what the environment could not observe alongside the results.

For portable analytics rules meant for any environment, keep the canonical table names from this guide and document the connector prerequisite in the rule header comment.

Output Location

Write KQL queries to: data/detection-rules/kql/<technique-id>-<slug>.kql

writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 322kUpdated Claude CodeCodexVerifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 118kUpdated Claude CodeCodexVerifiedInstall