0.3 The Tables a Security Analyst Queries

Introduction

A security workspace holds dozens of tables, and the first skill after writing a query is knowing which table to point it at. The tables fall into families that match the parts of an investigation. Identity tables say who signed in and what changed in the directory.

Endpoint tables say what ran, connected and wrote files on each device. Mail and cloud tables say what arrived and what people did with it. Alert tables say what the security products concluded. Server and network tables cover the machines and traffic the endpoint sensor does not reach.

This sub introduces the 21 tables in the course's practice lab, family by family, with what each records and how large it is. You'll count every table in one query, see how the three sign-in tables divide the month between them, and look at what normal activity looks like in the process and mail tables.

You'll also see which products raised the month's alerts, and why the alerts are not the whole story. The month holds several real attacks that the tables recorded as they happened, among them a password spray, a session theft and a ransomware infection, and each later module uses these tables to find them.

The diagram is the lab's map, and a real Sentinel and Defender workspace falls into the same families, though it usually holds many more tables. Five families, 21 tables, each family answering a different part of an investigation. Most real questions cross families, sometimes three or four of them, which is why the course spends whole modules on combining tables.

The tables in the lab are real table names with real column names, chosen from the hundreds a full workspace can hold because they are the ones security investigations use most. Section 0.6 explains what the lab includes and leaves out; for now, each of these 21 tables exists under the same name in Sentinel or Defender, and a query written here runs there with little or no change.

A workspace's tables are easiest to understand all at once, side by side, by size, before looking at any of them in detail. Before the families, then, the whole lab: one union that reads every table and counts its rows, labeled with withsource so that each count says which table it belongs to.

union withsource = SourceTable SigninLogs, AADNonInteractiveUserSignInLogs,
    AADServicePrincipalSignInLogs, AuditLogs, DeviceProcessEvents, DeviceNetworkEvents,
    DeviceFileEvents, DeviceLogonEvents, DeviceEvents, DeviceRegistryEvents, DeviceInfo,
    EmailEvents, OfficeActivity, CloudAppEvents, SecurityAlert, AlertEvidence,
    SecurityIncident, Syslog, CommonSecurityLog, IdentityLogonEvents, IdentityInfo
| summarize Rows = count() by SourceTable
| sort by Rows desc

The result is a list of 21 tables and their sizes. The largest are OfficeActivity, with 16,276 records of activity in mailboxes and SharePoint, and EmailEvents, with 15,468 messages. The non-interactive sign-ins, process starts, cloud application events and interactive sign-ins follow, each with eight to fourteen thousand.

The smallest are the inventories: DeviceInfo with 225 rows and IdentityInfo with 97, one per account. Event tables grow with activity; inventories grow with the organization. Both kinds are needed, for different questions.

The completed query below uses the same withsource option on the sign-in tables.

8,203, 13,586 and 1,522: the three ways an identity signs in, recorded separately, each with its own columns and its own uses.

Sizes are worth noticing for another reason: they change what is practical. A table of a hundred rows can be read in full; a table of fifteen thousand cannot, and needs filtering and summarizing before anything can be read.

In a real workspace the same tables hold millions of rows a day, and the habits of Module 10 decide whether a query on them returns in seconds or not at all.

Identity is where many cloud attacks begin, because an account is the key to everything the company runs in Microsoft 365. Identity tables come from Microsoft Entra ID, the directory that holds the company's accounts. SigninLogs records interactive sign-ins, a person at a prompt.

AADNonInteractiveUserSignInLogs records sign-ins made on a person's behalf without a prompt, such as an application refreshing a token. AADServicePrincipalSignInLogs records applications signing in as themselves. AuditLogs records changes to the directory: accounts created, roles assigned, applications consented to.

union withsource = SourceTable SigninLogs, AADNonInteractiveUserSignInLogs,
    AADServicePrincipalSignInLogs
| summarize Rows = count() by SourceTable

The counts show how activity is divided. The non-interactive table is the largest of the three, with 13,586 rows against 8,203 interactive sign-ins, because background token use is constant. An investigation into an account that reads only the interactive table sees the prompts and misses most of what the account's sessions did, which is why Module 10 counted the non-interactive table among its first checks.

AuditLogs
| summarize Events = count() by OperationName
| top 5 by Events desc

The directory's routine, read from the five most common operations, is administration: role memberships added and removed, permissions granted to applications, users updated. AuditLogs is small, 532 events in the month, and every event in it is a change someone made to the directory, which makes it one of the most valuable tables for understanding how an attacker who reached an account tried to keep it.

Two more identity tables are inventories and logons. IdentityInfo has one row per account, with department, job title and manager. IdentityLogonEvents, from Defender for Identity, records authentication against the on-premises domain controllers, 2,567 events in the month, which is where attacks on the older, on-premises half of identity show up.

