7.6 Key Tables and Schema for Security Operations

Key Tables and Schema for Security Operations

Introduction

Module 6 taught you KQL — the language. This subsection teaches you the data model — which tables exist, what data they contain, where the data comes from, and which columns matter for security investigation. This is reference material you will return to throughout Modules 8-10 and in your operational SOC work.

Sentinel stores data in dozens of tables. You do not need to memorise every table. You need to know the 20 tables that appear in 95% of security investigations and understand their schema well enough to write KQL queries against them without constantly checking documentation. This subsection provides that knowledge.

Identity tables

SigninLogs — Interactive user sign-ins to Entra ID. Every browser login, desktop app authentication, and mobile app sign-in creates a record here. This is the single most queried table in security operations.

Data source: Entra ID diagnostics connector.

Key columns: UserPrincipalName (who), IPAddress (from where), Location (country/city/state), AppDisplayName (which application), ResultType (0 = success; non-zero = specific error code), ConditionalAccessStatus (success/failure/notApplied), AuthenticationRequirement (singleFactorAuthentication or multiFactorAuthentication), RiskLevelDuringSignIn (none/low/medium/high — Entra ID Protection assessment), DeviceDetail (nested: OS, browser, displayName, trustType, isCompliant), MfaDetail (nested: authMethod, authDetail), OriginalTransferMethod, AuthenticationDetails (nested array: each step in the auth flow).

1
2
3
4
5
6
7
8

// Core investigation pattern: user sign-in timeline
SigninLogs
| where TimeGenerated > ago(24h)
| where UserPrincipalName == "j.morrison@northgateeng.com"
| project TimeGenerated, IPAddress, Location, AppDisplayName,
    ResultType, ConditionalAccessStatus, AuthenticationRequirement,
    RiskLevelDuringSignIn
| order by TimeGenerated desc

The ResultType column deserves specific attention. A value of 0 means successful authentication. Non-zero values indicate specific failures: 50126 (invalid username or password), 50074 (strong authentication required — MFA challenge), 53003 (blocked by conditional access), 50053 (account locked). Investigation queries frequently filter on ResultType to find failed sign-ins (potential brute force), MFA-challenged sign-ins (credential compromise with MFA holding), or conditional access blocks (policy enforcement).

AADNonInteractiveUserSignInLogs — Token refreshes, background sync, and application-initiated authentication without user interaction. Identical schema to SigninLogs. Very high volume (10-100× interactive sign-in volume). Critical for detecting token replay attacks: a stolen session token is used non-interactively from a new IP address while the legitimate user’s interactive sessions continue normally from their usual IP.

IdentityLogonEvents — Defender for Identity logon events from on-premises Active Directory domain controllers. Captures Kerberos and NTLM authentication that cloud-only SigninLogs does not see. Essential for hybrid identity environments.

Key columns: AccountName, AccountDomain, LogonType (Interactive, Network, RemoteInteractive, Service), DeviceName, DestinationIPAddress, Protocol (Kerberos, NTLM), FailureReason.

AuditLogs — Entra ID directory changes: user creation/modification/deletion, group membership changes, application consent grants, conditional access policy changes, role assignments, password changes. Every administrative action on the directory produces an AuditLogs record.

Key columns: OperationName, Category, Result, InitiatedBy (nested: user or app that performed the action), TargetResources (nested: what was changed — user, group, application, policy), AdditionalDetails.

1
2
3
4
5
6
7

// Detect new inbox rule creation via application consent
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Consent to application"
| extend InitiatorUPN = tostring(InitiatedBy.user.userPrincipalName)
| extend TargetApp = tostring(TargetResources[0].displayName)
| project TimeGenerated, InitiatorUPN, TargetApp, Result

Endpoint tables

DeviceProcessEvents — Process execution events from Defender for Endpoint. Every process creation on a managed endpoint is logged with full command line, parent process chain, user context, and file hash. The primary table for endpoint threat investigation.

Key columns: DeviceName, FileName, FolderPath, ProcessCommandLine, InitiatingProcessFileName (parent), InitiatingProcessCommandLine, InitiatingProcessParentFileName (grandparent), AccountName, AccountDomain, SHA256, MD5, ProcessId, Timestamp.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Detection pattern: encoded PowerShell execution
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine has_any
    ("-enc", "-encodedcommand", "downloadstring", "invoke-webrequest",
     "invoke-expression", "iex", "-nop", "bypass", "hidden")
