0.2 How Security Data Is Stored

Introduction

Every query in Section 0.1 read a table, and every answer was a table. This sub looks at what a table is made of, before the course starts asking it questions, because almost every mistake a new analyst makes with KQL comes from guessing at it.

A table holds one kind of event. Each row is one event. Each column has a name and a type that are the same in every row: text, a whole number, a true-or-false value, a date and time, or a bag of nested values. The type decides what you can do with the column.

You'll list a table's columns with getschema, which settles names and types for good. You'll count a table's columns by type, reach inside a column that holds nested data, and find out which values are missing and how a missing value shows itself. By the end, meeting a new table should take four short queries rather than a series of errors.

The diagram is the anatomy every security table shares, drawn from the sign-in table's real columns. Named columns with types across the top, one row per event below, a datetime column saying when, and at least one dynamic column holding nested details that a query reaches into.

None of this needs memorizing. The point of the sub is a short routine that answers storage questions on demand, so that whatever table an investigation needs, its names, types and gaps are a few queries away.

Tables are the starting point of every query, and they are simpler than they look. A table in KQL is a named collection of rows that all have the same columns.

Security products write one table per kind of event, and add to it continuously as events happen: sign-ins in one, process starts in another, mail in a third. Each row is one event, and each column is one fact about it, with a name and a type, and the same columns appear in every row.

SigninLogs
| getschema
| project ColumnName, ColumnType
| take 12

The tool for seeing a table's columns is getschema, written as a step after the table's name. getschema returns a table describing another table: one row per column, with the column's name and type.

On the sign-in table it shows TimeGenerated as a datetime, the account, address and application as strings, and DeviceDetail as dynamic. The names it returns are the exact names to use in queries, including their case, which is why Section 0.1 recommended it before writing anything.

The completed query below counts the columns.

30 columns in the sample sign-in table. A real workspace's sign-in table has more; the sample keeps the ones the course uses, which Section 0.6 explains.

The routine applies to every table in this course and every table in a real workspace. Microsoft documents each security table's columns too, and the documentation is worth reading for what each column means; getschema is the quicker way to confirm what a particular table actually holds, which can differ slightly between workspaces and over time.

Every column has one type, and the type decides what operations make sense on it. A column cannot hold text in one row and a number in the next; the type is fixed for the table, except that a dynamic column, described below, can hold values of any shape.

Strings can be compared and searched; numbers can be added and compared by size; datetimes can be filtered by range and subtracted to give durations; booleans are true or false; dynamic columns hold nested data that has to be reached into.

SigninLogs
| getschema
| summarize Columns = count() by ColumnType

Counting the schema's rows by type gives a quick picture of what kind of data a table holds. The sign-in table's 30 columns are 23 strings, 4 dynamic columns, 2 booleans and 1 datetime.

Most security data is text: account names, addresses, application names, codes and descriptions. Even some values that look numeric are stored as text, as the result code shows below. The one datetime, TimeGenerated, is the column almost every query filters first.

DeviceProcessEvents
| getschema
| where ColumnType in ("datetime", "long")
| project ColumnName, ColumnType

Other tables use other types. The process table adds whole numbers, of type long: process ids and other identifiers. It also has three datetime columns rather than one: Timestamp, when the event happened, and the creation times of the process and of its parent. Those extra times are what Module 9 used to tell one process from another when ids repeat.

Some values that look like numbers are stored as text, and that matters for how they are compared. The sign-in result code is a string, stored as text in every row, "0" for success, which is why queries compare it with a quoted value. getschema says which type each column really has, so the choice between ResultType == "0" and a number never needs guessing.

Booleans are worth a sentence of their own. IsInteractive and IsRisky are true or false, and a filter on them needs no comparison at all: where IsRisky keeps the rows where it is true, 23 sign-ins in the sample month. The type makes the query shorter and its meaning obvious.

Types also decide how values sort. Strings sort alphabetically, so "10" comes before "9"; numbers and datetimes sort by value. A sort on a code stored as text can therefore look wrong without being a bug, and converting with toint before sorting gives the numeric order.

Not every fact fits neatly into one plain column. A dynamic column can hold a whole structure in one cell: a property bag of named values, an array of items, or a mixture. Security tables use them for details that vary from event to event, such as location, device and authentication steps.

