In this section
The Eight Tables You Will Query Every Day
You know that Microsoft security products generate telemetry in distinct tables. You may have opened the Sentinel Logs blade and seen the table list in the left panel. This section profiles the eight tables you will query most often — what each contains, which columns matter for investigations, and how to explore any table's schema in your own workspace. By the end, you can point to any investigation question and immediately name the correct table.
Scenario
A Northgate Engineering SOC analyst receives an alert: "Impossible travel detected for j.morrison@northgate.com." The analyst needs to determine whether this is a true compromise or a false positive from VPN usage. The investigation touches four tables in the first five minutes. SigninLogs shows the authentication events and their locations. DeviceProcessEvents reveals what ran on the device after authentication. OfficeActivity shows whether the user created inbox rules or downloaded files. AuditLogs shows whether any configuration changes followed the sign-in. Four tables, four investigation questions, one incident. Knowing which table answers which question is the difference between a five-minute triage and a thirty-minute search.
SigninLogs — who authenticated and what was the outcome?
Every interactive authentication to Entra ID generates a row in SigninLogs. Cloud application access, Azure portal sessions, VPN connections through Entra SSO, and any other authentication flow where a user is present and actively authenticating. This is the most-queried table in security operations because identity compromise is the entry point for the majority of cloud-based attacks.
The columns that matter most for investigations: UserPrincipalName identifies the account. IPAddress and Location identify where the authentication originated. The Location field contains a dynamic object with countryOrRegion, state, and city derived from geo-IP lookup. ResultType is the single most important column in the table: a value of 0 means success, 50126 means invalid password, 50053 means the account is locked after too many failed attempts, and 500121 means MFA was required but the user denied or timed out the challenge. Learning the ten most common ResultType values is the fastest path to SigninLogs fluency. ConditionalAccessStatus tells you whether CA policies evaluated as success, failure, or notApplied. RiskLevelDuringSignIn carries the Entra ID Protection risk assessment. DeviceDetail is a dynamic object containing the browser, operating system, and device compliance status.
A sign-in record for the impossible travel alert looks like this:
Three things in this record demand attention. The Location shows the Netherlands, but j.morrison is based in London. The DeviceDetail shows a non-compliant device with no trust type (meaning it is unmanaged and not joined to Entra ID. And AuthenticationRequirement shows singleFactorAuthentication, which means MFA was not enforced for this session. Together, these three fields suggest a session token replay from an unmanaged device in a different country. This is the pattern that separates a genuine compromise from VPN-related false positives) VPN usage changes the IP and location, but the device detail remains the same managed, compliant device the user always authenticates from.
DeviceProcessEvents — what executed on the endpoint?
Every process that starts on an MDE-enrolled device generates a row. DeviceProcessEvents is the endpoint investigation table. It tells you what ran, who ran it, what spawned it, and what the full command line contained.
The critical columns: FileName is the process name. ProcessCommandLine contains the full command line including arguments (this is where you find the difference between legitimate powershell.exe usage and powershell.exe -enc [Base64EncodedPayload]. InitiatingProcessFileName identifies the parent process, which establishes the execution chain. AccountName identifies the user context. SHA256 provides the file hash for threat intelligence lookup. FolderPath tells you where the binary lives) a cmd.exe running from C:\Windows\System32 is normal, but a cmd.exe running from C:\Users\Public\Downloads is a renamed binary and warrants immediate investigation.
The parent-child relationship is the most powerful feature of this table. Every row links to its parent through InitiatingProcessFileName, InitiatingProcessCommandLine, and InitiatingProcessParentFileName. This gives you a three-generation execution chain in a single row. When you see powershell.exe spawned by winword.exe, that chain tells you a Word document executed a macro that launched PowerShell. A classic initial access pattern. When you see cmd.exe spawned by svchost.exe, the chain reveals a service executing commands, which may indicate lateral movement through scheduled tasks or WMI. The chain is the context that separates malicious execution from normal system behavior.
At Northgate Engineering, this table generates approximately 32,000 events per day from 796 enrolled devices. It is the highest-volume security table in most workspaces, which means time filtering is critical — always constrain TimeGenerated before adding other filters to avoid slow queries.
OfficeActivity — what did users do inside Microsoft 365?
OfficeActivity captures user actions across Exchange, SharePoint, OneDrive, and Teams. Inbox rule creation, email forwarding configuration, file downloads, sharing permission changes, and Teams channel modifications all land in this table.
The Operation column is the key field (it tells you exactly what happened. New-InboxRule means someone created an inbox rule, which is one of the most reliable indicators of business email compromise. Attackers create inbox rules that forward emails containing keywords like "invoice," "payment," or "wire transfer" to an external address, or that move security notification emails to a hidden folder so the victim never sees them. FileDownloaded records a document download from SharePoint or OneDrive. Set-Mailbox with forwarding parameters indicates mail forwarding to an external address) another classic BEC persistence technique. The UserId column identifies who performed the action, and ClientIP identifies where they performed it from — cross-reference this with SigninLogs.IPAddress to confirm the same source.
The RecordType field separates email operations (value 2) from SharePoint operations (value 6) from Azure AD operations (value 8). Filtering on RecordType before other conditions dramatically reduces the scan range in high-volume workspaces. At Northgate Engineering, OfficeActivity generates approximately 15,000 events per day. Without RecordType filtering, a BEC investigation query scans all 15,000. With where RecordType == 2 as the first filter, the scan drops to approximately 4,000 email-specific events.
The BEC investigation sequence that SOC analysts at Northgate run follows a consistent path: query OfficeActivity for New-InboxRule operations by the compromised user, query for Set-Mailbox operations that add forwarding, query for FileDownloaded operations from SharePoint libraries containing financial documents, and query for Add-MailboxPermission operations that grant delegate access to other mailboxes. Each query targets a specific persistence or collection technique. K5 builds these queries in detail.
AuditLogs — what changed in the directory configuration?
AuditLogs records every configuration change in Entra ID: role activations through PIM, application registrations, consent grants, Conditional Access policy modifications, group membership changes, and MFA method registrations. This is the table that answers "who changed what, and when?"
The OperationName column identifies the action (values like Add member to role, Add application, Grant admin consent to application, and Update conditional access policy appear here. InitiatedBy is a dynamic object containing the actor) either a user principal name or a service principal. TargetResources contains the object that was modified — also a dynamic object, which means extracting specific fields requires the todynamic() function or bracket notation that K3 covers.
During a compromise investigation, AuditLogs is where you find persistence mechanisms. An attacker who has compromised a Global Admin account will register a new application with Mail.ReadWrite and Files.Read.All permissions (this survives a password reset because the application authenticates with its own credentials, not the user's. They may add credentials to an existing service principal that already has broad permissions, or grant admin consent to a malicious OAuth application. Each of these actions appears as a distinct OperationName in AuditLogs. Privileged Identity Management (PIM) activations also appear here) monitoring for role activations outside of expected patterns (unusual times, unusual roles for the user, activations from unfamiliar IPs) is a core detection use case for this table.
DeviceFileEvents — what files were created, modified, or deleted?
DeviceFileEvents records file system operations on MDE-enrolled endpoints. File creation, modification, deletion, and rename events each generate a row. The FileName, FolderPath, and ActionType columns are the primary investigation fields. Ransomware investigations depend on this table (the encryption wave appears as a burst of FileModified and FileRenamed events across a wide range of FolderPath values in rapid succession. Data exfiltration investigations use the same table) an attacker staging files in a staging directory before exfiltration creates FileCreated events with suspicious folder paths.
DeviceNetworkEvents — what did the endpoint connect to?
DeviceNetworkEvents records outbound network connections initiated by processes on MDE-enrolled devices. Each row links a process (InitiatingProcessFileName) to a destination (RemoteIP, RemotePort, RemoteUrl). This is the table that connects process activity to network behavior. You can determine not just that powershell.exe ran, but that it connected to 185.220.101.xx on port 443. Combine this with DeviceProcessEvents using the device name and timestamp to build a complete picture: what process ran, what command line it used, and where it reached out to.
CommonSecurityLog — what did the network perimeter see?
CommonSecurityLog ingests CEF-format logs from firewalls, web proxies, VPN concentrators, and network appliances. Palo Alto, Fortinet, Check Point, Zscaler, and dozens of other vendors write to this table through the CEF connector. The DeviceAction column records whether the appliance allowed, denied, or dropped the connection. SourceIP, DestinationIP, and DestinationPort identify the traffic flow. DeviceVendor and DeviceProduct identify the source appliance.
This table provides the perimeter context that endpoint tables lack. DeviceNetworkEvents tells you the endpoint initiated a connection. CommonSecurityLog tells you whether the firewall permitted it to reach its destination. Correlating the two tables by IP address and timestamp window closes a common investigative gap.
SecurityAlert — what did the detection products flag?
SecurityAlert aggregates alerts from every connected security product into a normalized schema. A single query against this table returns alerts from Defender for Endpoint, Defender for Office 365, Defender for Identity, Defender for Cloud Apps, and any third-party product with an alert connector. The AlertName identifies the detection. Severity ranges from Informational through High. Entities is a JSON field containing the affected accounts, devices, IPs, and files. This field is the pivot point for correlating an alert with evidence in the other seven tables.
The investigation workflow that starts from SecurityAlert follows a consistent pattern. An alert fires. Say, "Suspicious PowerShell command line" from Defender for Endpoint. The Entities field contains the device name, the account name, and the process details. You extract the device name and query DeviceProcessEvents for the full process chain. You extract the account name and query SigninLogs to determine how the user authenticated and from where. You check AuditLogs to see whether the compromised account made configuration changes. Each entity in the alert becomes a pivot into a different table. K6 teaches you to parse the Entities JSON programmatically and automate these pivots.
The ninth table you will need
The eight tables above handle 95% of investigation queries. The ninth (AADNonInteractiveUserSignInLogs) handles one specific scenario that is critical when it arises. This table records non-interactive authentication events: token refreshes, application-initiated authentication, and service principal sign-ins. At Northgate Engineering, this table generates 42,000 rows per day — more volume than SigninLogs — because every time a browser tab refreshes a token or an application authenticates silently, a row is created. Most of these events are legitimate background activity.
But AiTM token replay appears in this table, not in SigninLogs. When an attacker replays a stolen session token, the authentication is non-interactive. The token refreshes silently without the user being present. If you query only SigninLogs during an AiTM investigation, you miss the attacker's operational sessions entirely. The detection pattern is a non-interactive sign-in from an IP address and device that do not match the user's normal interactive sign-in profile. K8 teaches the specific detection queries.
Exploring any table's schema
When you encounter an unfamiliar table or need to check the exact column names before writing a query, getschema returns the complete column inventory:
The first few rows of the output show the column inventory:
This returns one row per column: the column name, the data type (string, datetime, dynamic, int, real), and a brief description. For SigninLogs, getschema returns over 40 columns. You do not need to memorize all of them (the eight to ten columns described in this section handle the vast majority of investigation queries. But getschema is the reference you use when you need a column that is not in your working set. Run it against each of the eight tables in your own workspace to build familiarity with the schema. Pay attention to columns with type dynamic) these contain nested JSON objects that require special extraction operators (todynamic, tostring, parse_json) to query, which K3 covers in detail.
The analyst needs to find out whether a phishing email was delivered. They query OfficeActivity and find no delivery record. They conclude the email was blocked. In fact, OfficeActivity records user actions inside the mailbox, not the mail delivery pipeline. Email delivery events live in EmailEvents (Defender XDR Advanced Hunting). The analyst spent twenty minutes querying the right question against the wrong table. The table selection decision tree in the KQL Principle below prevents this: start with the investigation question, not the table name.
KQL Principle
Start every investigation with the question, not the table. "Who authenticated?" → SigninLogs. "What process ran?" → DeviceProcessEvents. "What did the user do in their mailbox?" → OfficeActivity. "What configuration changed?" → AuditLogs. "What file operations occurred?" → DeviceFileEvents. "Where did the endpoint connect?" → DeviceNetworkEvents. "What did the perimeter see?" → CommonSecurityLog. "What did detection products flag?" → SecurityAlert. Name the question, then name the table. The reverse — browsing tables hoping to find relevant data — wastes investigation time.
Get weekly detection and investigation techniques
KQL queries, detection rules, and investigation methods — the same depth as this course, delivered every Tuesday.
No spam. Unsubscribe anytime. ~2,000 security practitioners.