Log Analytcs with Unknown country

%3CLINGO-SUB%20id%3D%22lingo-sub-2688822%22%20slang%3D%22en-US%22%3ELog%20Analytcs%20with%20Unknown%20country%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2688822%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%20I%20need%20help%20with%20log%20analytcs.%3CBR%20%2F%3EI'm%20building%20an%20unsual%20contry%20login%20use%20case%2C%20but%20I%20have%20several%20logs%20that%20don't%20show%20the%20country.%3C%2FP%3E%3CP%3EHow%20can%20I%20fix%20this%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2688924%22%20slang%3D%22en-US%22%3ERE%3A%20Log%20Analytcs%20with%20Unknown%20country%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2688924%22%20slang%3D%22en-US%22%3EHi%2C%20Can%20you%20share%20the%20query%20you%20have%20built%20so%20far%3F%20How%20would%20you%20like%20to%20handle%2Fsee%20the%20logons%20without%20a%20country%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi guys, I need help with log analytcs.
I'm building an unsual contry login use case, but I have several logs that don't show the country.

How can I fix this problem?

2 Replies
Hi, Can you share the query you have built so far? How would you like to handle/see the logons without a country?
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')