How to export the user details with last login information via PowerShell script from Entra ID (Not MsGraph)

Question

Please Help me to get this details.

We are looking to export the few user information with last login and On-premises sync enabled details from the Microsoft Entra ID via Powershell script. Please find the sample below which are looking to export via the powershell script.

Kindly help us to share the right powershell script to get these details

Thank you.

Answer 1

Hello @Prince Chauhan,

Thank you for posting your query on Microsoft Q&A.

I understand that you want to export few user information like userPrincipalName, displayName, surname, mail, userType, accountEnabled, creationType, LastSigninDate and On-Premises sync enabled through PowerShell script.

Please follow the step by step process as mentioned below.

1.Open PowerShell as an Administrator.

2.Install Microsoft Graph Module. Run the following command to install the Microsoft.Graph module.

Install-Module Microsoft.Graph

3.Import Microsoft Graph Module by running the below PowerShell command.

Import-Module Microsoft.Graph

4.Authenticate with Microsoft Graph

You need to authenticate your session with the appropriate permissions. The command below will prompt you to log in and grant the necessary permissions (you’ll need Directory.Read.All and AuditLog.Read.All scopes):

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

A sign-in window will appear where you need to enter your Microsoft Entra ID credentials. Please ensure you have the required permissions to access this data.

5.Run the Command to Export User Data

The following command retrieves user details (like userPrincipalName, displayName, surname, mail, userType, accountEnabled, creationType, LastSigninDate and On-Premises sync enabled) and exports it to a CSV file located at C:\EntraUserDetails.csv

$Users = Get-MgUser -All -Property 'UserPrincipalName','DisplayName','Surname','Mail','CreationType','UserType','OnPremisesSyncEnabled','SignInActivity'

$FilteredUsers = $Users | Select-Object `

@{Name='UserPrincipalName'; Expression={$_.UserPrincipalName}},

@{Name='DisplayName'; Expression={$_.DisplayName}},

@{Name='Surname'; Expression={$_.Surname}},

@{Name='Email'; Expression={$_.Mail}},

@{Name='CreationType'; Expression={$_.CreationType}},

@{Name='UserType'; Expression={$_.UserType}},

@{Name='LastSignInDate'; Expression={$_.SignInActivity.LastSignInDateTime}},

@{Name='OnPremisesSyncEnabled'; Expression={$_.OnPremisesSyncEnabled}}

$FilteredUsers | Export-Csv -Path "C:\EntraUserDetails.csv" -NoTypeInformation -Encoding UTF8

6.Check the Output

After the script runs, the CSV file will be saved at C:\EntraUserDetails.csv. You can open this file in Excel or any other CSV viewer to check the exported data.

I hope this above information provided is helpful. Please feel free to reach out if you have any further questions.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hello Prince Chauhan,

I understand you are trying to export the user details with last login information via PowerShell script from Entra ID.

To fetch user's last login details, you need to use Get-AzureADAuditSignInLogs command that won't work in AzureAD module.

You need to uninstall your current module version with below commands:

Disconnect-AzureAD

Uninstall-Module AzureAD

Now, install AzureADPreview module and run below PowerShell command to list it:

Install-Module AzureADPreview

Get-Module -ListAvailable AzureAD*

You can make use of below PowerShell script to export the specific user's details with last login information:

Connect-AzureAD

$specifiedUsers = @("******@xxxxxxxxx.onmicrosoft.com", "sridevi.xxxxxx1_outlook.com#EXT#@xxxxxxxx.onmicrosoft.com", "******@xxxxxxxxxx.onmicrosoft.com")

$userDetails = @()

foreach ($upn in $specifiedUsers) {

    $user = Get-AzureADUser -Filter "UserPrincipalName eq '$upn'" | 

            Select-Object ObjectId, DisplayName, UserPrincipalName, Surname, Mail, UserType, AccountEnabled, CreationType, DirSyncEnabled

    if ($user) {

        $lastSignIn = (Get-AzureADAuditSignInLogs -Filter "UserId eq '$($user.ObjectId)'" | 

                       Sort-Object CreatedDateTime -Descending | 

                       Select-Object -First 1).CreatedDateTime

        $userDetails += [PSCustomObject]@{

            userPrincipalName   = $user.UserPrincipalName

            displayName         = $user.DisplayName

            surname            = if ($user.Surname) { $user.Surname } else { "N/A" }

            mail               = if ($user.Mail) { $user.Mail } else { "N/A" }

            userType           = if ($user.UserType) { $user.UserType } else { "N/A" }

            accountEnabled     = if ($user.AccountEnabled -ne $null) { $user.AccountEnabled } else { "N/A" }

            creationType       = if ($user.CreationType) { $user.CreationType } else { "N/A" }

            OnPremSyncEnabled  = if ($user.DirSyncEnabled -eq $true) { "True" } else { "False" }

            LastLoginDate      = if ($lastSignIn) { $lastSignIn } else { "No sign-in data" }

        }

    } else {

        Write-Host "User $upn not found in Azure AD."

    }

}

$userDetails | Export-Csv -Path "C:\EntraID_UserLogs.csv" -NoTypeInformation

Write-Host "User details exported to C:\EntraID_UserLogs.csv"

To confirm that, I checked the CSV file that have all the required details as below:

Hope this helps!

---

Please do not forget to click "Accept the answer” and Yes wherever the information provided helps you, this can be beneficial to other community members.

If you have any other questions or still running into more issues, let me know in the "comments" and I would be happy to help you.