Export Microsoft 365 Inactive User Report using MS Graph PowerShell

Microsoft 365 admins often check the last logon time to generate inactive users report. This report plays a crucial role in effectively managing user accounts, optimizing licenses, enhancing security, and meeting compliance requirements. So, let’s see how to check inactive users in the Microsoft 365 organization.

Since the ‘Get-MsolUser‘ or ‘Get-AzureADUser‘ cmdlets don’t provide users’ last sign-in time information, admins often resort to different approaches to identify inactive users.

1. Widely used method:
   It involves using the ‘Get-MailboxStatistics‘ cmdlet to retrieve inactive Exchange mailboxes. However, it’s often mentioned that the last logon time obtained from ‘Get-MailboxStatistics‘ is inaccurate.
2. Microsoft 365 admin center:
   Check the ‘Last sign-in’ attribute available in the Microsoft 365 admin center under ‘Active Users’. But it requires manually clicking on each user to view their last sign-in date.
3. Azure AD sign-in logs:
   Azure AD sign-in logs and Get-AzureADAuditSignInLogs provide users’ login details, but they are limited to the last 30 days.
4. Unified Audit Log:
   The ‘Search-UnifiedAuditLog’ provides Microsoft 365 users’ logon history for up to 90 days. However, it involves processing a large volume of audit records, making it a tedious task.

Do you find yourself worrying about the easiest and most accurate way to retrieve the last sign-in date? Fortunately, there is a solution.

With the help of the Get-MgUser cmdlet, you can retrieve the last logon time of Microsoft 365 users.

The above cmdlet will retrieve the last login details of a user. However, this process requires additional processing to calculate inactive days, which can be time-consuming.

To alleviate the burden on Office 365 administrators, I have developed a PowerShell script that generates 10+ Inactive User Reports with real-time use cases. So, let’s dive in and explore this efficient solution!

Download Script: GetM365InactiveUserReport

1. The single script allows you to generate 10+ different inactive user reports.
2. The script can be executed with an MFA-enabled account too.
3. The script supports Certificate-based authentication (CBA).
4. Provides details about non-interactive sign-ins too.
5. You can generate reports based on inactive days.
6. Helps to filter never logged-in users alone.
7. Generates report for sign-in enabled users alone.
8. Supports filteringlicensed users alone.
9. Gets inactive external users report.
10. Export results to CSV file.
11. The assigned licenses column will show you the user-friendly-name like ‘Office 365 Enterprise E3’ rather than ‘ENTERPRISEPACK’.
12. Automatically installs the MS Graph PowerShell module (if not installed already) upon your confirmation.
13. The script is scheduler friendly.

The exported Office 365 last login report aka inactive users report contains the following attributes.

• User Principal Name
• User Creation Date and Time
• Last Interactive Sign-in Time
• Inactive Days based on Interactive Sign-ins
• Last Non-interactive Sign-in Time
• Inactive Days based on Non-interactive Sign-in Date
• Refresh Token Valid From
• Employee Id
• Assigned License Details
• Account Status (i.e., Sign-in Status)
• Department
• Job Title

The exported Microsoft 365 inactive user report looks similar to the screenshot below.

Note: By accessing the audit log, you can track the creators of user accounts that have never been logged in to identify and remove unused accounts.

You can choose any of the below methods based on your requirement.
Method1: Export Inactive User Report using Admin Account:
You can choose this method when you want to generate the last logon time report using admin account. It supports both MFA and non-MFA accounts.

Method2: Schedule Microsoft 365 Inactive User Report using Certificates:

When you want to run the script unattended, you can choose this method. To use certificates, you must register the app in Azure AD and connect to MS Graph using certificate.

You can use either a CA certificate or create a self-signed SSL certificate. Most admins prefer self-signed certificates for internal use.

The script supports built-in filters to generate 10+ Office 365 last logon reports. You can combine multiple filters together to get more granular reports. I have listed a few major use cases below.