The exercise below adds the missing sign-in table.

Two rows, one per table: 163 interactive sign-ins and 251 non-interactive ones for the same account. For c.richardson, as for every account, the two tables together describe the account's activity; either alone describes part of it, and the larger part is the one most often left out.

The identity tables also connect to each other through the account name, which appears in every one of them. That shared key is what lets a question start with a suspicious sign-in and follow the account into the directory changes it made, the applications that act for it, and the inventory that says who it belongs to. Module 5 builds those connections with joins.

IdentityInfo and the sign-in tables also differ in what they describe: the inventory says who an account belongs to as the directory records it, and the sign-ins say what the account did. The two disagree sometimes, and Module 9 found that the disagreements were worth reading.

Endpoint tables describe what happens on machines, which is where malware runs and where attackers who reach a device work. Endpoint tables come from Microsoft Defender for Endpoint, the sensor on each laptop and server.

DeviceProcessEvents records every program started, with its command line and its parent. DeviceNetworkEvents records connections. DeviceFileEvents records files created, changed and deleted. DeviceLogonEvents records logons to the device. DeviceRegistryEvents records changes to the Windows registry. DeviceEvents holds other security events, and DeviceInfo is the device inventory.

DeviceProcessEvents
| summarize Starts = count() by FileName
| top 5 by Starts desc

Counting programs by name is the quickest way to see a device estate's ordinary activity. The most common programs started in the month are Edge, Word, PowerShell, the Windows service host and Chrome. That is what a working company looks like.

PowerShell's place among them is worth noticing: it is an ordinary administration tool used hundreds of times a month, which is why a query that flags every PowerShell start would be useless, and why later modules look at how and from where it was started rather than whether.

Endpoint tables share a structure, which makes them easier to learn as a group: each row names the device, the account the activity ran as, and the process that caused it, with that process's own command line and parent. That makes it possible to ask what caused something on a device as well as what happened, which is the basis of the process trees Module 9 builds.

The endpoint tables are also the largest family by number of tables, and the one that most rewards knowing which table to use: a question about a file goes to DeviceFileEvents, about a connection to DeviceNetworkEvents, about a logon to DeviceLogonEvents, and the process that did each is recorded alongside.

The device inventory deserves a mention of its own. DeviceInfo has a row for each device at intervals, recording its operating system, the users logged on, its onboarding status and its sensor's health, which turns a device name in an event into a machine with a context. All 15 devices in the sample inventory run Windows 11.

Across the sub so far, and before any attack was looked for, the lab's 21 tables fell into families, each counted and sampled with a query or two, each with its own scale and character: large event tables, small inventories, and identity activity split across several tables.

The record collects the families and what each answers, as a reference for choosing tables later.

The last row is easy to overlook because the inventories are small and describe things, accounts and devices, rather than events. They are what turn an account name into a person with a department and a manager, and a device name into a machine with an owner and an operating system.

The first two rungs come from the question; the last two from experience. The third rung is the one that most changes an investigation's result. The neighbors of the obvious table, the non-interactive sign-ins next to the interactive ones, the audit log next to the sign-ins, hold much of what the obvious table cannot.

The first mistake is the most common in identity work, and the third in reporting. The second mistake is common because alerts are where most investigations begin in practice. An alert is a product's conclusion from evidence; the evidence itself, and everything the product did not conclude, is in the event tables.

A useful way to remember the families is by the question a manager asks after an incident. Who was involved: identity. What happened on the machines: endpoint. How did it arrive and what was taken: mail and cloud. What did our tools see: alerts. What about the servers and the network: the last family. Each question points to its tables.

None of these families is complete on its own, and none is needed for every question. The skill is choosing: the fewest tables that hold the whole answer, read in an order that lets each one narrow the next.

Mail is one of the most common ways attacks reach people, and what they do afterwards happens in cloud applications. Mail and cloud tables cover the work people do in Microsoft 365.

EmailEvents, from Defender for Office 365, records each message's delivery, one row per message in the sample month: sender, recipient, subject, direction and verdict. OfficeActivity records what happens in Exchange mailboxes, SharePoint and OneDrive. CloudAppEvents, from Defender for Cloud Apps, records activity in cloud applications more broadly.

EmailEvents
| summarize Messages = count() by EmailDirection
| sort by Messages desc

Counting mail by direction shows what kind of mail flow the company has. Most of the month's mail, 12,967 messages, arrived from outside; 1,324 went out and 1,177 stayed inside the company.

Inbound mail is also where phishing arrives, among ordinary newsletters, invoices and replies, which makes EmailEvents the first table for any question about a lure. What a recipient did after opening a message, forwarding it, creating a rule, downloading a file, is in OfficeActivity and CloudAppEvents.

