KQL Search

Query Details

HomeAI ToolsDevice Query

Debugging Authentication Sign Ins

Query

SigninLogs
| where UserPrincipalName == "[email protected]"
| extend ClientAppUsed = iff(isempty(ClientAppUsed) == true, "Unknown", ClientAppUsed) 
| extend IsLegacyAuth = 
case(ClientAppUsed contains "Browser", "No", 
ClientAppUsed contains "Mobile Apps and Desktop clients", "No",
ClientAppUsed contains "Exchange ActiveSync", "No",
ClientAppUsed contains "Other clients", "Yes", "Unknown") 
| extend errorCode = toint(Status.errorCode) 
| extend SigninStatus = 
case(errorCode == 0, "Success", 
errorCode == 50058, "Interrupt",
errorCode == 50140, "Interrupt",
errorCode == 51006, "Interrupt",
errorCode == 50059, "Interrupt",
errorCode == 65001, "Interrupt",
errorCode == 52004, "Interrupt",
errorCode == 50055, "Interrupt",
errorCode == 50144, "Interrupt",
errorCode == 50072, "Interrupt",
errorCode == 50074, "Interrupt",
errorCode == 16000, "Interrupt",
errorCode == 16001, "Interrupt",
errorCode == 16003, "Interrupt",
errorCode == 50127, "Interrupt",
errorCode == 50125, "Interrupt",
errorCode == 50129, "Interrupt",
errorCode == 50143, "Interrupt",
errorCode == 81010, "Interrupt",
errorCode == 81014, "Interrupt",
errorCode == 81012 ,"Interrupt", 
"Failure") 
| extend StatusReason = tostring(Status.failureReason)
| extend DeviceOS = DeviceDetail.operatingSystem
| extend DeviceBrowser = extract("([a-zA-Z]+)", 1, tostring(DeviceDetail.browser))
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend State = tostring(LocationDetails.state)
| extend City = tostring(LocationDetails.city)
| extend conditionalAccessStatusDesc = 
case(ConditionalAccessStatus == 0, "Success", 
ConditionalAccessStatus == 1, "Failure",
ConditionalAccessStatus == 2, "Not Applied",
ConditionalAccessStatus == "", "Not Applied", 
"Unknown")
| project CreatedDateTime, IsLegacyAuth, Id, CorrelationId, ClientAppUsed, AppDisplayName, AppId, UserDisplayName, 
UserPrincipalName, UserId, IPAddress, Country, State, City, SigninStatus, StatusReason,DeviceOS,
DeviceBrowser, conditionalAccessStatusDesc, tostring(ConditionalAccessPolicies)
| sort by CreatedDateTime desc

Explanation

This query retrieves sign-in logs for a specific user and performs various transformations on the data. It adds a column to indicate the client application used for sign-in, determines if legacy authentication was used, assigns a status to each sign-in based on error codes, extracts the device operating system and browser, retrieves location details, assigns a status to the conditional access policy, and selects specific columns for the final result. The results are then sorted by the date and time of creation in descending order.

Details

Rod Trent profile picture

Rod Trent

Released: March 26, 2020

Tables

SigninLogs

Keywords

SigninLogs,UserPrincipalName,ClientAppUsed,IsLegacyAuth,errorCode,SigninStatus,StatusReason,DeviceOS,DeviceBrowser,Country,State,City,conditionalAccessStatusDesc,CreatedDateTime,Id,CorrelationId,ClientAppUsed,AppDisplayName,AppId,UserDisplayName,UserPrincipalName,UserId,IPAddress,Country,State,City,SigninStatus,StatusReason,DeviceOS,DeviceBrowser,conditionalAccessStatusDesc,ConditionalAccessPolicies

Operators

whereextendcasecontainstointtostringextractprojectsort by

Actions