• List all Azure AD users with their last logon date and time.
• Get inactive user reports based on inactive days (For e.g., inactive users for 90 days)
• Find inactive users based on non-interactive sign-in days
• Get a list of inactive users with licenses assigned
• View the last login date for sign-in enabled users
• Identify never logged in users in Microsoft 365
• Check the last sign-in date for external users
• Find Office 365 licensed users in a disabled state
• Get sign-in blocked external users
• Get inactive users with a specific license in Microsoft 365
• Scheduler inactive user report

To list all Azure AD users and their last sign-in activity, run the script as follows.

Using this last logon report, admins can identify inactive users in their organization and remove their licenses or delete them based on their organization’s policy. For easy understanding of inactive users for the past days with visual representations, you can go with Microsoft Entra workbooks.

Often admin wonders how many days since the user logged in to Office 365. If you are one of them, this would be helpful to you. To retrieve inactive users based on their inactive days, execute the script by passing the number of inactive days in the –InactiveDays parameter.

The exported report contains a list of users who have been inactive for 90 days. Additionally, you have the flexibility to customize the number of days to generate an inactive user report, whether it’s for 30 days, 180 days, or any other specified period.

Non-interactive sign-ins don’t require a user interaction or authentication factor. Instead, the device or client app uses a token or code to authenticate or access a resource on behalf of a user. Non-interactive logins are often used for automated tasks, backend processes that don’t require user involvement.
When identifying inactive users, it is advisable to take into account their non-interactive sign-ins as well.

To accomplish this, execute the script with the -InactiveDays_NonInteractive parameter followed by the desired number of inactive days. For example:

Alternatively, you can combine the -InactiveDays parameter and run the script to obtain Azure AD inactive users, including both interactive and non-interactive sign-ins:

The resulting report will display inactive users who have not performed any logins, including non-interactive sign-ins.

By identifying licensed inactive users, you can reclaim those licenses and allocate them to active users or avoid unnecessary license costs. Most admins prefer this technique for M365 license optimization.

To get inactive users with licenses, run the PowerShell script with –LicensedUsersOnly switch param.

The exported report contains all the licensed users and their inactive days. You can also combine the –InactiveDays param to obtain a list of licensed users who have been inactive for a specified number of days. This allows for more targeted analysis and the identification of users based on their activity status.

The report lists licensed users who haven’t had any recent sign-in activity within the past 180 days, allowing admins to identify and potentially remove licenses as needed.

Most organizations disable user accounts as part of the employee offboarding process to prevent them from signing into their Microsoft 365 account. As a result, analyzing the last login time of disabled users is not necessary. To address this, we have implemented a solution. By using the -EnabledUsersOnly switch parameter, you can export and analyze the last sign-in date of only the enabled users.

To utilize this feature, simply run the script with the following format:

The above format will provide the last sign-in activity of all the enabled users, allowing you to focus on the relevant data without including sign-in disabled user accounts.

Note: In case you want to ensure the offboarded user no longer logs in, you can make use of this user-friendly script to track Microsoft 365 offboarded user activities.

In Office 365, never logged-in users are created when accounts are provisioned in advance for new employees or when multiple accounts are created without proper employee onboarding procedures. These never logged-in user accounts can pose significant security risks, as many of them may have default or weak passwords.

Therefore, it is crucial to identify and address these accounts to ensure the overall security of the Microsoft 365 organization. To find Azure Active Directory users who have never logged in, run the script with -ReturnNeverLoggedInUsers switch param.

Note: Consider the account creation date when determining never logged-in users.

Stale external accounts can pose a security risk and become targets for unauthorized access or account compromise. Additionally, monitoring external users’ last sign-in activity helps identify any suspicious or unauthorized access attempts.

To view external users and their last sign-in details, execute the script with -ExternalUsersOnly switch param.

