Extracting the Last Sign-In Date in Azure (Entra ID) with Microsoft Graph PowerShell

As a Cloud Engineer, I’m always looking for ways to automate processes and streamline the management of our Azure environment. Recently, a customer approached me with a challenge: they needed to extract the last sign-in date for every user in their tenant. Although the Entra ID portal provides this information, the customer required it in a CSV format so that their security team could review the data, collaborate with user managers, and determine if certain accounts were still needed.

The Challenge

The customer needed a consolidated CSV report that contained each user’s:

• Display Name
• User Principal Name
• Email Address
• Last Sign-In Date
• Manager Information (which includes the manager’s display name and email)

Although the Entra ID portal displays sign-in data, it has its limitations:

Data Extraction vs. Portal View

While the Entra ID portal provides sign-in data, having it in CSV format enables deeper analysis and easier sharing among teams. This approach consolidates all the required information into one single report. Currently, the portal doesn’t allow you to add a manager column in the view, meaning that extracting manager details would otherwise be an extremely manual process.

My Approach

To solve this challenge, I leveraged the Microsoft Graph PowerShell module. Here’s a breakdown of the process:

1. Connect to Microsoft Graph:
   I used the Connect-MgGraph cmdlet with the necessary scopes (User.Read.All, Directory.Read.All, and AuditLog.Read.All) to ensure access to all the required user data.

2. Retrieve Users with Sign-In Data:
   Using Get-MgUser, I queried all users in the tenant and requested properties such as DisplayName, UserPrincipalName, Mail, and SignInActivity. Although SignInActivity isn’t directly expandable, including it in the -Property parameter was sufficient to retrieve the data, provided the user has signed in.

3. Extract and Format the Last Sign-In Date:
   For each user, I extracted the LastSuccessfulSignInDateTime from the SignInActivity property and added it as a custom property to the user object.

4. Export the Data to CSV:
   Finally, I exported the consolidated data to a CSV file. This format was essential for the security team, as it enabled them to share the report with managers and decide if inactive accounts should be decommissioned.

The Script

Below is the final PowerShell script I developed:

# Connect to Microsoft Graph with the required scopes.
Connect-MgGraph -Scopes "User.Read.All", "Directory.Read.All", "AuditLog.Read.All"

# Define the properties to retrieve for each user.
$Properties = @(
    ‘DisplayName’,
    ‘UserPrincipalName’,
    ‘Mail’,
    ‘SignInActivity’
)

# Retrieve all users with the specified properties.
$AllUsers = Get-MgUser -All -Property $Properties

# Prepare an array to hold our results.
$results = @()

foreach ($user in $AllUsers) {
    # Extract the last successful sign-in date (this might be null if the user has never signed in successfully).
    $LastLoginDate = $user.SignInActivity.LastSuccessfulSignInDateTime

    # Initialize manager details.
    $managerDisplayName = ""
    $managerEmail = ""

    try {
        # Get the user’s manager as a directory object.
        $managerObj = Get-MgUserManager -UserId $user.Id -ErrorAction Stop

        # Now retrieve the full details of the manager.
        $managerDetails = Get-MgUser -UserId $managerObj.Id -Property "DisplayName,Mail,UserPrincipalName"
        $managerDisplayName = $managerDetails.DisplayName
        $managerEmail = $managerDetails.Mail

        # If Mail is not populated, use UserPrincipalName as a fallback.
        if (-not $managerEmail) {
            $managerEmail = $managerDetails.UserPrincipalName
        }
    }
    catch {
        # If the user does not have a manager, these values remain empty.
        $managerDisplayName = ""
        $managerEmail = ""
    }

    # Build a custom object with the desired details.
    $results += [PSCustomObject]@{
        DisplayName         = $user.DisplayName
        UserPrincipalName   = $user.UserPrincipalName
        Mail                = $user.Mail
        ManagerDisplayName  = $managerDisplayName
        ManagerEmail        = $managerEmail
        LastLoginDate       = $LastLoginDate
    }
}

# Display the results in a formatted table.
$results | Format-Table -AutoSize

# Export the results to a CSV file.
$results | Export-Csv -Path "UsersLastSuccessfulSignInData.csv" -NoTypeInformation

Write-Output "Export complete. Data saved to UsersLastSuccessfulSignInData.csv"

The Outcome

This script successfully extracted the last sign-in date for each user and exported the data to a CSV file named UsersLastSuccessfulSignInData.csv. With this report in hand, the customer’s security team can now work directly with user managers to review account activity and make informed decisions about which accounts may be decommissioned.

Lessons Learned

• Data Extraction vs. Portal View:
  While the Entra ID portal provides sign-in data, having it in CSV format enables deeper analysis and easier sharing among teams. This approach consolidates all the required information into one report. Currently, the portal doesn’t allow you to add a manager column in the view, meaning that extracting this data manually would be extremely time-consuming.

• Automation is Key:
  Automating the extraction of this data not only saves time but also reduces the risk of human error. It ensures that the security team has accurate, up-to-date information, empowering them to make informed decisions.

• Leveraging Microsoft Graph:
  The Microsoft Graph PowerShell module is a powerful tool that enables you to extract and process data efficiently from your Azure tenant.

If you’re facing a similar challenge or have questions about automating data extraction with Microsoft Graph PowerShell, feel free to leave a comment or reach out. Happy cloud engineering!