Analyzing Azure Active Directory Sign-In Data with PowerShell
AzureADPreview Module Gives Insight into Sign-in Data
The Azure Active Directory PowerShell module (now renamed the Azure Active Directory PowerShell for Graph module) comes in two versions. The general availability version is intended for production while the preview version (AzureADPreview) contains the cmdlets from the general availability version plus some new cmdlets under development group. The current version of the AzureADPreview module is 2.0.2.105, released in July.
The Get-AzureADAuditSiginInLogs cmdlet exposes the Azure audit sign-in data that is also available through the Azure Active Directory portal (Figure 1), where up to a month of sign-in daa can be browsed. You can download events from the portal in CSV or JSON format, and the same events are available to PowerShell.Image 1 Expand
Data downloaded to a CSV file can be opened and analyzed with Excel.
Checking the Last Sign-in for an Account
The availability of the data to PowerShell makes it possible to look at the information in a different way. For example, we can retrieve the last successful sign-in for an account by running a command like this:
PowerShell
| 12345 | Get-AzureADAuditSignInLogs -Top 1 -Filter (“UserPrincipalName eq ‘Kim.Akers@Office365itpros.com’ and status/errorCode eq 0”) | Format-Table CreatedDateTime, UserDisplayName CreatedDateTime UserDisplayName————— —————2020-07-28T13:50:39.0039859Z Kim Akers |
It’s interesting to discover last sign-in data for tenant accounts (users now have an option to review their sign-in activity), but given that guest accounts have a habit of lingering in tenants when not being used, the technique can reveal the last sign-in for guest accounts. This code asks for the name of a guest and uses it to find matching accounts. For each account, we check the sign-ins and report how long ago the sign-in was.
PowerShell
| 12345678910111213141516171819 | $Guest = Read-Host “Enter name of guest account”$Guests = Get-AzureADUser -SearchString $GuestForEach ($G in $Guests) { If ($G.UserType -eq “Guest”) { $UserLastLogonDate = $Null Try { $UserObjectId = $G.ObjectId $UserLastLogonDate = (Get-AzureADAuditSignInLogs -Top 1 -Filter “userid eq ‘$UserObjectId’ and status/errorCode eq 0”).CreatedDateTime } Catch { Write-Host “Can’t read Azure Active Directory Sign in Logs” } If ($UserLastLogonDate -ne $Null) { $LastSignInDate = Get-Date($UserLastLogonDate); $Days = New-TimeSpan($LastSignInDate) Write-Host “Guest” $G.DisplayName “last signed in on” $LastSignInDate “or” $Days.Days “days ago” } Else { Write-Host “No Azure Active Directory sign-in data available for” $G.DisplayName “(” $G.Mail “)” } }} Enter name of guest account: BrianGuest Brian Desmond last signed in on 03/08/2020 15:28 or 0 days agoNo Azure Active Directory sign-in data available for Brian Ricks (brianr@brian2.com) |
It’s also possible to retrieve sign in information for individual users with Graph API calls.
Processing Sign-in Data
Raw data about someone’s sign-ins are interesting. The data is more useful if we do a little processing before attempting any analysis. This code finds the last month’s sign-in data and populates a PowerShell list object with information extracted from the sign-in records.
PowerShell
| 12345678910111213141516171819202122232425 | # Fetches the last month’s Azure Active Directory sign-in dataCLS; $StartDate = (Get-Date).AddDays(-30); $StartDate = Get-Date($StartDate) -format yyyy-MM-dd Write-Host “Fetching data from Azure Active Directory…”$Records = Get-AzureADAuditSignInLogs -Filter “createdDateTime gt $StartDate” -all:$True $Report = [System.Collections.Generic.List[Object]]::new() ForEach ($Rec in $Records) { Switch ($Rec.Status.ErrorCode) { “0” {$Status = “Success”} default {$Status = $Rec.Status.FailureReason} } $ReportLine = [PSCustomObject] @{ TimeStamp = Get-Date($Rec.CreatedDateTime) -format g User = $Rec.UserPrincipalName Name = $Rec.UserDisplayName IPAddress = $Rec.IpAddress ClientApp = $Rec.ClientAppUsed Device = $Rec.DeviceDetail.OperatingSystem Location = $Rec.Location.City + “, ” + $Rec.Location.State + “, ” + $Rec.Location.CountryOrRegion Appname = $Rec.AppDisplayName Resource = $Rec.ResourceDisplayName Status = $Status Correlation = $Rec.CorrelationId Interactive = $Rec.IsInteractive } $Report.Add($ReportLine) } Write-Host $Report.Count “sign-in audit records processed.” |
The Applications People Use
The populated list allows me to gain some insight into the applications users are signing into. For example:
PowerShell
| 12345678910111213141516171819202122232425262728 | $Report | Group AppName | Sort Count -Descending | Format-Table Count, Name Count Name—– —- 577 Microsoft Teams Web Client 113 Microsoft Exchange REST API Based Powershell 99 Azure Active Directory PowerShell 79 Office365 Shell WCSS-Client 64 SharePoint Online Web Client Extensibility 20 Office 365 SharePoint Online 19 Microsoft Exchange Online Remote PowerShell 15 Microsoft Teams Admin Portal Service 12 Microsoft 365 Security and Compliance Center 11 Exchange Filtering Service 7 Microsoft Office 365 Portal 4 Azure Portal 3 Microsoft Stream Portal 3 Microsoft Office Web Apps Service 2 Ideas in Word Online SSO Client 2 Microsoft Teams 2 Office Online Client AAD- Loki 2 Office Online Client AAD- Augmentation Loop 1 Skype For Business Powershell Client Application 1 O365 Suite UX 1 Office 365 Reports 1 ACOM Azure Website 1 Graph explorer 1 Office 365 Exchange Online |
This data is interesting because it reveals how some Office 365 applications work. Many of the applications are instantly understandable, others are more obscure. Microsoft gives some odd names to clients, which is OK when an administrator looks at data, but is a real challenge for users when they review their sign-in data.
The data shows that Teams appears to be more heavily used than any other application, but that’s due to the way that Teams signs into many different resources when it starts up, including Exchange Online, SharePoint Online, and the Skype presence service. The new Exchange REST-based cmdlets are also heavily used, but the high number is accounted for by the way that the module reconnects to Exchange Online every so often during a session. There’s no trace of clients that might have signed on using modern authentication some time ago and are now using refresh tokens to keep connected to applications. The data is an insight into applications rather than a complete summary of workload usage across the tenant. For that, we’d need to dive into the Graph and access activity data.
Finding Where Users Sign-in From
Azure Active Directory captures a user’s location when they sign in. Here’s what I found in my tenant, which reveal a nice collection sign-ins from of different countries.
PowerShell
| 1234567891011121314151617 | $Report | Group Location|Sort Count -Descending | Format-Table Count, Name Count Name—– —- 480 Dublin, Dublin, IE 233 Ashburn, Virginia, US 134 Nijmegen, Gelderland, NL 118 Chicago, Illinois, US 46 Sofiya, Sofiya-Grad, BG 14 Togrenda, Akershus, NO 6 Washington, Virginia, US 2 Kleinpestitz/Mockritz, Sachsen, DE 2 Oxford, Oxfordshire, GB 2 Amsterdam, Noord-Holland, NL 1 North York, Ontario, CA 1 Seattle Hill-Silver Firs, Washington, US 1 Brussels, Brussels, BE |
You might be surprised at the number of locations where sign-ins to a tenant originate. A command like this will tell you who’s signing in from a location:
PowerShell
| 1 | $Report | ?{$_.Location -Like “*Sofiya*”} | Group User | Sort Count -Descending | Ft Count, Name |
No New Data – Just Better Access
The data is the data. PowerShell won’t uncover insights that you can’t get by browsing sign-in data through the Azure Active Directory portal or in Excel after downloading the sign-in data from the portal. However, because PowerShell can access sign-in data, you can now include the data in scripts should you ever need to report or analyze user sign-in activity. And that’s nice
