KQL Sign in Logs - razmipatel/Random GitHub Wiki

🔍 1. Basic Sign-In Activity for a Specific UPN SigninLogs | where UserPrincipalName == "[email protected]" // Replace with actual UPN | project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, ResourceDisplayName, AuthenticationRequirement, ConditionalAccessStatus, Status, Location, DeviceDetail | order by TimeGenerated desc

🔐 2. Filter for Failed Sign-Ins Only SigninLogs | where UserPrincipalName == "[email protected]" | where Status.errorCode != 0 | project TimeGenerated, UserPrincipalName, Status, IPAddress, AppDisplayName, FailureReason | order by TimeGenerated desc

📍 3. Sign-Ins from a Specific Country SigninLogs | where UserPrincipalName == "[email protected]" | where Location.countryOrRegion == "United Kingdom" // Replace as needed | project TimeGenerated, IPAddress, Location, AppDisplayName, Status | order by TimeGenerated desc

🧠 4. Sign-In Methods and MFA Requirement SigninLogs | where UserPrincipalName == "[email protected]" | project TimeGenerated, AuthenticationMethodsUsed, AuthenticationRequirement, MFARequired = AuthenticationDetails[0].authenticationStepResultDetail, Status | order by TimeGenerated desc

⏱️ 5. Last Sign-In SigninLogs | where UserPrincipalName == "[email protected]" | summarize LastSignIn = max(TimeGenerated)

✅ Tips SigninLogs is the standard Entra ID table for interactive sign-ins.

Ensure your Log Analytics workspace is connected to Entra ID sign-in logs (via Diagnostic Settings or Microsoft Entra ID > Monitoring).

Use filters like AppDisplayName to target specific services (e.g., "Microsoft Teams" or "Office 365 Exchange Online").

Let me know if you want to correlate sign-ins with Conditional Access policy decisions, risky sign-ins, or integrate these into workbook visualizations.