Azure AD Sign-In Activity Report (Via Get-MgUser)

Another post on the Azure SDK cmdlet module, this time regarding the new SignInActivity attribute.

I am regularly being asked by customers to identify inactive Office 365 accounts. I’m well past the: “lol, you’re saying you don’t know who works here?” part of this problem and just follow up with a discussion on the criteria for “inactive”. The most in-depth analysis include looking at Exchange, Teams, and SharePoint usage, but I’m finding this new beta API attribute to be real handy.

Question-1: What is SignInActivity?

Answer-1: It’s an attribute exposed through the the Microsoft Graph Beta API (not Get-Msoluser or Get-AzureADUser). The How to detect inactive user accounts topic states it as such:

You detect inactive accounts by evaluating the lastSignInDateTime property exposed by the signInActivity resource type of the Microsoft Graph API. The lastSignInDateTime property shows the last time a user made a successful interactive sign-in to Azure AD.

Question-2: Ok, super, how do I use it with PowerShell?

Answer-2a: Graph of course! Here is a script that I wrote that worked for me:

Answer-2b: If you aren’t comfortable working with Microsoft Graph directly, or if you just don’t want the hassle, you can also use the Get-MgUser cmdlet from the aforementioned SDK module.

In either case, you’ll need to decide if you’re going to run this against the built-in app: “Microsoft Graph PowerShell” (simpler), or if you want to run it as an custom app registration (less simple, but better for automation). Once you’ve made that decision, assign the permissions.

If you’re using a custom app, use the Azure portal and search the Microsoft Graph API permissions list for:

  • AuditLog.Read.All
  • Organization.Read.All

If you’re using the Get-MgUser cmdlet, you can add these ‘scopes’ with -Scopes parameter of Connect-MgGraph.

Connect-MgGraph -Scopes AuditLog.Read.All, Organization.Read.All

In both of theses cases (app & delegated), you should be prompted once, and depending on how your tenant is configured, your administrator (you?) will need to give consent to the permissions.

NOTE: You don’t need to use the -scopes parameter each time you connect. This parameter is to prompt the permission consent page, which, once approved, is then recorded with the app.

After you’ve connected to Graph, using Connect-MgGraph, you’ll need to switch to the ‘beta’ API. The SignInActivity attribute isn’t exposed to the v1.0 API yet.

Select-MgProfile beta

From here, you can start using PowerShell like you’re used to. One thing to note when pulling the SignInActivity attribute is that you must use the user’s Azure AD GUID for the -UserId parameter, even if normally UPN would have sufficed. For example:

Get-MgUser -UserId 3a927ed7-d581-40ff-96a0-f5185014f0bf -Property SignInActivity | Select-Object -ExpandProperty SignInActivity | Format-List *

LastSignInDateTime   : 10/28/2021 12:10:41 AM
LastSignInRequestId  : c344b3c5-02fc-4fb4-b46f-7fe1624f5e91
AdditionalProperties : {[lastNonInteractiveSignInDateTime, 2021-10-28T02:02:41Z], [lastNonInteractiveSignInRequestId, 5ffc8a91-85b5-421c-8201-75e9fa40797d]}

Here is a more involved example, that demonstrates SignInActivity put together with some Exchange Online attributes (as I said, I am trying to find inactive users, and these additional fields can be helpful). It will create an Excel (.xlsx) file with the following column headers:

UserPrincipalName, CreatedDateTime, DisplayName, Mail, OnPremisesImmutableId, OnPremisesDistinguishedName, OnPremisesLastSyncDateTime, SignInSessionsValidFromDateTime, RefreshTokensValidFromDateTime, Id, PrimarySmtpAddress, ExternalEmailAddress, LastSignInDateTime, lastNonInteractiveSignInDateTime

Before you run the script, you’ll need to install the following modules:

Install-Module ExchangeOnlineManagement
Install-Module ImportExcel
Install-Module Microsoft.Graph.Authentication
Install-Module Microsoft.Graph.Users