| project TimeGenerated, DeviceName, AccountName,
    ProcessCommandLine, InitiatingProcessFileName,
    InitiatingProcessCommandLine
| order by TimeGenerated desc

DeviceNetworkEvents — Outbound network connections from managed endpoints. Source/destination IP, port, protocol, URL, remote domain. Essential for identifying command-and-control communication, data exfiltration, and lateral movement.

Key columns: DeviceName, RemoteIP, RemotePort, RemoteUrl, LocalIP, LocalPort, Protocol, InitiatingProcessFileName, InitiatingProcessCommandLine, ActionType (ConnectionSuccess, ConnectionFailed, InboundConnectionAccepted).

DeviceFileEvents — File creation, modification, deletion, and rename events on managed endpoints. Detects malware drops (new executable in temp folder), ransomware encryption patterns (rapid file rename with new extension), and data staging (large file creation before exfiltration).

DeviceRegistryEvents — Windows registry modifications. Detects persistence mechanisms: Run/RunOnce keys, service installations, scheduled task registration through registry, browser helper objects, WMI event subscriptions.

DeviceLogonEvents — Local and remote logon events on managed devices. Captures RDP sessions, network logons (SMB access), interactive logons, and service logons at the device level.

Email tables

EmailEvents — Email delivery events from Defender for Office 365. Every inbound and outbound email with sender, recipient, subject, delivery action, authentication results, and threat detection verdicts.

Key columns: SenderFromAddress, SenderFromDomain, RecipientEmailAddress, Subject, DeliveryAction (Delivered, Quarantined, Blocked, Replaced), DeliveryLocation, AuthenticationDetails (SPF/DKIM/DMARC pass/fail), ThreatTypes (Phish, Malware), ConfidenceLevel, NetworkMessageId (unique identifier for cross-table joins), InternetMessageId.

1
2
3
4
5
6
7

// Find all emails from a domain and check delivery status
EmailEvents
| where TimeGenerated > ago(7d)
| where SenderFromDomain == "suspicious-domain.com"
| project TimeGenerated, SenderFromAddress, RecipientEmailAddress,
    Subject, DeliveryAction, ThreatTypes, NetworkMessageId
| order by TimeGenerated desc

EmailUrlInfo — URLs found in email bodies. Joined with EmailEvents on NetworkMessageId to determine which URLs appeared in which emails. Investigation pattern: find the malicious URL → join to find all emails containing it → join with UrlClickEvents to find who clicked.

EmailAttachmentInfo — Attachments in emails. File name, file type, size, SHA256 hash, detection results. Joined on NetworkMessageId.

UrlClickEvents — Safe Links click tracking. Records every URL click in email and whether Safe Links allowed, blocked, or warned. Essential for answering “did anyone click the phishing link?” — the most urgent question in any phishing investigation.

Cloud and infrastructure tables

AzureActivity — Azure management plane (ARM) operations. Every resource creation, modification, deletion, RBAC change, and policy action. The audit trail for your Azure infrastructure.

Key columns: OperationNameValue (the ARM operation), CallerIpAddress, Caller (UPN or service principal), ResourceGroup, Resource, ActivityStatusValue (Succeeded/Failed), CategoryValue (Administrative/ServiceHealth/Policy), Properties (nested: operation-specific details).

CloudAppEvents — Defender for Cloud Apps events. Covers mailbox operations (MailItemsAccessed — the definitive evidence that an attacker read email), inbox rule manipulation (New-InboxRule, Set-InboxRule), SharePoint file access, OneDrive downloads, Teams actions, and third-party SaaS app events.

Key columns: ActionType (MailItemsAccessed, New-InboxRule, FileDownloaded, etc.), AccountObjectId, AccountDisplayName, IPAddress, City, CountryCode, Application, RawEventData (nested: full event detail — parse with parse_json()).

1
2
3
4
5
6
7

// Detect inbox rule creation — BEC persistence
CloudAppEvents
| where TimeGenerated > ago(7d)
| where ActionType in ("New-InboxRule", "Set-InboxRule")
| extend RuleDetails = parse_json(RawEventData)
| project TimeGenerated, AccountDisplayName, ActionType,
    IPAddress, CountryCode, RuleDetails

