View and Count Failed Office 365 Sign-In Attempts by User
Question details
Identify failed Office 365 sign-in attempts and count failures for each user.

- Product
- Microsoft 365 and Microsoft Entra ID
- Device & OS
- not provided
- Scenario
- An administrator wants to review failed sign-ins and summarize the number of failures by user, preferably without a Premium license.
- Observed behavior
- The user needs a list of failed sign-in events and a per-user count.
Sign in to the Microsoft Entra admin center with a role that can read sign-in logs, such as Reports Reader. Choose the date range and sign-in type you want to review, and check your tenant's log retention and licensing limits.
Export failed Microsoft Entra sign-ins and count them by user
Use the sign-in logs to review failures, then summarize the exported events in a spreadsheet.
Basic interactive sign-in logs are available in Microsoft Entra ID Free, subject to retention limits. The originally suggested report may require a Premium license; availability of other report features can also depend on your license.
The spreadsheet count represents logged sign-in events, which may not always equal the number of password attempts.
Go to the Microsoft Entra admin center at entra.microsoft.com and open Identity > Monitoring & health > Sign-in logs.
Select the Interactive user sign-ins tab, set the date range, and use Add filters to set Status to Failure. Add a user filter if you want to inspect one account.
Select Download and choose CSV to export the filtered sign-in events. Open the downloaded file in Excel or another spreadsheet application.
Select the exported data in Excel and choose Insert > PivotTable. Add the user principal name or equivalent user column to Rows, then add the same column to Values and set its calculation to Count.
Return to Sign-in logs and select an event to inspect details such as its failure reason, time, IP address, and location.

Review exported sign-in data with WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility for an easier move from Excel. Use WPS Spreadsheets to review exported sign-in data; Microsoft Entra is still required to access the logs.

Frequently Asked Questions
Do I need Microsoft Entra ID Premium to view failed sign-ins?
Basic interactive sign-in logs are available in Microsoft Entra ID Free. Premium may be required for specific reports or features, and retention differs by license.
Where can I find the reason a sign-in failed?
In the Microsoft Entra admin center, open Identity > Monitoring & health > Sign-in logs, filter Status to Failure, and select an event to view its details.
How do I count failures for each user?
Filter the sign-in log to failures, select Download > CSV, and open the file in Excel. Use Insert > PivotTable, place the user column in Rows, and count it in Values.
Why can't I see older failed sign-ins?
The events may be outside your tenant's retention period. Microsoft Entra ID Free generally retains sign-in logs for seven days; check your license and whether logs were previously exported to another service.