SigninLogs
| summarize SignIns = count() by City = tostring(LocationDetails.city)
| top 4 by SignIns desc

LocationDetails holds each sign-in's country, state and city, as a small bag of named values written in JSON. A dot reaches inside it, LocationDetails.city, naming the property wanted, and tostring turns the value into ordinary text that can be grouped and compared. Manchester, where the company is based, has 7,486 sign-ins; London, Frankfurt and Stockholm follow far behind.

SigninLogs
| summarize SignIns = count() by OS = tostring(DeviceDetail.operatingSystem)
| top 4 by SignIns desc

DeviceDetail holds the device's details, its identifier, name, operating system and whether it is compliant with policy, and here the counts show something else: 5,787 sign-ins have no operating system recorded at all, and 2,416 say Windows10.

Nested data is often incomplete, because the source only fills in what it knows about each device. An empty value is itself information, and a filter that assumes every row has one will quietly drop most of the table.

The exercise below reaches into the bag for one property.

One row per city. Without tostring, the engine refuses to group: the value taken from the bag is still dynamic, and a group key needs a plain type. The conversion is the step that turns nested data into something a query can group, sort and join. Module 6 covers dynamic data in depth, including arrays and the operators that expand them.

Arrays are the other common shape in dynamic columns, and security data uses them often for anything that comes in steps or lists. AuthenticationDetails, for example, holds a list of the steps each sign-in went through, one entry per step.

Reading arrays takes operators that turn each entry into its own row, which Module 6 introduces; for now, it is enough to recognize that a dynamic column may hold a list rather than a single bag.

Across the sub, tables turned out to be simple and exact, and every question about them was answered by a short query. Rows are events, columns have one name and one type, getschema lists both, and dynamic columns hold nested details that need a dot and a conversion. Missing values were common, in nested data especially.

The record collects how security data is stored, six facts that later modules rely on without restating.

The first four rows are the vocabulary; the last two are where most mistakes happen. The fifth row lists the types this course uses. timespan, a duration, appears whenever two datetimes are subtracted, and real, a number with a fractional part, whenever a calculation divides.

The first three rungs take a minute together. The fourth rung is the one most often skipped. A filter on a column that is usually empty returns a small result for a reason that has nothing to do with the question, and counting the gaps first prevents the confusion.

The first mistake raises an error when grouping, and the second hides most of a table. The third mistake is subtle because the query runs. A missing string is usually empty rather than null, so isnull finds none of them; isempty, which Microsoft documents as true for an empty string or a null, finds both.

Before moving on, it is worth noticing how much the four queries in this sub's middle sections revealed with no knowledge of any attack: the shape of the sign-in table, where the company's people sign in from, how incomplete device details are, and which columns are usually missing. Every investigation starts with that kind of knowledge, whether or not anyone writes it down.

dynamic columns also explain why some queries in later modules look longer than their questions. Every value taken from a bag needs its conversion, tostring, toint or todatetime, before it can be grouped or compared with confidence, and the conversions are part of writing the query correctly rather than clutter.

A value can be missing in two ways in KQL. A string with nothing in it is usually empty: it exists, and has no characters. A value of another type with nothing in it is null: there is no value at all. isnull is true only for null; isempty, as Microsoft documents it, is true for an empty string or a null, so it catches both.

DeviceProcessEvents
| summarize Rows = count(), NoParentTime = countif(isnull(InitiatingProcessCreationTime))

The process table shows how common missing values can be. It records each parent process's creation time, but 7,883 of its 9,576 rows have none: the value is null.

Module 9 depended on that column, and on knowing that it was missing in most rows, to build process trees correctly. A query that sorts, groups or joins on a column that is usually null behaves very differently from one on a complete column.

DeviceNetworkEvents
| summarize NullUrl = countif(isnull(RemoteUrl)), EmptyUrl = countif(isempty(RemoteUrl))

The network table shows the difference directly, with both tests in one summarize. 1,799 connections have no URL, and isempty finds all of them; isnull finds none, because the missing URLs are stored as empty strings, not as nulls. A query that used isnull to find connections without a URL would report that there are none, which is exactly wrong.

Nulls also behave differently in comparisons. A null compared with a value with == is not true, and with != is true, which can surprise: a filter written to exclude one value keeps the nulls too. Module 1 covers them with examples; the habit to form now is to count a column's missing values before relying on it.

