Set Up Automated Microsoft 365 User Sign-in Summary Email Using PowerShell

Every organization’s ultimate goal is to keep its Microsoft 365 environment secure. Among the many security measures, monitoring user sign-ins keeps you stay one step ahead of potential attacks. For example, repeated occurrence of sign-in failures may indicate attempts to compromise user accounts or result from Conditional Access policies blocking sign-ins.

While the Microsoft Entra admin center allows exporting sign-in logs in CSV or JSON format, these reports often contain raw data that can be overwhelming to analyze. As a result, you may spend significant time sifting through details to identify critical events.

To simplify this process, we’ve developed a PowerShell script that automates daily Microsoft 365 user sign-in summary email delivery. The script generates an HTML report that highlights key metrics and a detailed CSV report. This helps you to quickly identify unusual sign-in activity.

The HTML report generated by script will provide the counts of following activities for overview.

• Total user sign-ins
• Successful and failed sign-ins
• Sign-ins blocked by Conditional access (CA) policies
• Sign-ins granted by CA policies
• Successful MFA and non-MFA sign-ins
• Successful and failed external user sign-ins

In addition, this report provides detailed insights for easy access, which includes:

• Failed sign-in users: Displays the user principal names along with the number of failed sign-in attempts.
• Sign-ins blocked by CA policies: Lists users whose sign-ins were blocked by CA policies, along with counts and timestamps.
• External user sign-in details: Shows external user’s successful and failed sign-ins, their last sign-in time, and sign-in status.
• Successful sign-ins using single-factor authentication: Tracks users signing in without MFA along with their counts and last sign-in timestamp.
• Sign-ins granted by CA policies: Displays users whose sign-ins were granted by CA policies along with their sign-in count and last sign-in time.

With these insights, you can quickly spot suspicious patterns and potential security issues without spending hours manually analyzing data.

Here’s a sample view of the report to show how the data is presented:

For a more detailed overview, the CSV report provides extensive insights that includes:

Sign-in date, username, sign-in ID, 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 and is interactive.

Download Script: UserSigninInsights.ps1

Script Highlights

• Automatically sends daily Microsoft 365 user sign-in summary email to specified recipients.
• Automatically installs the Microsoft Graph PowerShell module (if not installed already) upon your confirmation.
• Exports a detailed user sign-in report to a CSV file and generates an HTML report for a clear summary of user sign-ins.
• The script can be executed with MFA and non-MFA accounts.
• Supports certificate-based authentication (CBA) too.
• The script is scheduler-friendly.

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

Method 1: Execute the script with MFA or non-MFA accounts.

This sends the daily user sign-in summary email to Admin and Alex with the CSV file and HTML report attached in the name of Support.

Method 2: Execute the script using certificate-based authentication.

To run the script unattended, you can register an app in Microsoft Entra that helps you connect to Graph using certificate. Depending on your needs, you can use a certificate authority (CA) or create a self-signed certificate.

While registering app, make sure to grant the following Microsoft Graph API application permissions.

• AuditLog.Read.All
• Directory.Read.All
• Policy.Read.ConditionalAccess
• Mail.Send

The above format sends the daily user sign-in summary email to Alex and Ajay in the name of Jack.

Once the app has been registered, it’s time to create a scheduled task that run the script every morning, automatically retrieving user sign-in data and sending email notification to the specified recipients.

Follow the steps below to automate the execution of the PowerShell script with Task Scheduler:

• Open the Task Scheduler and click the Create Task. Enter a name and add description.
• Choose Run whether the user is logged on or not to run the script non-interactively. This executes the task in the background without requiring the user login.
  • Enable Do not store password. The task will only have access to local computer resource to limit its permissions.
• Check Run with high privileges to elevate the task’s priority.

• Then, switch to the Triggers tab to define the conditions when the task should run and click New.
  • In the New Trigger page, set the trigger frequency to Daily, choose the Start date and time, and set it to Recur every 1 day.
  • Finally, check the Enabled box, and click OK to save.

• Now, switch to the Actions tab and click New to execute the script.
  • In the Program/Script textbox, you can use the following path to locate to the PowerShell.
    
    C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe

    1	C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe

  • To ensure the PowerShell script runs unattended, you need to add the required arguments in the Add arguments field. Here’s an example:
    
    -ExecutionPolicy Bypass -File "<FolderPath>\UserSigninInsights.ps1" -UserSigninInsights.ps1 -Recipients "<RecipientEmail>" -HideSummaryAtEnd -TenantId <TenantID> -ClientId <ClientID> -CertificateThumbprint <CertificateThumbprint> -FromAddress "<FromAddress>"

    1	-ExecutionPolicy Bypass -File "<FolderPath>\UserSigninInsights.ps1" -UserSigninInsights.ps1 -Recipients "<RecipientEmail>" -HideSummaryAtEnd -TenantId <TenantID> -ClientId <ClientID> -CertificateThumbprint <CertificateThumbprint> -FromAddress "<FromAddress>"

  • Replace <FolderPath> with the actual path to your script to directly navigate to the correct directory. Here is the breakdown for the specified parameters:

• Next, you can optionally configure conditions and make additional changes under the Settings tab.
• If no further changes are needed, simply click OK to complete the task scheduling process.

Note: To run the script continuously, the system must remain powered on and connected to the internet. Alternatively, you can use Azure Automation that allows the script to run reliably without needing the system to be always on or online.

Now that the script is scheduled and the script runs daily to send the user sign-in details. Below is a sample email of Microsoft 365 sign-in insights.

The email includes both the CSV and HTML reports, making it easy to review and maintain records of user sign-in events.

We hope this blog has provided you with valuable insights on setting up email to receive Microsoft 365 user sign-in summary report. Feel free to drop your questions or share your thoughts about the script in the comments section below. We’re happy to help!