Forum Discussion

mcavalcantitecmicrosof's avatar
mcavalcantitecmicrosof
Copper Contributor
Aug 26, 2021

Log Analytcs with Unknown country

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?

azure monitor

2 Replies

Sort By
  • Anders Bengtsson's avatar
    Anders Bengtsson
    Former Employee
    Aug 26, 2021
    Hi, Can you share the query you have built so far? How would you like to handle/see the logons without a country?
    • mcavalcantitecmicrosof's avatar
      mcavalcantitecmicrosof
      Copper Contributor
      Aug 27, 2021
      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')

Resources