Missing values are not errors in the data, and should not be cleaned away without some careful thought first. A sign-in from a browser with no device registration has no device details to record; a connection to an address has no URL.

The absence is a fact about the event, and treating it as one, counting it, filtering on it deliberately, is what separates a careful query from a lucky one.

Time is the column every investigation filters first, so it is worth seeing how a table's times are stored before using them. The earliest and latest sign-in, and the difference between them, take one summarize.

SigninLogs
| summarize Earliest = min(TimeGenerated), Latest = max(TimeGenerated)
| extend Span = Latest - Earliest

The sample month runs from early on 13 February to just before noon on 15 March, a little over 30 days, and the difference of the two datetimes is a timespan, a duration that can be compared and summed like a number.

Every security table has at least one datetime column saying when the event happened, and every investigation filters on it. Sentinel's tables mostly name it TimeGenerated; Defender's tables name it Timestamp. The values are in UTC, coordinated universal time, whatever time zone the analyst or the company is in.

Some tables carry more than one time, as the process table did, and the difference matters: the time of the event, and the times of things it refers to. Choosing the right time column is part of asking the right question, and getschema shows which time columns a table offers.

A filter on the event time asks when the event happened; a filter on a process's creation time asks when that process started, which can be days earlier for a process left running, such as a window a user opened the morning before.

Times are compared with datetime values or with ago(), the time a given duration before now, and subtracted to give a timespan, as the course does from Module 1 onward. Because they are stored as their own type, none of this needs parsing text: a datetime is ready to compare, sort and bin as it is.

A datetime displayed in a result may look different from how it was written in a query, depending on the tool; the stored value is the same. Writing times in queries in the ISO form, year, month, day and then the time, as datetime(2026-03-02 22:00), avoids any ambiguity about day and month order.

Durations are written with a number and a unit: 1d for a day, 2h for two hours, 30m for thirty minutes, 10s for ten seconds. ago(7d) is a datetime seven days before now; a filter comparing TimeGenerated with it keeps the last week. The same units appear in bins and windows throughout the course.

A type is not a detail of storage; it decides which questions a column can answer, and how they must be written. The result code shows it plainly.

SigninLogs
| where ResultType > 0
| count

Almost every surprise in KQL traces back to storage. Here the result code, which looks like a number, is a string, and asking for codes greater than zero is a comparison the engine refuses.

A comparison with the wrong type, a name with the wrong case, a filter on a column that is usually empty, a property left inside a bag: each runs, or fails, for reasons that getschema and a few counts would have shown in advance.

That is why the course starts each new table with the same short routine and returns to it whenever a result is surprising. It is also why the tables a security analyst queries, the subject of the next sub, Section 0.3's subject, are introduced with their columns and their gaps rather than just their names.

A table is only useful once you know what its columns hold, and how often they hold nothing.

Types also shape what queries can do efficiently, which Module 10 measured. Section 10.1 explains that filters on stored datetime columns let the engine skip data, and Section 10.3 that string columns are indexed by term. Neither makes sense without knowing that a column is a datetime or a string, which is the knowledge this sub builds.

The routine is also a defense against data that changes over time. A connector update can add, rename or retype a column, and a query written against last year's schema can start returning wrong answers without any error. getschema, run occasionally on the tables a team's detections depend on, catches that early.

Getting types right early pays off later in the course, where queries combine many columns from several tables. A join between two tables needs their keys to have the same type; a calculation needs numbers rather than text; a time window needs datetimes. Each of those is a storage question first.

The exercise profiles the network table in one query: its size, how many connections have no URL, and how many have no remote port.

The grader checks for 1,799 connections with an empty URL, the count Section 05 found. If you got zero, check that the test is isempty on RemoteUrl, which is a string; if you got an error, check the case of the column names against getschema.

The query counts three things in one summarize, each with its own condition, which is the profile pattern used throughout the course. Read the result as the kind of profile worth running on any table before using it, and keeping alongside the queries that depend on the table.

5,995 connections, 1,799 of them without a URL, which is ordinary for connections made to an address rather than a named site, and none without a port. A URL filter written without this knowledge would silently ignore almost a third of the table.

Practice

Count the table, list its schema, look at a few rows, and check its gaps.

The next sub, Section 0.3, introduces the tables a security analyst queries, and what each one records.