Script to Count and Export Users from Azure AD to CSV with Last Sign-In Information

Azure AD User Export with Sign-In Activity Script

Overview

This script connects to Microsoft Graph to retrieve comprehensive user data from Azure AD, including critical sign-in activity information. It counts total users and exports detailed properties such as UserPrincipalName, account status, user type, and last sign-in timestamps to a CSV file for security monitoring and compliance reporting.

---

Key Features

Feature	Capability	Business Value
User Enumeration	Retrieves all users in the tenant	Complete user inventory
Activity Tracking	Captures last sign-in dates and times	Identifies inactive accounts
Account Status	Reports AccountEnabled and UserType	Security posture assessment
Beta API Access	Uses Graph Beta for sign-in data	Access to advanced user properties

---

Script Requirements

Ideal for: Security administrators, compliance officers, and IT auditors

Prerequisites:

• Microsoft Graph PowerShell SDK installed
• Global Administrator or User Administrator role
• Microsoft Graph Beta module for sign-in activity
• AuditLog.Read.All permissions for activity data

---

Implementation

# Prompt the user to enter Tenant Id or Primary domain
$TenantId = Read-Host "Please enter Tenant Id or Primary domain"

# Define the scopes needed for the Microsoft Graph API permissions
$Scopes = "User.Read.All,AuditLog.Read.All,Directory.Read.All"

# Connect to Microsoft Graph
Connect-MgGraph -Scopes $Scopes

# Count users
$users = Get-MgUser -All
$userCount = $users.Count
Write-Host "Total number of users: $userCount"

# Define the output path
$OutputPath = "C:\temp\UsersExport.csv"

# Check if the directory exists
if (!(Test-Path -Path (Split-Path -Path $OutputPath -Parent))) {
    Write-Host "The directory does not exist. Please check the output path and try again."
    return
}

# Export users with specified properties
try {
    Get-MgBetaUser -All -Property "Id, UserPrincipalName, AccountEnabled, userType, SignInActivity, SignInSessionsValidFromDateTime" |
    Select-Object Id, UserPrincipalName, AccountEnabled, userType, SignInSessionsValidFromDateTime,
    @{Name="LastNonInteractiveSignInDateTime"; Expression={$_.SignInActivity.LastNonInteractiveSignInDateTime}},
    @{Name="LastSignInDateTime"; Expression={$_.SignInActivity.LastSignInDateTime}} |
    Export-Csv -Path $OutputPath -NoTypeInformation
    Write-Host "Export successful. The file is located at $OutputPath"
} catch {
    Write-Host "Failed to export users. Please check your permissions and try again."
}

---

Output Structure

The generated CSV contains the following columns:

Column	Description
Id	Unique user identifier
UserPrincipalName	User’s sign-in address (UPN)
AccountEnabled	Account status (True/False)
userType	User type (Member/Guest)
SignInSessionsValidFromDateTime	When sign-in sessions became valid
LastNonInteractiveSignInDateTime	Last non-interactive sign-in timestamp
LastSignInDateTime	Last interactive sign-in timestamp

---

Security Analysis Capabilities

Inactive Account Detection

• Identify users with no recent sign-in activity
• Flag potentially abandoned accounts
• Support account cleanup initiatives

Access Pattern Monitoring

• Track user authentication frequency
• Detect unusual sign-in patterns
• Monitor guest user activity

---

Usage Scenarios

Security Auditing

• Generate user activity reports
• Identify dormant accounts for review
• Assess overall tenant security posture

Compliance Management

• Document user access patterns
• Support regulatory audit requirements
• Maintain user lifecycle records

License Optimization

• Identify inactive licensed users
• Optimize Microsoft 365 license allocation
• Reduce unnecessary subscription costs

---

Key Takeaway: This script provides essential visibility into user activity patterns, enabling proactive security management and license optimization through comprehensive sign-in data analysis.