Use the Microsoft Graph PowerShell SDK to Retrieve Sign in Logs
Every time a user account or process logs into a Microsoft 365 tenant, Entra ID captures the interaction in its sign-in logs. Some of the transactions flow forward into the unified audit log and can be interrogated there using the Search-UnifiedAuditLog cmdlet, the Audit search tool in the Purview portal, or the AuditLog Query Graph API.
However, the unified audit log doesn’t receive every sign-in record. Because sign-in data can be enormously helpful to administrators to understand the type and nature of connections into the tenant, including connections from potentially malicious actors, it follows that understanding how to query and analyze sign-in data is a useful skill.
Some administrators are content to browse sign-in data in the Entra admin center. This is a good way of checking something on an ad-hoc basis and the portal exposes a great deal of information about sign-in events (Figure 1). Up to one month’s sign-in data is available.
It’s easy to download sign-in data to a CSV file and use that data for different purposes. For instance, the script to report MFA status for Entra ID accounts uses sign-in data to know if user accounts use multifactor authentication when they connect to the tenant.
Using the Graph API to Access Sign-In Data
Good as it is to have the facilities included in the Entra admin center, it’s even better to be able to search for and analyze sign-in data according to your own requirements, and that’s where the Microsoft Graph comes in. Microsoft’s documentation for using Graph APIs to analyze Entra activity logs includes some useful examples to start with.
For the remainder of this article, we will use the Get-MgAuditLogSignIn cmdlet from the Microsoft Graph PowerShell SDK to retrieve sign-in records. In some cases, the Get-MgBetaAuditSignIn cmdlet is needed to fetch specific data. The beta cmdlet, which accesses the beta Graph endpoint for the List SignIns API, returns many more properties for sign-in records than the production (V1.0) cmdlet.
Retrieving account sign-in reports (logs) via Graph APIs (including SDK cmdlets) requires tenants to have Entra ID P1 licenses. Given the value of the sign-in information, it’s almost inevitable that it should come with a premium charge, but you can reduce the impact by buying just a few licenses for administrator use. If you attempt to access sign-in information using an account without an Entra ID P1 license, you’ll see the “Neither tenant is B2C or tenant doesn’t have premium license” error.
Fetching Sign-In Records for a User Account
To start, this command returns the sign-in records for the specified tenant member account over the past 30 days. We don’t need to specify a date range because the command tells Entra to fetch all available sign-in records. The AuditLog.Read.All scope is required to access sign-in records.
[array]$SignInLogs = Get-MgAuditLogSignIn -Filter "userPrincipalName eq 'brian.weakliam@office365itpros.com'" -All -PageSize 500
Even if the user principal name for an account has initial capitals for the forename and surname (as in Brian.Weakliam), use lowercase letters for the search. The filter won’t work with uppercased user principal names or those in a mixture of lower and uppercase characters. Because it’s easy to forget to lowercase user principal names, user account identifiers are a better choice to find sign-in records:
$UserId = (Get-MgUser -UserId Lotte.Vetler@office365itpros.com).Id [array]$SignInLogs = Get-MgAuditLogSignIn -Filter "userid eq '$UserId'" -All -PageSize 500
Because Microsoft Graph PowerShell SDK cmdlets cannot resolve object properties in filters, always use objects in filters. That’s why the code populates the $UserId variable with the account identifier instead of fetching all account properties to a variable like $User and referencing the identifier as $User.Id.
To find the sign-in records for a guest account, use the value of the account’s Email property instead of the value stored for the account’s user principal name. For whatever reason, Entra ID searches the sign in log using the email address of the guest:
[array]$Logs = Get-MgAuditLogSignIn -Filter "userPrincipalName eq 'pro@keepit.com'" -All
Finding sets of sign-in records is useful, but it’s even better when you can answer a question. For example, here’s how to check for the last time a user sign-in included a successful conditional access challenge.
[array]$Logs = Get-MgAuditLogSignIn -Filter "userPrincipalName eq 'brian.weakliam@office365itpros.com' and ConditionalAccessStatus eq 'success'" -Top 1
If ($Logs) {
Write-Host ("Brian Weakliam last successfully used conditional access on {0} with the {1} app" -f $Logs[0].CreatedDateTime, $Logs[0].AppDisplayName)
}
Brian Weakliam last successfully used conditional access on 22/10/2024 17:05:08 with the Office 365 Exchange Online app
Extracting Sign-In Data About Apps
The Get-MgAuditLogSignIn cmdlet can also extract information about app sign-ins. For example, to find what accounts are signing into Teams, use this command:
$AppId = (Get-MgServicePrincipal -filter "displayName eq 'Microsoft Teams'" -All).AppId [array]$AuditRecords = Get-MgAuditLogSignIn -Top 5000 -Sort "createdDateTime DESC" -Filter "AppId eq '$AppId'" -PageSize 500 $AuditRecords | Group-Object UserPrincipalName -NoElement | Sort-Object Count -Descending | Select-Object Name, Count
The output is a sorted list of accounts that have signed into Teams based on the last 5,000 events. To find the application identifier to use in the command, search sign-in events through the Entra admin center to find a matching event and copy the application identifier from the details in the event.
A variation on the theme finds the sign-in audit records for a specific user and then reports what apps the user has signed in with:
[array]$AuditRecords = Get-MgAuditLogSignIn -Top 5000 -Sort "createdDateTime DESC" -Filter "UserDisplayName eq 'Tony Redmond'" -PageSize 500 $AuditRecords | Group-Object AppDisplayName -NoElement | Sort-Object Count -Descending | Format-Table Name, Count Name Count ---- ----- Office365 Shell WCSS-Client 195 Microsoft Loop App 38 Exchange Admin Center 27 Microsoft 365 Security and Compliance Center 25
Given the large number of sign-in events generated by tenants, it’s always important to make the query as precise as possible. For example, sometimes administrators need to understand if Entra ID applied a conditional access policy during a sign-in. This snippet searches the sign-in logs for instances where a user successfully met the conditions of a conditional access policy and accessed the SharePoint Online app in the last seven days:
$StartDate = Get-Date (Get-Date).AddDays(-7) -format 'yyyy-MM-dd' $EndDate = (Get-Date -format 'yyyy-MM-dd') $PolicyId = (Get-MgIdentityConditionalAccessPolicy -Filter "displayname eq 'CA005: Require multi-factor authentication for guest access'").Id [array]$Records = Get-MgAuditLogSignIn -Filter "appliedConditionalAccessPolicies/any(x:x/id eq '$PolicyId') and createdDateTime gt $StartDate and createdDateTime lt $EndDate and ConditionalAccessStatus eq 'Success' and appDisplayName eq 'Office 365 SharePoint Online'" $Records | Format-Table UserDisplayName, CreatedDateTime UserDisplayName CreatedDateTime --------------- --------------- Jane Payne 30/10/2024 23:42:47 James Smith 30/10/2024 09:34:44
Here’s another example of being precise with the filter used to find sign-in events. Sign-in records have a status property. However, this is a compound property composed of three sub-properties to capture additional details, an error code, and a failure reason (if one occurs). To find the last successful sign-in record for a user account, we therefore must retrieve matching sign-in records and then apply a client-side filter to find successful events. This code does just that by fetching three sign-in records, which might include one or more failed events, and then selects the first successful event from the set.
$StartDate = Get-Date (Get-Date).AddDays(-14) -format 'yyyy-MM-dd'
$EndDate = (Get-Date -format 'yyyy-MM-dd')
# Retrieve the sign-in logs for the specified user
[array]$SignInRecords = Get-MgAuditLogSignIn `
-Filter "createdDateTime gt $StartDate and createdDateTime lt $EndDate and UserId eq '$UserId'" -Top 3 -Sort "createdDateTime DESC"
# Make sure that we have successful signins
$LastSignInRecord = $SignInRecords | Where-Object {$_.Status.errorcode -eq 0} | Select-Object -First 1
If ($LastSignInRecord) {
Write-Host ("Last sign-in record for {0} found at {1}" -f $LastSignInRecord.UserDisplayName, $LastSignInRecord.CreatedDateTime)
}
Last sign-in record for Tony Redmond found at 28/10/2024 23:44:16
Sign-In Event Types
So far, the examples of finding sign-in records have used the production (V1.0) endpoint to retrieve information about user sign-ins (interactive and non-interactive). However, other kinds of sign-ins occur, such as those for managed identities when Azure Automation runbooks execute. The following examples use the Get-MgBetaAuditLogSignIn cmdlet because filtering by sign-in event types is currently a beta feature and the SignInEventTypes property is unsupported by the production endpoint. In addition, the beta version of the Get-MgAuditSignIn cmdlet returns a significantly higher number of properties for sign-in records.
You can use an app’s service principal to track sign-ins by the app to different resources through the Entra admin center or via the Get-MgBetaAuditLogSignIn cmdlet. For example, this command retrieves the most recent two thousand sign-in records for service principals recorded over the last 30 days:
[array]$AuditRecords = Get-MgBetaAuditLogSignIn -Filter "(signInEventTypes/any(t:t eq 'servicePrincipal'))" -Top 2000 -PageSize 500 -Sort "createdDateTime DESC" $AuditRecords | Group-Object AppDisplayName -NoElement | Sort-Object Count -Descending | Format-Table Name, Count Name Count ---- ----- SPO Graph 14 Graph Microsoft 365 Groups Membership Report 12 Call Recorder Teams PROD 7
The sign-in event type for interactive sign-ins is “interactiveuser.”
This command finds the set of sign-in log entries for managed identities and reports the automation account as the app name:
$AuditRecords | Where-Object {$_.ServicePrincipalName -eq "ManagedIdentitiesAutomation"} | Format-Table ServicePrincipalName, CreatedDateTime, ResourceDisplayName
ServicePrincipalName CreatedDateTime ResourceDisplayName
-------------------- --------------- -------------------
Clean up Exo Mailboxes 12/10/2024 05:33:12 Microsoft Graph
Clean up Exo Mailboxes 07/10/2024 15:32:34 Microsoft Graph
Server-side filtering is always preferred because the service does the work to refine the set of audit records retrieved from the Graph. Here’s the equivalent command using server-side filtering:
[array]$AuditRecords = Get-MgBetaAuditLogSignIn -Filter "(signInEventTypes/any(t:t eq 'managedIdentity')) and ServicePrincipalName eq 'ManagedIdentitiesAutomation'"
The resource display name tells you which resource the app accessed. In this case, “Microsoft Graph” tells you that the app made a Graph API request. Other values include:
- Azure Key Vault: Fetch information (like secrets) from Azure Key Vault.
- Office 365 Exchange Online: Run Exchange Online administrator commands.
- Skype and Teams Tenant Admin API: Run Teams administrator commands.
- Windows Azure Service Management API: Azure API requests, like connecting to an Azure account.
Graph SDK Proves Its Worth Again
Entra sign-ins are an invaluable source of information for Microsoft 365 tenant administrators. By all means, use the GUI, but for serious analysis, focus on extracting and filtering sign-in records with Graph APIs, including the Get-MgAuditLogSignIn cmdlet. Understanding how query sign-in records is just another example of how useful the Graph SDK is in day-to-day administrative life.
