How to summarize Azure AD sign-ins from certain IP ranges using Log Analytics

We needed to figure out which users were signing in to Azure AD from networks that were not internal. By using a KQL Query in Log Analytics this was made very simple.

The KQL Query

union AADNonInteractiveUserSignInLogs, SigninLogs
| where ResultType == 0
| extend IsFromInternalNetwork=ipv4_is_in_any_range(IPAddress, "xxx.xxx.xxx.xxx/32", "xxx.xxx.xxx.xxx/32", "xxx.xxx.xxx.xxx/24")
| where IsFromInternalNetwork == false
| project TimeGenerated, IsFromInternalNetwork, CorrelationId, UserPrincipalName, IPAddress, AppDisplayName, AppId, OperationName, DeviceDetail_dynamic, NetworkLocationDetails, Location
| mv-expand parse_json(NetworkLocationDetails)
| evaluate bag_unpack(NetworkLocationDetails)
| summarize UniqueSignIns=count_distinct(CorrelationId) by UserPrincipalName, tostring(networkNames), networkType, Location

Understanding the Query

First, we union the tables for Non-interactive Sign-ins and regular Sign-ins, excluding all records which do not reflect successful sign-ins (where ResultType == 0). The internal IP ranges are specified in row 3, and then we exclude all records with IP Addresses not in this range. (where IsFromInternalNetwork == false).

The finished table counts all distinct sign-ins and summarizes by the user, and the named networks set up in Azure.

Read more

• securecloud.blog: Named and trusted networks in Azure AD logs with Log Analytics

Comments