K0.3 The Eight Tables You Will Query Every Day

SigninLogs — the identity investigation table

Every authentication to Entra ID (Microsoft 365, Azure portal, VPN via Entra SSO) generates a row in SigninLogs. This is the most-queried table in security operations because identity compromise is the starting point of 80% of cloud-based attacks.

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

// SigninLogs: key fields for investigation
SigninLogs
| where TimeGenerated > ago(1h)
| project
    TimeGenerated,              // When
    UserPrincipalName,          // Who
    IPAddress,                  // From where
    Location,                   // Country/city (geo-IP)
    ResultType,                 // Success (0) or failure code
    ResultDescription,          // Human-readable result
    AppDisplayName,             // Which application
    DeviceDetail,               // Browser, OS, device compliance
    ConditionalAccessStatus,    // CA policy evaluation result
    RiskLevelDuringSignIn,      // Entra ID Protection risk score
    IsInteractive               // Interactive (user present) vs non-interactive (token refresh)
| take 10

The field that matters most: ResultType. This single field determines whether the authentication succeeded (0), failed due to wrong password (50126), failed due to account locked (50053), failed due to MFA denial (500121), or dozens of other outcomes. Learning the 10 most common ResultType values is the fastest path to SigninLogs fluency.

NE investigation use: Rachel queries SigninLogs 15-20 times per day. The most common patterns: “show me all failures for this IP” (spray investigation), “show me all sign-ins for this user in the last 24 hours” (compromise scope), “show me non-interactive sign-ins with deviceDetail mismatch” (AiTM detection).

Figure K0.3 — The 8 daily tables. These handle 95% of security investigation queries at NE.

DeviceProcessEvents — the endpoint investigation table

Every process that starts on an MDE-enrolled device generates a row. This is the table that reveals WHAT AN ATTACKER DID on the endpoint: which tools they ran, which commands they executed, and which process spawned which child process.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

// DeviceProcessEvents: key fields for investigation
DeviceProcessEvents
| where TimeGenerated > ago(1h)
| project
    TimeGenerated,
    DeviceName,                     // Which device
    AccountName,                    // Which user ran the process
    FileName,                       // Process name (e.g., powershell.exe)
    ProcessCommandLine,             // Full command line arguments
    InitiatingProcessFileName,      // Parent process (who spawned this)
    InitiatingProcessCommandLine,   // Parent's command line
    FolderPath                      // Where the binary lives
| take 10

The field that matters most: InitiatingProcessFileName — the parent process. Security analysis is not about WHAT ran but about WHO SPAWNED IT. PowerShell spawned by Explorer.exe (user double-clicked a script) is different from PowerShell spawned by Word (macro execution). The parent-child relationship is the foundation of process-based detection.

OfficeActivity — the email and collaboration table

Every inbox rule, email send, SharePoint download, OneDrive sync, and Teams action generates a row. This table reveals what users DO with their M365 applications.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// OfficeActivity: key fields for investigation
OfficeActivity
| where TimeGenerated > ago(1h)
| project
    TimeGenerated,
    UserId,                 // Who performed the action (UPN)
    Operation,              // What they did (New-InboxRule, FileDownloaded, etc.)
    ClientIP,               // From which IP
    OfficeObjectId,         // The target object (file path, rule name)
    ResultStatus            // Success/failure
| take 10

The field that matters most: Operation. This single field determines whether you are looking at inbox rule creation, email forwarding, file downloads, sharing link creation, or dozens of other actions. Filter by Operation first, then investigate the details.

The remaining 5 tables

AuditLogs — Entra ID configuration changes: MFA registrations, role activations, app consent grants, conditional access policy modifications. The table that reveals whether an attacker changed your security configuration.

DeviceFileEvents — file operations on endpoints: creation, modification, deletion, rename. The table that reveals ransomware (mass modification), data staging (bulk copies to temp folders), and evidence destruction (log file deletion).

IdentityLogonEvents — Kerberos and NTLM authentication events from domain controllers. The table that reveals Kerberoasting, Pass-the-Hash, Golden Ticket usage, and on-premises lateral movement.

CommonSecurityLog — firewall, proxy, and VPN logs in CEF (Common Event Format). The table that reveals network-layer activity: blocked connections, cross-site traffic, and external communication patterns.

SecurityAlert — aggregated alerts from all connected products. The table that reveals what Defender, Sentinel, and third-party products have ALREADY detected. Use this to check: has this entity triggered other alerts recently?

Troubleshooting

“getschema returns no results for a table.” The table does not exist in your workspace — either the data connector is not enabled, or your licence tier does not include that product. Run union withsource=T * | distinct T to see which tables actually contain data in your workspace.

The table selection decision tree

When an investigation question arrives, the analyst’s first decision is: which table do I query? This decision tree handles 90% of cases:

“Who authenticated?” → SigninLogs (cloud authentication) or IdentityLogonEvents (on-premises AD authentication). If the question is about Entra ID sign-ins (cloud apps, VPN, Azure portal): SigninLogs. If the question is about Kerberos or NTLM authentication to domain controllers: IdentityLogonEvents.

“What process ran?” → DeviceProcessEvents. Always. This table contains every process creation event on MDE-enrolled devices. If the question involves: what command was executed, which tool was used, what spawned what — the answer is in DeviceProcessEvents.

“What happened with email?” → OfficeActivity (for user actions — inbox rules, sends, reads) or EmailEvents (for system actions — delivery, filtering, quarantine). If the question is “did the user create an inbox rule?” → OfficeActivity. If the question is “was the phishing email delivered or blocked?” → EmailEvents.

“What was changed in the cloud config?” → AuditLogs. MFA registrations, role activations, app consent grants, conditional access policy changes — all configuration modifications to Entra ID land in AuditLogs.

“What network traffic occurred?” → CommonSecurityLog (for firewall/proxy logs) or DeviceNetworkEvents (for endpoint network connections). If the question is about perimeter traffic (what did the firewall allow/deny): CommonSecurityLog. If the question is about endpoint connections (which process connected to which IP): DeviceNetworkEvents.

This decision tree is not comprehensive — some investigations require tables not listed here. But for the 8 daily tables, this covers the most common decision points. By K7 (Sentinel Tables Deep Dive), you will know every field in every table well enough to select without the decision tree.

NE’s table volumes: planning for query performance

Table volume directly affects query performance. Querying a table with 5,000 rows/day is instantaneous. Querying a table with 500,000 rows/day requires careful time filtering to avoid 30-second query times. NE’s daily volumes:

DeviceProcessEvents leads at approximately 32,000 events/day from 796 MDE-enrolled devices. This is the table where time filtering matters most — always start with where TimeGenerated > ago(1h) before adding other filters.

SigninLogs generates approximately 45,000 events/day — but this includes non-interactive token refreshes (AADNonInteractiveUserSignInLogs). Interactive sign-ins alone are approximately 3,000/day. The IsInteractive field separates the two.

OfficeActivity generates approximately 15,000 events/day covering email, SharePoint, OneDrive, and Teams. The RecordType field separates email operations (RecordType 2) from SharePoint operations (RecordType 6) from other activity types.

The ninth table you will need

The 8 daily tables handle 95% of investigation queries. The ninth — needed for 5% of investigations but CRITICAL when required — is AADNonInteractiveUserSignInLogs. This table contains non-interactive authentication events: token refreshes, application-level authentication, and service principal sign-ins. At NE, this table generates 42,000 rows/day — more than SigninLogs. Most of these are legitimate application activity. But AiTM token replay appears HERE, not in SigninLogs — because the attacker’s replayed token generates a non-interactive authentication event. DE4-002 queries this table specifically. If you query only SigninLogs during an AiTM investigation, you miss the attacker’s operational sessions entirely.