Parser - Networkaddresses Watchlist
Networkaddresses Parser
Query
// Retrieve Sign-in logs from unknown IPRanges and summarize location
union isfuzzy=true SigninLogs, AADNonInteractiveUserSignInLogs
| evaluate ipv4_lookup(Wl_NetworkAddresses, IPAddress, IPSubnet,return_unmatched = true)
| where isempty(IPSubnet)
| extend geoinfo = geo_info_from_ip_address(IPAddress)
| extend country = tostring(geoinfo.country)
| extend city = tostring(geoinfo.city)
| extend state = tostring(geoinfo.state)
| project TimeGenerated, UserPrincipalName, IPAddress, Type, Category, country, state, city
| summarize TotalUsers = dcount(UserPrincipalName), Users = make_set(UserPrincipalName) by country,city, IPAddressAbout this query
Explanation
The query retrieves sign-in logs and matches them with IP ranges from a watchlist. It then adds geolocation information and filters the results based on whether the IP range is known or unknown. The final query also summarizes the results by country, city, and IP address, showing the total number of users and their usernames.
Details
Alex Verboon
Released: November 2, 2023
Tables
SigninLogsAADNonInteractiveUserSignInLogsWl_NetworkAddresses
Keywords
DevicesIntuneUserSigninLogsAADNonInteractiveUserSignInLogsIPAddressIPSubnetWl_NetworkAddressesgeoinfocountrycitystateTimeGeneratedUserPrincipalNameRangeNameTagsWatchlistSourceTypeCategoryTotalUsersUsers
Operators
unionisfuzzySigninLogsAADNonInteractiveUserSignInLogsevaluateipv4_lookupWl_NetworkAddressesIPAddressIPSubnetreturn_unmatchedextendgeoinfogeo_info_from_ip_addresscountrycitystateprojectTimeGeneratedUserPrincipalNameRangeNameTagsWatchlistSourcewhereisemptyTypeCategorysummarizeTotalUsersdcountmake_setby