The mail and cloud tables connect to identity through the account and to each other through message identifiers and file names, which is how an investigation follows a lure from its arrival to what the recipient did next. Each table holds one stage; the story needs all three.

CloudAppEvents deserves a note because it overlaps the others. Defender for Cloud Apps sees activity across many cloud services, including Microsoft 365, so some of what it records also appears in OfficeActivity, from a different source and with different columns. When the two disagree, or one has what the other lacks, both are worth reading.

Every product in Defender raises alerts when its detections match, and Sentinel adds its own. Alert tables hold the conclusions security products have drawn. SecurityAlert records each alert, AlertEvidence the entities each alert names, and SecurityIncident the incidents that group alerts together and track how they were handled.

SecurityAlert
| summarize Alerts = count() by ProviderName
| sort by Alerts desc

Counting alerts by the product that raised them shows where the products are looking. Six products raised the month's 710 alerts. Defender for Cloud Apps, recorded as MCAS, raised the most, 427; Entra ID Protection, recorded as IPC, raised 150; then Defender for Office 365, Defender for Identity, Defender for Endpoint and Sentinel's own scheduled rules.

The provider names are the products' older internal names, which the tables keep, so MCAS rather than Defender for Cloud Apps appears in queries.

SecurityIncident
| summarize Incidents = dcount(IncidentNumber), Rows = count()

SecurityIncident shows why counting rows is not always counting things, which is a lesson for every table that tracks an object over time. Its 1,278 rows describe 540 incidents, because each incident gains a new row every time its status, owner or classification changes.

Counting distinct incident numbers counts incidents; counting rows counts updates, a distinction worth checking in any unfamiliar table before counting anything in it. The difference is typical of tables that track something over time.

Alerts are a starting point, not a boundary, and the month's attacks show why repeatedly. A product raises an alert when its rules match; activity that matched no rule raises nothing, and an investigation that reads only alerts sees only what the products were built to see.

The course uses alert tables as one source among many and spends most of its time in the event tables that the alerts summarize.

Alerts and incidents also tell an analyst about the SOC itself: how many alerts each product raises, how incidents are classified and how quickly they close. Those questions belong to a SOC operations course rather than this one, but the tables are the same, and the queries this course teaches answer them as easily as investigation questions.

Incidents and alerts also carry the products' view of severity and the analysts' classifications, which later modules use to compare what the products flagged with what the event tables show. The comparison is often instructive: the attacks a product flagged loudly and the ones it did not flag at all.

Not every machine runs the endpoint sensor, and not every event happens on a machine. Two tables cover the rest of the estate. Syslog holds messages from Linux servers: logons, scheduled jobs, service activity. CommonSecurityLog holds records from network devices in a standard format; here, the company's Palo Alto firewall.

union withsource = SourceTable Syslog, CommonSecurityLog
| summarize Rows = count(), Sources = dcount(coalesce(Computer, DeviceProduct)) by SourceTable

One query covers both tables, counting rows and sources in each. Syslog holds 5,332 messages from eight Linux servers, and CommonSecurityLog 2,095 firewall records. These tables cover what Defender's endpoint tables cannot: servers without the sensor, and traffic seen at the network edge rather than on a device. Section 10.7 showed how much an investigation loses when a machine is not covered; these tables are part of the answer.

Linux servers often run the company's web applications and databases, which makes their logs important despite their small size. Syslog records sign-ins over SSH, scheduled jobs and service activity, and its messages are free text, which is why Module 6's parsing techniques are needed to read most of them.

The firewall's records are the only view of traffic that never touched a monitored device, such as connections to servers without the sensor or blocked attempts from outside. They are structured in the Common Event Format, with named fields for source, destination, port and action.

Together with the endpoint tables, the server and network tables complete the picture of the estate: devices with sensors, servers with logs, and the network boundary where traffic enters and leaves. Where all three are present, an investigation can follow activity from the outside in; where one is missing, Section 10.7's health checks say so.

Choosing between them, or using both, is a matter of the question. The firewall says a connection was attempted and what the firewall decided; Syslog says what the server did when the connection arrived; the endpoint tables say what a device did before it made the connection. Each answers a different part.

The exercise builds the map from Section 01 yourself: every table in one union, counted and sorted.

The grader checks for 21 rows, one per table. If you got an error, one table name is misspelled; check it against the diagram, letter by letter and in the same case.

The query is long only because it names every table; its logic is one union, one summarize and one sort. Keep the result, and rerun it in any workspace you work in.

A map of tables and their sizes is the first thing to build in any workspace, and the sizes say what to expect from a query before it runs: a filter on a table of 97 rows returns at most 97, a join of two tables of fifteen thousand can return far more, as Module 10 showed.

Practice

Name the entity, name the activity, add the neighbors, and check the tables hold the period.

The next sub, Section 0.4, compares the two places security queries run: Sentinel and Defender Advanced Hunting.