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

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.

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.

The Applications People Use

The populated list allows me to gain some insight into the applications users are signing into. For example:

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.

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:

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.


Don't have a login but want to join the conversation? Sign up for a Petri Account

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He covers Office 365 and associated technologies for and is also the lead author for the Office 365 for IT Pros eBook, updated monthly to keep pace with change in the cloud.