This report helps to clean up stale accounts. If you need to narrow down the list of inactive external users based on a specific number of days, you can include the -InactiveDays parameter while executing the script.

This inactive external user report contains the guest accounts who have not logged in for the last 180 days. By reviewing this report, you can disable or delete inactive external users based on your organization’s policy.

Admins disable user accounts when an employee leaves the organization, or there’s a temporary account suspension, or for administrative reasons. In a disabled state, the user is unable to sign in and utilize the licensed services associated with their account.

Identifying Office 365 licensed users in a disabled state is important for managing user accounts, license allocations, and overall security. To find disabled users with active license assignments, run the script as follows.

The exported report displays disabled users with active licenses. This report can be utilized to revoke licenses from inactive users.

Sometimes, admins may choose to disable external user accounts when their collaboration with the organization comes to an end. Therefore, it is crucial to review the status of these external accounts for better external user management. Admins have the option to delete disabled external user accounts based on organizational policies and data retention requirements.

To view disabled external users, run the script as follows.

You can also include the –LicensedUsersOnly parameter to filter out licensed external users and remove licenses from them.

Getting a list of Office 365 inactive users with a specific license can be beneficial for optimizing license allocation and cost management. By identifying users who are not actively utilizing their assigned licenses, administrators can consider switching their plan from a high-cost license to a lower-cost one or removing their license altogether.

For example, to find inactive users with an E3 license, open the generated report in Excel and filter the column “License Detail” with ‘Microsoft 365 EnterpriseE3’. This will show a list of users who have an E3 license assigned.

Admins can leverage the Task Scheduler to execute the PowerShell script as a scheduled task, utilizing certificate-based authentication for seamless execution without requiring user interaction. This enables automated and regular generation of the ‘inactive users report’.

Administrators often need to identify inactive users based on service usages, such as Exchange Online, SharePoint, or Teams. They also consider app usage, specifically targeting users who don’t utilize desktop applications. This level of granularity allows for efficient license management and cost reduction. However, gathering this information through native methods can be challenging and time-consuming. AdminDroid Microsoft 365 reporting tool comes to the rescue by providing comprehensive reports that enable administrators to easily identify inactive users by services and app usage.

AdminDroid provides 40+ reports to determine users’ inactivity. It includes,

• • Exchange inactive users
    • By last mail sent, read, and received time
    • By last activity time
    • By last logon time
  • SharePoint inactive users
    • By last file accessed & sync time
    • By last internal & external file sharing
    • By last page accessed time
    • By last SharePoint activity time
  • Teams inactive users
    • By last Team chat & private message sent
    • By last call & meeting activity
    • By last Teams activity
  • OneDrive inactive users
    • By last file accessed & sync time
    • By last internal & external file sharing
    • By last page accessed time
  • Yammer inactive users
    • By last like received
    • By last post posted & read time
    • By last Yammer activity time
  • Inactive user overview reports
    • Inactive users report for each O365 service
    • Last logon time report based on city, state, county, browser, device OS, etc.
    • Inactive mailbox based on the mailbox app usage (Outlook for Mac/Windows, OWA, Outlook for mobile, etc)

AdminDroid introduces the Inactive User ReportBoard, a centralized solution (40+ inactive users reports) that streamlines the monitoring of user inactivity across all Microsoft 365 services.

AdminDroid provides comprehensive details about Microsoft 365 usage and adoption, enabling improved resource efficiency and effective management of inactive users.

In addition, AdminDroid provides a wide range of features, including 1800+ pre-built reports and 30+ Office 365 dashboards. These reports provide comprehensive insights, covering areas such as reporting, auditing, analytics, usage statistics, security, and compliance.

Besides, AdminDroid offers a free Azure AD reporting tool that includes 120+ reports & dashboards covering various categories. These reports come with features such as exporting, sending reports via email, and scheduling.

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

I hope this blog will help you in managing inactive users efficiently. If you have any queries, you can reach us through the comment section.