Finally, the script itself:

Connect-ExchangeOnline -UserPrincipalName <user>
Connect-MgGraph -TenantId <tenant>
Select-MgProfile beta

$MailUsers = Get-User -filter {recipienttypedetails -eq 'MailUser'} -ResultSize unlimited
$ReportUsers = $MailUsers | ForEach-Object {
    $MailUser = $_   
    $Counter ++
    $percentComplete = (($Counter / $MailUsers.count) * 100)
    Write-Progress -Activity "Getting MG Objects" -PercentComplete $percentComplete -Status "$percentComplete% Complete:"    
       #to do - organize properties better
    Get-MgUser -UserId $MailUser.ExternalDirectoryObjectId -ConsistencyLevel eventual -Property @(
    ) | Select-Object @(
        @{n='PrimarySmtpAddress'; e={$MailUser.PrimarySmtpAddress}}
        @{n='ExternalEmailAddress'; e={$MailUser.ExternalEmailAddress}}
        @{n='LastSignInDateTime'; e={[datetime]$_.SignInActivity.LastSignInDateTime}}
        @{n='lastNonInteractiveSignInDateTime'; e={[datetime]$_.SignInActivity.AdditionalProperties.lastNonInteractiveSignInDateTime}}           

$Common_ExportExcelParams = @{
    # PassThru     = $true
    BoldTopRow   = $true
    AutoSize     = $true
    AutoFilter   = $true
    FreezeTopRow = $true

$FileDate = Get-Date -Format yyyyMMddTHHmmss

$ReportUsers | Sort-Object UserPrincipalName | Export-Excel @Common_ExportExcelParams -Path ("c:\tmp\" + $filedate + "_report.xlsx") -WorksheetName report

NOTE: Future updates to this script will be on GitHub:

8 thoughts on “Azure AD Sign-In Activity Report (Via Get-MgUser)

  1. any idea on this? Write-Progress : Cannot validate argument on parameter ‘PercentComplete’. The 323 argument is greater than the maximum allowed range of 100. Supply an argument that is less than or equal to
    100 and then try the command again.
    At line:7 char:68

  2. “Connect-MgGraph: AADSTS650053: The application ‘Microsoft Graph PowerShell’ asked for scope ‘AuditLogs.Read.All’ that doesn’t exist on the resource…”

    it seems like I cannot grant this permission, although I’m an admin
    Organization.Read.All, User.Read.All, Group.ReadWrite.All permissions work fine, AuditLogs is making problems for me
    Any ideas?

  3. In this line
    Get-MgUser -UserId $MailUser.ExternalDirectoryObjectId -ConsistencyLevel eventual

    -ConsistencyLevel eventual parameter showing error.

    • Thanks for pointing this out.

      It looks like that’s in the “List” parameter set, whereas -UserId is in the “Get” parameter set – not sure if that changed recently, but I do recall reading that Microsoft would be reducing the need for the eventual consistency-level. Please just try the line without that parameter, and I’ll see if the post needs an update later.

  4. Mike that is Fantastic!!! I will integrate it.

    Just one thing… I was trying to run some Graph commands in an automation process

    When I run it as “Connect-MgGraph -Scopes AuditLog.Read.All,…….” works perfectly but a validation prompt is requiere….

    I migrated the validation using an API on AzureAD… but to collect all the details of my environment take to much time (days…) I have around 39k objects…. are there any way to process this quickly?

    Using Connect-MgGraph take “just” 6 hours….

    Thanks in advance I will appreciate it

    • Microsoft Graph throttles pretty heavily. Its unfortunate. you could try batching, trimming your request down to the bare essentials and testing with different times in the day, but otherwise I’m not aware of a good solution. You may consider sending data over to Azure Log Analytics through the tenant’s “diagnostic” configuration, so that you can query the analytics workspace – its much faster.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s