Query Details
Teams AAD Signins Success Unsuccess
Query
//Tracking Teams sign-ins for successful/unsuccesful logins
let timeFrame = 1d;
let logonDiff = 10m;
SigninLogs
| where TimeGenerated >= ago(timeFrame)
| where ResultType == "0"
| where AppDisplayName startswith "Microsoft Teams"
| project SuccessLogonTime = TimeGenerated, UserPrincipalName, SuccessIPAddress = IPAddress, AppDisplayName, SuccessIPBlock = strcat(split(IPAddress, ".")[0], ".", split(IPAddress, ".")[1])
| join kind= inner (
SigninLogs
| where TimeGenerated >= ago(timeFrame)
| where ResultType !in ("0", "50140")
| where ResultDescription !~ "Other"
| where AppDisplayName startswith "Microsoft Teams"
| project FailedLogonTime = TimeGenerated, UserPrincipalName, FailedIPAddress = IPAddress, AppDisplayName, ResultType, ResultDescription
) on UserPrincipalName, AppDisplayName
| where SuccessLogonTime < FailedLogonTime and FailedLogonTime - SuccessLogonTime <= logonDiff and FailedIPAddress !startswith SuccessIPBlock
| summarize FailedLogonTime = max(FailedLogonTime), SuccessLogonTime = max(SuccessLogonTime) by UserPrincipalName, SuccessIPAddress, AppDisplayName, FailedIPAddress, ResultType, ResultDescription
| extend timestamp = SuccessLogonTime, AccountCustomEntity = UserPrincipalName, IPCustomEntity = SuccessIPAddressExplanation
This query is tracking sign-ins for successful and unsuccessful logins in Microsoft Teams. It filters the SigninLogs table for entries within a specified time frame and with a successful result type. It then joins this table with another filtered SigninLogs table to get entries with unsuccessful result types. The query further filters the joined table to only include entries where the success logon time is earlier than the failed logon time, the time difference between them is within a specified threshold, and the IP addresses are not in the same block. Finally, it summarizes the results by grouping them based on user principal name, success IP address, app display name, failed IP address, result type, and result description. It also extends the results with a timestamp and custom entities for the account and IP address.
Details
Rod Trent
Released: September 1, 2020
Tables
Keywords
Operators