Find Microsoft 365 SMTP AUTH senders with Microsoft Graph, OpenAI and Google Sheets

Quick overview

This workflow runs weekly to identify Microsoft 365 accounts sending via SMTP AUTH (Basic auth) using Microsoft Graph reports and sign-in logs, generates an OpenAI-based remediation plan for risky accounts, writes the inventory to Google Sheets, and sends a status digest to Telegram.

How it works

1. Runs every Monday at 08:00 (or manually in test mode with built-in sample data).
2. Retrieves the Microsoft Graph email app usage user detail report (CSV) and Entra ID sign-in events filtered to the “Authenticated SMTP” client app.
3. Parses the usage report and aggregates sign-ins per account to capture counts, failures, last seen time, and sending IP addresses.
4. Combines both sources to produce one record per account with a verdict (BREAKS end of Dec 2026, CHECK, or No SMTP use seen) and supporting reasoning.
5. For accounts that need action, sends minimal account facts to OpenAI to return a structured device guess, a single recommended fix path, short steps, and a risk rating.
6. Appends or updates all account rows in Google Sheets and, while any accounts need action, sends a weekly digest summary to Telegram.

Setup

1. Register a Microsoft Entra app with Microsoft Graph application permissions Reports.Read.All and AuditLog.Read.All, grant admin consent, and create a client secret.
2. Create an n8n OAuth2 API credential (client credentials) for Microsoft Graph and select it in both Microsoft Graph HTTP requests.
3. Create a Google Sheet (for example a tab named “SMTP AUTH inventory”) with an account column for matching plus the workflow’s inventory columns, then set the Google Sheets document URL/ID.
4. Add an OpenAI API credential for the chat model used to generate fix plans.
5. Create a Telegram bot, set the chat ID, and update the Telegram node configuration to receive the weekly digest.

Requirements

• Microsoft 365 admin consent for an Entra app (Reports.Read.All, AuditLog.Read.All), Google Sheets, an OpenAI API key and a Telegram bot. Sign-in logs need Entra ID P1 or P2; without them the workflow continues with the usage report only.

Customization

• Change the schedule, report period or sign-in days in Set Configuration Settings, turn on alert_only_changes to get the digest only when accounts change, or send the digest to Slack or Teams instead of Telegram.