Hi, follow my query for consult:
let selectedCountry = dynamic([]);
let nonInteractive = AADNonInteractiveUserSignInLogs
| extend
LocationDetails = parse_json(LocationDetails),
Status = parse_json(Status),
ConditionalAccessPolicies = parse_json(ConditionalAccessPolicies),
DeviceDetail =parse_json(DeviceDetail);
let details = dynamic({"Name": "", "Type": "*"});
let data = union SigninLogs, nonInteractive
| extend Country = iff(LocationDetails.countryOrRegion == '', 'Unknown', tostring(LocationDetails.countryOrRegion))
| extend Device = iff(DeviceDetail.operatingSystem == '', 'Unknown', tostring(DeviceDetail.operatingSystem))
| extend AppDisplayName = iff(AppDisplayName == '', 'Unknown', AppDisplayName)
| where AppDisplayName in ('*') or '*' in ('*')
| extend Country = tostring(LocationDetails.countryOrRegion)
| where Country != 'BR'
| where Country != 'US'
| where Country != 'CO'
| where Country != 'Singapore'
| where Country != 'PH'
| where Country != 'UK'
| where Country != 'GB'
| where Country != 'SG'
| where Country != 'CH'
| where ResultType != '0'
| where ResultType != '50079'
| where ResultType != '50058'
| where array_length(selectedCountry) == 0
or "*" in (selectedCountry)
or Country in (selectedCountry)
| extend City = tostring(LocationDetails.city)
| extend errorCode = Status.errorCode
| extend SigninStatus = "Success"
| 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);
data
| top 1000 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 / 1d), ' days')), ' ago')
| project
['Time generated'] = TimeGenerated,
['Sign-in Status'] = strcat(iff(SigninStatus == 'Success', ':heavy_check_mark:', ':cross_mark:'), ' ', SigninStatus),
['Sign-in Time'] = TimeAgo,
['Country'] = LocationDetails.countryOrRegion,
User = UserDisplayName,
IPAddress,
['Operating system'] = DeviceDetail.operatingSystem,
App = AppDisplayName,
Category,
['Result type'] = ResultType,
ResultDescription,
['Result signature'] = ResultSignature,
['Conditional access policies'] = ConditionalAccessPolicies,
['Conditional access status'] = ConditionalAccessStatus,
Browser = DeviceDetail.browser
| where Category in ('SignInLogs', 'NonInteractiveUserSignInLogs')
