7.7 Watchlists: Named Data for KQL Enrichment

Watchlists: Named Data for KQL Enrichment

Introduction

A watchlist is a named data set uploaded to Sentinel as a CSV file and referenced in KQL queries using the _GetWatchlist() function. Watchlists transform static reference data — VIP user lists, approved IP ranges, critical asset inventories, known-good file hashes, terminated employee lists — into dynamic query enrichment that makes analytics rules smarter and investigations faster.

Without watchlists, you hardcode reference data into KQL: | where UserPrincipalName in ("ceo@northgateeng.com", "cfo@northgateeng.com"). When the executive team changes, you update every query and analytics rule that references it. With watchlists, you write a single join against _GetWatchlist('VIP-Users') — and when executives change, you update the watchlist once. Every query and rule that references it automatically uses the updated data.

Creating a watchlist

Step 1: Prepare the CSV file. The CSV must have a header row. One column is designated as the SearchKey — the primary lookup field. UTF-8 encoding, no trailing commas, no embedded line breaks within fields.

Example CSV for a VIP user watchlist (vip-users.csv):

UserPrincipalName,DisplayName,Title,RiskTier
ceo@northgateeng.com,Sarah Chen,Chief Executive Officer,Critical
cfo@northgateeng.com,Mark Thompson,Chief Financial Officer,Critical
cto@northgateeng.com,Priya Patel,Chief Technology Officer,Critical
vp.sales@northgateeng.com,James Wilson,VP Sales,High
vp.engineering@northgateeng.com,Lisa Nakamura,VP Engineering,High

Step 2: Create the watchlist in Sentinel. Navigate to Microsoft Sentinel → Watchlist → Add new. Provide the Name (alias used in KQL — keep short: VIP-Users, Approved-IPs, Critical-Assets), Description, Source type (Local file), upload the CSV, and select the SearchKey column.

Step 3: Verify.

1
2

_GetWatchlist('VIP-Users')
| project UserPrincipalName, DisplayName, Title, RiskTier

KQL patterns with watchlists

Pattern 1: Enrichment join — Add watchlist context to query results.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Enrich failed sign-ins with VIP status
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| join kind=inner (
    _GetWatchlist('VIP-Users')
    | project UserPrincipalName, RiskTier
) on UserPrincipalName
| project TimeGenerated, UserPrincipalName, RiskTier,
    IPAddress, Location, ResultType
| order by TimeGenerated desc

This returns only failed sign-ins for VIP users. Without the watchlist, you see all failed sign-ins and manually filter. With the watchlist, the query does the filtering for you — and the RiskTier column provides immediate context for severity assessment.

Pattern 2: Exclusion filter — Remove known-good items from detection results.

1
2
3
4
5
6
7
8

// Exclude approved IPs from foreign sign-in detection
let ApprovedIPs = _GetWatchlist('Approved-IPs')
    | project SearchKey;
SigninLogs
| where TimeGenerated > ago(24h)
| where IPAddress !in (ApprovedIPs)
| where tostring(Location.countryOrRegion) != "GB"
| project TimeGenerated, UserPrincipalName, IPAddress, Location

This detects sign-ins from non-UK IPs that are not on the approved list. VPN exit nodes, partner offices, and other verified foreign IPs are excluded. When VPN infrastructure changes, update the watchlist — no rule modification needed.

Pattern 3: Severity escalation — Dynamic severity based on asset criticality.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Escalate Mimikatz alerts on critical assets
DeviceProcessEvents
| where TimeGenerated > ago(1h)
| where FileName == "mimikatz.exe"
    or ProcessCommandLine has "sekurlsa"
| join kind=leftouter (
    _GetWatchlist('Critical-Assets')
    | project DeviceName = SearchKey, AssetTier
) on DeviceName
| extend AlertSeverity = iff(
    AssetTier == "Critical", "High", "Medium")

Mimikatz on a workstation: medium severity. Mimikatz on a domain controller (listed as Critical in the watchlist): high severity. The watchlist drives the decision.

Pattern 4: Honeytoken detection — Instant high-severity alerts for decoy accounts.

1
2
3
4
5
6
7
8
9

// Any activity on a honeytoken account = immediate alert
SigninLogs
| where TimeGenerated > ago(1h)
| join kind=inner (
    _GetWatchlist('Honeytokens')
    | project UserPrincipalName = SearchKey
) on UserPrincipalName
| project TimeGenerated, UserPrincipalName, IPAddress,
    Location, AppDisplayName, ResultType

Honeytoken accounts should never authenticate. Any sign-in attempt — success or failure — indicates credential compromise (the attacker found the credentials) and warrants immediate investigation. This is one of the highest-fidelity detections available because there is zero legitimate use.

Common watchlists for SOC operations

Management best practices

Size limits. Watchlists support up to approximately 10 million rows. Most SOC watchlists are small (10-1,000 rows). Large watchlists (100,000+ rows) increase query latency — use custom tables instead.

Update methods. Small watchlists: delete and recreate with the updated CSV. Large watchlists: use the Sentinel REST API to update individual rows. Automated updates: Logic Apps playbook that pulls from HR (terminated users), asset management (critical assets), or TI feeds (blocklists).

Naming conventions. Short, hyphenated, descriptive: VIP-Users, Approved-IPs. The name appears in every KQL query — keep it readable.

Watchlist vs custom table. Watchlists: small, frequently referenced lookup data (hundreds to thousands of rows), easy CSV upload. Custom tables: large, frequently updated data sets (hundreds of thousands of rows), need retention policies, need analytics rule evaluation.

Watchlist versioning and change tracking

Watchlists do not have built-in version history. When you upload a new CSV, the previous data is replaced. For operational accountability, implement your own versioning.

