Analyzing Azure Active Directory Sign-In Data with PowerShell

AzureADPreview Module Gives Insight into Sign-in Data

The Azure Active Directory (recently renamed Microsoft Entra ID) 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, 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
Azure AD Signins
Figure 1: Azure AD sign-ins (image credit: Tony Redmond)

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:

Get-AzureADAuditSignInLogs -Top 1 -Filter ("UserPrincipalName eq '[email protected]' 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.

$Guest = Read-Host "Enter name of guest account"
$Guests = Get-AzureADUser -SearchString $Guest
ForEach ($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: Brian
Guest Brian Desmond last signed in on 03/08/2020 15:28 or 0 days ago
No Azure Active Directory sign-in data available for Brian Ricks ([email protected])

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.

# Fetches the last month's Azure Active Directory sign-in data
CLS; $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:

$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.

$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:

$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.

Related article: