Query Details
Device Sign Ins
Query
// This query uses logs from Azure. // Show Device Logins for Azure joined devices. Result will show success, failure, login status, error codes as well as the location (Country, State, City) of the device. SigninLogs | where OperationName == "Sign-in activity" | where UserDisplayName != "On-Premises Directory Synchronization Service Account" | where isnotempty(Status) | extend Login_Status = tostring(todynamic(Status).errorCode) // Check the error code here: https://login.microsoftonline.com/error | extend Login_Status_Info = tostring(todynamic(Status).failureReason) | extend Device_Join_Status = tostring(todynamic(DeviceDetail).trustType) | extend location_country = tostring(todynamic(LocationDetails).countryOrRegion) | extend location_city = tostring(todynamic(LocationDetails).city) | extend location_state = tostring(todynamic(LocationDetails).state) | extend Location = strcat(location_country, " ", "/", " ", location_state, " ", "/"," ", location_city) | extend Authentication_Method = tostring(todynamic(AuthenticationDetails).[0].authenticationMethod) | extend Authentication_Detail = tostring(todynamic(AuthenticationDetails).[0].authenticationStepResultDetail) | extend Authentication_Success = tostring(todynamic(AuthenticationDetails).[0].succeeded) | where isnotempty(Device_Join_Status) | where AppDisplayName == "Windows Sign In" | project TimeGenerated, UserDisplayName, AppDisplayName, Login_Status, Login_Status_Info, Device_Join_Status, Location, Authentication_Method, Authentication_Detail, Authentication_Success
Explanation
This query is pulling data from Azure's sign-in logs to provide a detailed report on device logins for devices that are joined to Azure. It filters out any entries from the "On-Premises Directory Synchronization Service Account" and any entries with an empty status.
The query then extracts and converts various pieces of information from the logs, including the error code and failure reason associated with the login status, the trust type of the device, and the location details (country, state, city). It also concatenates the location details into a single string.
Additionally, the query extracts details about the authentication method used, the result of the authentication step, and whether the authentication was successful.
Finally, it filters out any entries with an empty device join status and any entries where the application display name is not "Windows Sign In".
The resulting output includes the time the log was generated, the user display name, the application display name, the login status and associated information, the device join status, the location, and the authentication details.
Details
Ugur Koc
Released: August 8, 2022
Tables
Keywords
Operators