0
let data = SigninLogs
| extend AppDisplayName = iff(AppDisplayName == '', 'Unknown', AppDisplayName)
| where AppDisplayName in ('*') or '*' in ('*')
| where UserDisplayName in ('*') or '*' in ('*')
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend City = tostring(LocationDetails.city)
| extend errorCode = Status.errorCode
| extend SigninStatus = case(errorCode == 0, "Success", errorCode == 50058, "Pending user action", errorCode == 50140, "Pending user action", errorCode == 51006, "Pending user action", errorCode == 50059, "Pending user action", errorCode == 65001, "Pending user action", errorCode == 52004, "Pending user action", errorCode == 50055, "Pending user action", errorCode == 50144, "Pending user action", errorCode == 50072, "Pending user action", errorCode == 50074, "Pending user action", errorCode == 16000, "Pending user action", errorCode == 16001, "Pending user action", errorCode == 16003, "Pending user action", errorCode == 50127, "Pending user action", errorCode == 50125, "Pending user action", errorCode == 50129, "Pending user action", errorCode == 50143, "Pending user action", errorCode == 81010, "Pending user action", errorCode == 81014, "Pending user action", errorCode == 81012, "Pending user action", "Failure")
| where SigninStatus == '*' or '*' == '*' or '*' == 'All Sign-ins'
| where details.Type == '*' or (details.Type == 'Country' and Country == details.Name) or (details.Type == 'City' and City == details.Name)
| where UserPrincipalName contains "example.com"
| where Country != "AU";
//| where SigninStatus contains "success";
data
| top 10000 by TimeGenerated desc
//| extend TimeFromNow = now() - TimeGenerated
//| extend TimeAgo = strcat(case(TimeFromNow < 2m, strcat(toint(TimeFromNow / 1m), ' seconds'), TimeFromNow < 2h, strcat(toint(TimeFromNow / 1m), ' minutes'), TimeFromNow < 2d, //strcat(toint(TimeFromNow / 1h), ' hours'), strcat(toint(TimeFromNow / 10d), ' days')), ' ago')
| project User = UserDisplayName, ['Sign-in Status'] = strcat(iff(SigninStatus == 'Success', ':heavy_check_mark:', ':cross_mark:'), ' ', SigninStatus), ['Sign-in Time'] = TimeGenerated, App = AppDisplayName, ['Error code'] = errorCode, ['Result type'] = ResultType, ['Result signature'] = ResultSignature, ['Result description'] = ResultDescription, ['Conditional access policies'] = ConditionalAccessPolicies, ['Conditional access status'] = ConditionalAccessStatus, ['Operating system'] = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser, ['Country or region'] = LocationDetails.countryOrRegion, ['State'] = LocationDetails.state, ['City'] = LocationDetails.city, ['Time generated'] = TimeGenerated, Status, ['User principal name'] = UserPrincipalName, ['ClientAppUsed'] = ClientAppUsed````
