Correlating Azure AD logs to Office 365 workload operations With Azure Sentinel

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

• https://docs.microsoft.com/en-us/azure/sentinel/connect-azure-active-directory
• https://docs.microsoft.com/en-us/azure/sentinel/connect-office-365

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

Example results

OfficeActivity Map

Distinct Cities

Further references

Previous Sentinel Map use blog