One of my favorite things these days in Microsoft Cloud ecosystem is the widespread support of KQL (Kusto Query Language). I’ve used it to match variety of log information from multiple sources in Log Analytics/Sentinel Workspaces. As I wanted to keep this blog as short as possible, I’ve attached external references for more background, and pretty much jump to the task at hand 🙂
Recommended reading before proceeding
Populating location information to Office 365 logs
The OfficeActivity type category in Sentinel Logs does not have natively the location details, however there are alternative ways to populate the log
If you are willing to spend bit more time, and jump to external data sources (using GeoIP lookup) I recommend using Guided Hunting – Office365-Exploring – however, if you want to do initial lookup of location for Office Operations, then why not utilize the existing data sources first, then go to separate ”hunting mode”
Utilizing interactive and non-interactive Azure AD Sign-in logs
First step is to create list of unique locations and IP’s in Azure AD logs.
- Since most of the OfficeActivity operations have preceding login event, it makes sense to look into the Azure AD logs
Second step is to use semi-loose correlation
- Creating a larger lookup set for IP address and locations might uncover more results when creating correlation
- There are multiple JOIN types here available, I opted for showing all correlation sources in the log with FullOuter type Join (Ipaddress/location from interactive and non-interactive locations)
- There might be more optimal query available here (or more accurate), but I wanted to have single output, where I can also see the unmatched events.
- For investigation sometimes the non-correlation/negation is interested condition, especially when the negation has less output than the positive results
// extract location/city to string from different format (non-interactive, and interactive logs have different object type for LocationDetails) let dist = AADNonInteractiveUserSignInLogs | where isnotempty( IPAddress) |where TimeGenerated > now() - 50d | extend origcity = parse_json(LocationDetails).city | extend city = tostring(origcity) | join kind=inner (SigninLogs | where isnotempty( IPAddress) | extend origcity = LocationDetails.city | extend cat = Category | extend city = tostring(origcity)) on city | distinct city1,city,IPAddress,IPAddress1,Location, Location1, UserDisplayName, TimeGenerated1; // match the location to OfficeActivity logs OfficeActivity |where TimeGenerated > now() - 100d | join kind=fullouter dist on $left.ClientIP==$right.IPAddress1 | distinct city, city1, OfficeWorkload