The Four Entra ID Tables That Hold Identity Signal

Introduction

Identity signal in Entra ID is split across four tables and one of them holds the events almost every detection is written against. You will finish this section able to say which table answers which question, why the one everybody queries is the smallest, and what a rule aimed at the wrong one returns.

Entra ID does not have a sign-in log. It has four, and they were separated for reasons that made sense to whoever designed the schema and that trap almost everybody who queries them for the first time.

SigninLogs holds interactive sign-ins: a person authenticating, entering a credential, completing a challenge. It is the table the portal opens on, the table every tutorial uses, and in a typical tenant it is a small fraction of the authentication that actually happens.

AADNonInteractiveUserSignInLogs holds everything a client does on that person's behalf afterwards. A token refreshing, a desktop application reauthenticating silently, a mobile client waking up. Same user, same account, no human involved, and an order of magnitude more rows.

AADServicePrincipalSignInLogs holds authentication by identities with no person attached at all: applications, managed identities, service principals running scheduled work. Nothing in this table has a user, which means nothing in it can be found by a query that joins on one.

AuditLogs is different in kind from the other three. It does not record authentication. It records change: a role assigned, a policy modified, a credential added, a consent granted. Most identity persistence is a change rather than a sign-in, which is why a great deal of this course's detection work happens here.

One more thing about the split before moving on, because it explains why so many people are caught by it. The portal presents these as tabs under a single Sign-in logs blade, which makes them look like filtered views of one dataset. They are not. They are four separate tables with separate schemas, separate ingestion switches and separate costs, and the tab interface is the only place in the product where they appear unified.

The scenario at the top is that failure in its simplest form. The contractor never signed in interactively again because they never needed to. An application they had consented to held a refresh token and kept using it, which lands in the non-interactive table, and the analyst was looking at the interactive one.

The proportions are the part people find hardest to believe until they run the query themselves, so here they are for a real estate rather than as a claim.

Three things follow from that shape and each one changes how you read a detection estate.

The first is that any percentage quoted about identity security needs its denominator checked. A tenant reporting that 94 per cent of sign-ins were protected by MFA is describing the narrow bar on the left, because MFA is an interactive control and cannot apply anywhere else. That figure is true and it says nothing about 93 per cent of the authentication happening in the tenant.

The second is that ingestion decisions are made on the largest table first, which is the one holding the evidence of continued access. A workspace review looking for savings finds the non-interactive table before it finds anything else, and switching it off produces the largest single reduction in cost available. It also removes the only record of an attacker who is refreshing rather than signing in.

The third is that the service principal table is substantial, is usually ingested, and in most estates is queried by nothing at all. Northgate sends 486,700 rows a month to a table no analytics rule reads.

That shape is not unusual and it is not a Northgate quirk. A user with Outlook, Teams and OneDrive open generates non-interactive events every few minutes across three clients, all day, whether or not they touch anything. They sign in interactively once.

The consequence for detection is direct. An attacker holding a stolen session token never appears in SigninLogs again after the theft, because they are not signing in, they are refreshing. Everything they do lands in the largest table, which is also the one most likely to have been switched off for cost, for the reasons section 0.1 walked through.

Knowing what a table holds is the easy half. The half that decides whether a rule works is knowing what it structurally cannot contain, because that is where a query returns an empty result and an analyst reads it as good news.

SigninLogs cannot contain a service principal. There is no user, and the table's schema is built around one, so no amount of filtering finds a workload identity here. A rule counting failed sign-ins per account will never see a compromised application no matter how badly it is behaving.

AADNonInteractiveUserSignInLogs cannot tell you that a human did anything. Every row in it is a client acting on a token that already exists, so it is evidence of continued access rather than of a decision. A spike here at three in the morning is a synchronization job as often as an attacker.

AADServicePrincipalSignInLogs cannot tell you what the application then did. It records authentication, so you learn that a service principal obtained a token and from where, and nothing at all about which mailboxes it read afterwards. That lives in the workload's own telemetry.

AuditLogs cannot tell you whether a change was legitimate. It records that a credential was added, by whom and when, and per section 0.5 that record is identical whether an administrator rotated a secret on schedule or an attacker established persistence.

There is a general shape to those four absences worth naming. Each table records one kind of event well and is silent about the surrounding context: SigninLogs knows about people and not about applications, the non-interactive table knows about continuation and not about intent, the service principal table knows about authentication and not about action, and AuditLogs knows about change and not about authorization.

Detection work in this course is largely the business of putting two of those silences next to each other so they cover for one another. A credential added in AuditLogs means little; a credential added in AuditLogs followed by a service principal authenticating from a new address means a great deal, and neither table can say that alone.

You will meet each of those absences again as a specific problem: the service principal gap in id01, the intent gap in id04, and the change-versus-authorization gap in id03. They are introduced here together so that the pattern is recognizable before the individual cases arrive.