Git-based versioning. Store watchlist CSVs in the same Git repository as your analytics rules. When a watchlist needs updating, the analyst modifies the CSV, commits the change with a descriptive message (“Added new VPN exit IPs for London office expansion”), and the CI/CD pipeline uploads the updated CSV to Sentinel. The Git history provides a complete audit trail: who changed what, when, and why.

Before/after comparison. Before updating a watchlist, export the current version: _GetWatchlist('Approved-IPs') | project SearchKey, IPAddress, Description. Save the results. Upload the new CSV. Compare the new watchlist output with the saved export to verify the changes are correct and no entries were accidentally removed.

Impact assessment before updates. Before updating a watchlist that is referenced by analytics rules, assess the impact: will the update add entries that suppress alerts (adding IPs to an exclusion list)? Will it remove entries that re-enable alerts (removing IPs that were previously excluded)? Either change affects the SOC’s alert volume. Notify the SOC team before making watchlist changes that significantly affect alert volume.

Watchlist troubleshooting

Issue: “_GetWatchlist returns empty results.” The watchlist exists but returns no data. Common causes: the watchlist name in the query does not match the alias (watchlist names are case-sensitive), the CSV had formatting errors (embedded line breaks, UTF-8 BOM characters, trailing commas) that prevented rows from being parsed, or the watchlist is still being processed (large watchlists take several minutes to become queryable after upload).

Issue: “Join returns no results even though matching data exists.” The join column data types do not match. Watchlist columns are always strings. If you are joining on an IP address column, ensure the log table’s IP column is also a string (use tostring() if necessary). Also verify the column names match exactly — IPAddress in the watchlist must match the join column name in the query.

Issue: “Watchlist query is slow.” Watchlists larger than 50,000 rows can cause noticeable query latency. Optimise by: reducing the watchlist size (do you need all entries, or can you filter to active/relevant entries only?), using let to materialise the watchlist once at the start of the query rather than calling _GetWatchlist() multiple times, and considering migration to a custom table for very large datasets.

1
2
3
4
5
6
7

// Optimised: materialise watchlist once
let VIPs = materialize(_GetWatchlist('VIP-Users')
    | project UserPrincipalName);
SigninLogs
| where TimeGenerated > ago(1h)
| where UserPrincipalName in (VIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, RiskLevelDuringSignIn

Issue: “Watchlist CSV upload fails.” Validate the CSV before uploading: UTF-8 encoding without BOM, header row with no spaces in column names, consistent column count across all rows, no embedded newlines within quoted fields, and file size under the maximum (currently 3.8 MB per upload — for larger files, use the API).

Automating watchlist updates with Logic Apps

Manual CSV uploads work for watchlists that change infrequently (VIP users, honeytoken accounts). For watchlists that change frequently — terminated employees updated daily, approved IPs updated when infrastructure changes — automate the update process with Logic Apps playbooks.

Terminated user watchlist automation. Create a Logic App that runs daily on a schedule. The Logic App queries your HR system’s API (Workday, BambooHR, SAP SuccessFactors) for employees terminated in the last 24 hours. It formats the results as a CSV, calls the Sentinel Watchlist API to update the watchlist, and logs the update in the activity log. The terminated user watchlist is always current without manual intervention.

Approved IP watchlist automation. Create a Logic App triggered by an Azure DevOps webhook. When the network team deploys new infrastructure (VPN concentrators, office firewalls, cloud NAT gateways), the deployment pipeline fires a webhook. The Logic App extracts the new IP ranges from the deployment manifest, updates the Approved-IPs watchlist, and sends a confirmation to the SOC team Slack channel. New infrastructure IPs are automatically excluded from foreign sign-in detections within minutes of deployment.

IP reputation watchlist automation. Create a Logic App that runs weekly, pulling known-malicious IP lists from community sources (Abuse.ch, Emerging Threats). It formats the IPs as a watchlist update and pushes them to a “Known-Malicious-IPs” watchlist. Analytics rules that detect connections to these IPs fire immediately when any device communicates with a listed IP — providing community-sourced detection without a full TI platform.

Watchlist-driven analytics rule patterns

Watchlists become most powerful when they drive analytics rule logic — not just enrichment, but the core detection decision.

Pattern: Geographical impossibility. The High-Risk-Countries watchlist contains country codes where your organisation has no business operations. The analytics rule detects any sign-in from these countries — but the detection threshold and severity are driven by the watchlist, not hardcoded:

1
2
3
4
5
6
7
8

let HighRisk = _GetWatchlist('High-Risk-Countries');
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| join kind=inner HighRisk on $left.Country == $right.CountryCode
| project TimeGenerated, UserPrincipalName, IPAddress,
    Country, RiskLevel, AppDisplayName

When geopolitical circumstances change (you open an office in a previously high-risk country), update the watchlist — the rule adjusts without modification.

Pattern: Service account anomaly. The Service-Accounts watchlist contains all known service accounts. The analytics rule detects interactive sign-ins from service accounts (which should only authenticate non-interactively) and sign-ins from unexpected IPs:

1
2
3
4
5
6
7
8

let ServiceAccounts = _GetWatchlist('Service-Accounts');
SigninLogs
| where TimeGenerated > ago(1h)
| join kind=inner ServiceAccounts on UserPrincipalName
| where AuthenticationDetails has "password"  // Interactive auth
    or IPAddress != ExpectedIP  // From unexpected source
| project TimeGenerated, UserPrincipalName,
    ExpectedIP, ActualIP = IPAddress, AppDisplayName

Service account compromise is a common persistence mechanism — the attacker uses a service account because it is less monitored than user accounts. This watchlist-driven rule catches the anomaly.

Knowledge check