Export Microsoft 365 Users’ Sign-in Report Using PowerShell

Tracking user activities in your organization’s Microsoft 365 environment isn’t just about security and compliance—it’s about staying one step ahead of potential threats. As an IT administrator, staying vigilant means regularly monitoring user sign-ins. Understanding Entra ID sign-in logs is crucial; they provide detailed insights into who accesses what and when, helping you spot anomalies that could indicate suspicious activity.

Let’s dive into the methods available to access Entra ID sign-in log activity details.

The following are the available methods to check user sign-in logs in Microsoft Entra ID.

Microsoft Entra Admin Center: Sign in to the Microsoft Entra admin center and navigate to Identity –> Monitoring & Health –> Sign-in logs. Here you can find the list of sign-in events of users in your organization. However, the sign-in logs in the Entra admin center are limited to the past 30 days.

Unified Audit Logs: Navigating through the Unified Audit Log using ‘Search-UnifiedAuditLog’ provides access to Microsoft 365 users’ login history from the last 180 days. However, it will provide limited details and won’t provide info about whether it’s interactive or non-interactive, MFA details, sign-in risk level, etc.

PowerShell: You can use the Get-MgAuditLogSignin cmdlet in MS Graph PowerShell to retrieve Entra ID sign-in logs with PowerShell. However, distinguishing between successful and failed login attempts can pose a challenge. Also, it can only retrieve data from the last 30 days.

To address this, we’ve developed a PowerShell script that allows you to schedule and store sign-in data for a longer period. It also exports detailed sign-in activities of Microsoft 365 users. This includes information such as sign-in date, location, device name, browser, operating system, authentication details, etc.

Download Script: GetEntraSigninLogs.ps1

• Exports all Entra ID sign-in logs in user-friendly format.
• Allows you to find out successful and failed sign-in attempts separately.
• Filters interactive/non-interactive user sign-ins.
• Helps export risky sign-ins alone.
• Tracks guest users’ sign-in history.
• Segments sign-in attempts based on Conditional Access applied & not applied.
• Helps to monitor CA policy sign-in failures & success separately.
• You can export the report to choose either ‘All users’ or ‘Specific user(s)’.
• The script uses MS Graph PowerShell and installs MS Graph Beta PowerShell SDK (if not installed already) upon your confirmation.
• The script can be executed with an MFA-enabled account too.
• Exports report results as a CSV file.
• The script is scheduler friendly.
• It can be executed with certificate-based authentication (CBA) too.

The script exports Azure AD sign-in logs with the following attributes.

• Sign-in Date
• Username
• UPN
• Status
• IP Address
• Location
• Device Name
• Browser
• Operating System
• User Type
• Authentication Requirement
• Risk Detail
• Risk State
• Conditional Access Status
• Applied Conditional Access Policies
• Interactive/Non-Interactive Sign-in

The exported report on Microsoft Entra ID sign-in logs looks like the screenshot below.

1. Download the script.
2. Start the Windows PowerShell.
3. Select any of the methods provided to execute the script.

Method 1: You can run the script with MFA and non-MFA accounts.

The example provided enables you to export Entra sign-in logs into a CSV file for the last 30 days. In addition, it’s essential to retrieve each user’s last logon time across the Microsoft 365 environment. This helps identify inactive users within the organization.

Method 2: You also have the option to run the script using certificate-based authentication, which is scheduler-friendly. When you want to run the script unattended, you can choose this method.
To use certificates, you must register the app in Microsoft Entra and connect to MS Graph using certificate.

Note: Depending on your requirements, you can create a self-signed certificate. Before employing certificate-based authentication, it is crucial to register an application in Azure AD.

The script features predefined filters to meet your specific needs, excelling in these scenarios:

Note: The script retrieves sign-in data using the Get-MgBetaAuditLogSignin cmdlet and then filter the log based on the parameters you provide.

• List users with risky sign-ins in Microsoft 365
• Export Microsoft 365 guest user sign-in report
• Export interactive sign-ins in MS Entra ID
• Export Azure AD non-interactive sign-ins
• View Entra users’ successful sign-in attempts
• Identify Microsoft 365 users’ failed sign-in attempts
• Retrieve CA policies applied sign-ins
• Retrieve sign-in attempts without CA policy
• Track successful CA policy enforcement in Entra sign-in logs
• Track conditional access sign-in failures in Entra
• Get login history for a specific user
• Export Microsoft 365 sign-in details for a list of users

Risky sign-ins could indicate potential threats, such as compromised accounts or malicious activities. By monitoring and analyzing these sign-ins, you can take proactive measures to protect your organization and respond swiftly to security incidents. Using the script with –RiskySignInsOnly parameter, you can generate a report that lists all users with risky sign-ins, enabling you to address vulnerabilities promptly.

This script generates a report of all users with risky sign-ins.

By keeping track of when and where guest users access your Microsoft 365 resources, you can identify unusual activity, enforce policies, and improve overall compliance. By using the – GuestUserSignInsOnly parameter, you can generate a comprehensive report that details all sign-in activities by guest users.

