Reporting on Entra ID sign-ins and risk with Microsoft Graph

The sign-in log is the most useful data set in Microsoft Entra ID and the portal is not a good way to analyse it. Filtering interactively is fine for one investigation; answering “which applications still use legacy authentication, and who uses them” needs a query.

This article covers the queries I reach for most, and the specific pitfalls that make a first attempt return the wrong answer.

Requirements

Permissions. AuditLog.Read.All for the sign-in and directory audit logs, IdentityRiskyUser.Read.All for risk data. Both are delegated scopes and both are confirmed by Find-MgGraphCommand, as described in Microsoft Graph PowerShell: authentication and scopes.

A directory role as well. Reading activity logs requires an appropriate role — Security Reader or Reports Reader at minimum. Holding the scope without the role produces an error about the user not being in an allowed role, which reads like a permission problem and is actually a role problem. This catches people out constantly.

Licensing. Sign-in log retention in Entra ID depends on your licence, and risk detections require Entra ID P2. A query that returns nothing may be telling you about your licence rather than about your tenant.

The data model: what a sign-in record contains

Worth knowing before querying, because it determines what you can ask.

• Who and when — user, time.
• What — the application and the resource.
• From where — IP address, and a location derived from it.
• How — client application, which is the field that identifies legacy authentication, and the authentication protocol.
• The result — a status with an error code. Code 0 is success; everything else is specific.
• Conditional Access — which policies were evaluated and what each concluded.
• Risk — risk level and detail, where licensed.

One structural point that shapes every query: the sign-in log is large, and filtering must happen server-side. Retrieving everything and filtering in PowerShell is slow, and against a busy tenant it will not complete in a reasonable time. Use -Filter, which passes an OData filter to Graph.

The queries

Connect

Connect-MgGraph -Scopes 'AuditLog.Read.All', 'IdentityRiskyUser.Read.All' -NoWelcome
Get-MgContext | Select-Object Account, Scopes

Failed sign-ins, grouped by error code

The most useful first query against an unfamiliar tenant.

$since = (Get-Date).AddDays(-7).ToString('yyyy-MM-ddTHH:mm:ssZ')

$failures = Get-MgAuditLogSignIn -All `
    -Filter "createdDateTime ge $since and status/errorCode ne 0"

$failures |
    Group-Object { $_.Status.ErrorCode } |
    Sort-Object Count -Descending |
    Select-Object @{ Name = 'ErrorCode';   Expression = { $_.Name } },
                  @{ Name = 'Count';       Expression = { $_.Count } },
                  @{ Name = 'Description'; Expression = { $_.Group[0].Status.FailureReason } },
                  @{ Name = 'Users';       Expression = { ($_.Group.UserPrincipalName | Sort-Object -Unique).Count } }

The Users column is what distinguishes the patterns. One error code with a high count and one user is somebody with a stale credential on a phone. The same count spread across hundreds of users is a password spray.

Legacy authentication, by application

This is the query to run before blocking legacy authentication, and it is the one that makes that project possible. The point is covered in Conditional Access baselines — you cannot block what you have not inventoried.

$legacyClients = @(
    'Exchange ActiveSync', 'IMAP4', 'POP3', 'SMTP', 'MAPI Over HTTP',
    'Exchange Web Services', 'Other clients', 'Authenticated SMTP',
    'Exchange Online PowerShell', 'Autodiscover'
)

Get-MgAuditLogSignIn -All -Filter "createdDateTime ge $since" |
    Where-Object { $_.ClientAppUsed -in $legacyClients } |
    Group-Object ClientAppUsed, AppDisplayName |
    Sort-Object Count -Descending |
    Select-Object @{ Name = 'ClientAndApp'; Expression = { $_.Name } },
                  Count,
                  @{ Name = 'DistinctUsers'; Expression = { ($_.Group.UserPrincipalName | Sort-Object -Unique).Count } },
                  @{ Name = 'SampleUser';    Expression = { $_.Group[0].UserPrincipalName } }

Every row is something that will break when you block legacy authentication. Work through them before enabling the policy, not afterwards.

Sign-ins from unexpected locations

Get-MgAuditLogSignIn -All -Filter "createdDateTime ge $since and status/errorCode eq 0" |
    Group-Object { $_.Location.CountryOrRegion } |
    Sort-Object Count -Descending |
    Select-Object @{ Name = 'Country'; Expression = { $_.Name } },
                  Count,
                  @{ Name = 'Users'; Expression = { ($_.Group.UserPrincipalName | Sort-Object -Unique).Count } }

Interpret this carefully. A location is derived from an IP address and is wrong often enough to matter — a VPN, a mobile network, or a cloud service will produce a country nobody visited. Treat it as a prompt to look, not as evidence of anything.

Risky users

Get-MgRiskyUser -All -Filter "riskLevel eq 'high' or riskLevel eq 'medium'" |
    Select-Object UserPrincipalName, RiskLevel, RiskState, RiskDetail,
                  RiskLastUpdatedDateTime |
    Sort-Object RiskLevel, RiskLastUpdatedDateTime -Descending

RiskState matters as much as RiskLevel. A user at high risk whose state is atRisk needs attention now; one showing as remediated or dismissed has already been handled, and mixing the two produces a report that overstates the problem.

Sign-ins by privileged accounts

Worth running regularly. Combine directory role membership with sign-in data and look at where privileged accounts are actually authenticating from — the evidence that tests whether the separation in administrative tiering holds in practice.

Pitfalls that produce wrong answers

Forgetting -All. You get the first page. On a sign-in log that is a small and unrepresentative sample, and nothing tells you it was truncated.

Filtering client-side. Retrieving a week of sign-ins from a large tenant and filtering in PowerShell will be slow enough to look broken. Push the filter into -Filter.

Date format. The filter needs ISO 8601 in UTC. A local-format date silently returns nothing useful.

Assuming coverage. Interactive and non-interactive sign-ins are recorded differently, and service principal sign-ins are a separate collection. A query over interactive sign-ins alone misses a large proportion of what happens in a tenant.

Assuming retention. If your licence retains less than the period you queried, the absence of older data is a licensing fact.

Extension

The natural next step is scheduling: run the failure and legacy authentication queries weekly, export to CSV, and compare against the previous run. Trends are more informative than snapshots, and a new application appearing in the legacy authentication report is a specific question with a specific owner.

For continuous monitoring rather than periodic reporting, this data belongs in your security platform — the connectors discussed in Defender XDR and Sentinel bring sign-in logs in with alerting on top, which is a better answer than a scheduled script for anything you want to be told about rather than to go and look at.

Handling the output

Sign-in data is personal data. It records where individuals were, what they used and when. Store the exports with restricted access, agree retention, and be aware that in many jurisdictions this data carries obligations. Do not attach it to a ticket.

Verification and limits

Get-MgAuditLogSignIn requiring AuditLog.Read.All or Directory.Read.All, Get-MgRiskyUser requiring IdentityRiskyUser.Read.All, the directory role requirement for activity log access, and the sign-in record fields used above were checked against current Microsoft documentation on 20 September 2026.

The queries were not executed against a tenant for this article. All are read-only. Retention depends on licensing and risk data requires Entra ID P2, so an empty result may reflect your licence rather than your tenant — confirm both before drawing conclusions. Location data is derived from IP address and is unreliable as evidence.

References

• Get-MgAuditLogSignIn
• Get-MgRiskyUser
• How to analyse activity logs with Microsoft Graph
• Microsoft Graph PowerShell SDK and Microsoft Entra ID Protection