Which of the four a tenant holds is a diagnostic settings decision, and it is checked in the portal or with Graph rather than by querying the tables themselves. A query against an empty table and a query against a table nothing is written to return the same thing.

The portal answers it for one setting at a time. Graph answers it for all of them at once, which is the version worth keeping.

Connect-MgGraph -Scopes "Directory.Read.All"

Get-MgBetaEntraDiagnosticSetting |
  ForEach-Object { $_.Logs } |
  Select-Object Category, Enabled |
  Sort-Object Category

# Category                       Enabled
# --------                       -------
# AuditLogs                         True
# NonInteractiveUserSignInLogs     False
# ServicePrincipalSignInLogs        True
# SignInLogs                        True

Two details in that output are worth knowing before you run it. The command reads the diagnostic settings on the Entra tenant itself rather than on the Log Analytics workspace, which is the direction people usually get backwards: the workspace receives whatever the tenant is configured to send, so the tenant is where the decision lives. And a tenant can have more than one diagnostic setting, each sending a different combination of categories to a different destination, so a category can be enabled in one setting and absent from another.

That second point produces a specific confusion worth anticipating. A tenant sending non-interactive logs to a storage account for retention and not to the workspace will show the category as enabled, and no rule will ever query it, because Sentinel reads the workspace. Enabled is not the same as reaching the place your detections run.

One false in that output is four dead rules in the estate from section 0.1, and this is the only place the estate will tell you so. Nothing in Sentinel, nothing in the rule editor and nothing in a health dashboard reports it, because from the rule's point of view executing successfully against an empty table is a normal Tuesday.

The clearest way to hold this is to look at a single account across all four sources on the same day, which is what the artifact below does. It is the contractor from the scenario, and each table has a different answer to the question of whether they still have access.

Notice which table produced the finding and how ordinary it looks in isolation. No single row in that set is alarming. The interactive table's silence reads as an account nobody uses; the non-interactive rows read as a client that has not been uninstalled; the audit entry is eleven months old and was legitimate at the time. It is only the combination that says a departed contractor's consent is still serving tokens to an application that is still reading a SharePoint site.

Work through the four groups in order and the shape of the reasoning becomes visible. The interactive table gives you a last date and nothing since, which is the only evidence the analyst in the scenario had. The non-interactive table gives you continued token activity attributed to the same account, which on its own reads as a client somebody forgot to sign out. The service principal table gives you the application authenticating on its own behalf, at intervals, from an address that has not changed in months. And the audit entry gives you the consent that authorized all of it, dated eleven months earlier and granted by the contractor themselves while they still worked there.

None of those four is a detection on its own. Together they are a finding, and assembling them is the work this course teaches.

That is the ordinary condition of identity detection and it is why section 0.5 exists. Most of the time no individual record is the evidence, and the rule has to assemble one.

Before writing any rule in this course, the first decision is the table, and it is decided by the question rather than by familiarity.

If the question is did a person authenticate, it is SigninLogs, and that includes anything about MFA, Conditional Access evaluation or the method used, because those are properties of an interactive challenge.

If the question is does this access still continue, it is AADNonInteractiveUserSignInLogs. Session persistence, token replay and the long tail of a compromise after the initial theft all live there.

If the question is is a non-human identity behaving differently, it is AADServicePrincipalSignInLogs, and per section 0.6 that population is larger than the human one in most tenants and is watched by almost nobody.

If the question is did something change, it is AuditLogs, and most identity persistence is a change. Roles, policies, credentials, consent and group membership are all here and none of them is a sign-in.

// Did this person authenticate? Interactive only.
SigninLogs
| where TimeGenerated > ago(90d)
| where UserPrincipalName == "j.mercer@northgateeng.com"
| summarize interactive = count(), last = max(TimeGenerated)

// Does their access still continue? Everything after the fact.
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(90d)
| where UserPrincipalName == "j.mercer@northgateeng.com"
| summarize silent = count(), last = max(TimeGenerated) by AppDisplayName
| sort by silent desc

Run those two against a departed user and the second frequently returns rows the first does not. Both are three lines and neither is difficult; the difficulty was deciding which one the question needed.

Two questions do not resolve to a single table and it is worth recognizing them early, because they are the ones that produce the best detections. Did access continue after we thought we removed it needs the audit record of the removal and the non-interactive activity afterwards, which is a join. Is this identity behaving as it did last month needs a baseline built from one table and compared against another period of the same one, which is a different shape again and the subject of a full sub in id04.

Both of those are harder than a single-table rule and both are worth more, because a single-table rule can usually be evaded by moving to a different table, and a rule spanning two cannot.

Twenty minutes, and the answer changes what any later module can do in your estate.

The next section takes the word detection, which has been used loosely up to this point, and says what one actually consists of.