This script generates a report of sign-in activities for all external users in your Microsoft 365 environment. You can also use the last login time to identify inactive guest users.

Focusing on interactive sign-ins helps you understand user-initiated activities, providing insights into how users engage with your Microsoft Entra ID environment. To export interactive sign-ins, execute the script with – InteractiveOnly switch param.

This script generates a report of interactive sign-ins in Microsoft Entra ID.

Non-interactive sign-ins are sign-ins performed by a client app or an OS component on a user’s behalf. Understanding access patterns allows you to gain insights into how applications and services interact within Azure AD. Run the script with the –NonInteractiveOnly switch parameter.

The generated result produces a report of non-interactive user sign-ins in Azure AD.

Real-time monitoring of successful sign-ins is crucial for swiftly verifying legitimate access and detecting unauthorized activities. Run the script with –Success parameter to list users with successful Entra ID sign-ins.

This script provides a report of successful Entra ID sign-in activities for users.

Note: To track an individual user’s last sign-in time using PowerShell, use the ‘Get-EntraUser’ cmdlet.

Monitoring failed sign-in attempts in your Microsoft 365 environment is crucial for several reasons. It helps analyze recurring patterns that may indicate systematic issues or targeted attacks, troubleshoot authentication problems promptly, and detect potential security threats. To get a list of failed Entra ID sign-ins, you can run the script with the –Failure parameter.

This result has a report of unsuccessful Entra ID sign-in attempts for users in Microsoft 365

By identifying sign-in attempts with applied Conditional Access policies, organizations can enforce granular access controls based on user context and device compliance. To retrieve a list of sign-in attempts with applied Conditional Access policies, execute the script with the -CAPAppliedOnly parameter.

The result captures both successful and failed Conditional Access login attempts.

Identifying sign-ins without applied Conditional Access policies allows administrators to review and enforce necessary access controls, reducing the risk of unauthorized access. To retrieve a list of sign-ins without Conditional Access policies, execute the script with the –CAPNotAppliedOnly parameter.

The result shows all sign-ins without Conditional Access enforcement.

If you are particularly looking to monitor all CA policies in your organization, you can utilize this PS script to export Conditional Access policies to Excel.

By monitoring successful policy enforcement during sign-ins, you can verify effective security measures and ensure that access controls function correctly. To export a list of successful sign-ins using CA policies, execute the script with the -CAPSuccessOnly parameter:

This command generates a report listing all successful sign-ins based on Conditional Access policies.

Tracking Conditional Access policy failures during sign-ins addresses vulnerabilities, such as policy misconfigurations or unauthorized access attempts. To export a list of sign-ins which failed due to a CA policy, run the script with the –CAPFailedOnly parameter.

This command identifies sign-ins that failed due to a conditional access policy. There are also methods to find which Conditional Access policy is blocking a user’s sign-in.

Monitoring the sign-in activities of individual users helps detect unauthorized access attempts or suspicious activities associated with a specific user account. To export a specific user’s sign-in history, execute the script with the –UserPrincipalName param.

The exported report contains sign-in activity details of Alex.

If you want to get an Office 365 sign-in report for multiple users, you can pass usernames using the –UserPrincipalName param as comma-separated values.

The exported report contains the sign-in activity details of Alex and Lidia.

While PowerShell offers granular control, it requires some scripting knowledge. However, for those seeking a more straightforward approach, AdminDroid offers a compelling alternative. This free tool combines user-friendly interfaces with robust reporting capabilities, making it ideal for administrators looking to streamline their operations.

Explore AdminDroid’s Free Microsoft 365 reporting tool, boasting over 120 pre-built reports tailored to manage users, groups, licenses, and more. Among its extensive features are 50+ sign-in reports that cover various facets of user activity:

User Logins

• All user logins
• Failed user logins
• Successful user login
• User’s last logon time
• Users’ first logon time of a day
• Users’ monthly login count summary

Security

• Admins logins
• Guest logins
• Risky login attempts
• Failed to pass MFA challenge
• Legacy/Basic authentication login attempts

Office 365 service-based logins

• Outlook login history
• Mailbox PowerShell logins
• Microsoft Teams logins
• External user login activities in MS Teams

Sign-in Types

• Interactive sign-ins
• Non-interactive sign-ins
• All successful MFA sign-ins and more…

In addition, with over 1800 reports and more than 30 dashboards, AdminDroid covers Exchange Online, SharePoint Online, Microsoft Teams, OneDrive for Business, and more. Start your optimized management journey today with AdminDroid. Try our 15-day free trial and experience the difference firsthand!

Download the AdminDroid M365 management tool now and experience the power of simplified administration!

We hope that this blog has provided you with detailed information on tracking M365 users’ sign-in details using PowerShell. If you are looking for further ways to track user sign-ins, you can use methods like scenario monitoring, Entra workbook, etc. Thanks for reading! For further queries, reach out to us in the comments section.