SecurityAlert — Aggregated alerts from all Defender products, Entra ID Protection, and Sentinel analytics rules. Each alert includes AlertName, Severity (Informational/Low/Medium/High), Description, Tactics (MITRE ATT&CK), Techniques, Entities (nested: users, IPs, devices, files linked to the alert), ProviderName (which product generated it), Status (New/InProgress/Resolved).

SecurityIncident — Sentinel incidents that group related alerts. Title, Severity, Status (New/Active/Closed), Classification (TruePositive/FalsePositive/BenignPositive), Owner (assigned analyst), Labels, AlertIds (linked alerts), RelatedAnalyticRuleIds.

Sentinel operational tables

SentinelHealth — Health events for data connectors, analytics rules, and automation rules. Connector failures, rule execution errors, and automation rule trigger problems. Query this table for operational monitoring (subsection 7.11).

SentinelAudit — Audit trail for Sentinel configuration changes. Who created/modified/deleted an analytics rule, automation rule, or data connector. Essential for change management.

ThreatIntelligenceIndicator — Threat intelligence IOCs: IP addresses, domains, URLs, file hashes with threat type, confidence score, and expiration date. Used in TI-matching analytics rules and hunting queries (subsection 7.8).

Watchlist — Named data sets uploaded as CSV. VIP user lists, approved IP ranges, critical asset inventories, allowlists/blocklists. Referenced in KQL using _GetWatchlist('WatchlistName') (subsection 7.7).

Table quick reference for investigation scenarios

Multi-table investigation queries: building the attack timeline

The real power of understanding table schema is writing multi-table queries that reconstruct attack timelines. Here are three investigation patterns that combine tables from different domains.

Pattern 1: Phishing → credential theft → BEC. This is the most common attack chain in M365 environments. The investigation crosses email, identity, and application tables.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

// Step 1: Find the phishing email
let PhishEmail = EmailEvents
| where TimeGenerated > ago(7d)
| where SenderFromDomain == "attacker-domain.com"
| where DeliveryAction == "Delivered"
| project NetworkMessageId, RecipientEmailAddress, Subject;
// Step 2: Who clicked the link?
let Clickers = UrlClickEvents
| where TimeGenerated > ago(7d)
| join kind=inner PhishEmail on NetworkMessageId
| project RecipientEmailAddress, ClickTime = Timestamp, Url;
// Step 3: Suspicious sign-ins after the click
let CompromisedSignIns = SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName in (Clickers | project RecipientEmailAddress)
| where RiskLevelDuringSignIn in ("medium", "high")
| project UserPrincipalName, SignInTime = TimeGenerated,
    IPAddress, Country = tostring(LocationDetails.countryOrRegion);
// Step 4: Post-compromise mailbox activity
CloudAppEvents
| where TimeGenerated > ago(7d)
| where AccountId in (CompromisedSignIns | project UserPrincipalName)
| where ActionType in ("New-InboxRule", "Set-InboxRule",
    "MailItemsAccessed", "Send")
| project TimeGenerated, AccountId, ActionType, IPAddress
| order by TimeGenerated asc

This query traces the full BEC attack chain across four tables. Each step narrows the scope: all phishing emails → emails that were clicked → users who had suspicious sign-ins after clicking → post-compromise mailbox activity by those users. The output is the complete attack timeline.

Pattern 2: Lateral movement detection. The investigation crosses identity and endpoint tables.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

// Find users who signed in from a suspicious IP,
// then logged into devices they have never used before
let SuspiciousSignIns = SigninLogs
| where TimeGenerated > ago(24h)
| where RiskLevelDuringSignIn == "high"
| project UserPrincipalName, RiskyIP = IPAddress,
    RiskyTime = TimeGenerated;
DeviceLogonEvents
| where Timestamp > ago(24h)
| where AccountName in (
    SuspiciousSignIns | project User = split(UserPrincipalName, "@")[0])
| where LogonType in ("Interactive", "RemoteInteractive")
| join kind=leftanti (
    DeviceLogonEvents
    | where Timestamp between(ago(30d) .. ago(24h))
    | where LogonType in ("Interactive", "RemoteInteractive")
    | distinct AccountName, DeviceName
) on AccountName, DeviceName
| project Timestamp, AccountName, DeviceName, LogonType,
    RemoteIP, ActionType

This query identifies users with high-risk sign-ins who then logged into devices they have not used in the past 30 days — a strong lateral movement indicator.

Knowledge check