Join, lookup and union your way to unified identity in Sentinel

One of the ongoing challenges I have had when trying to detect credential compromise or other identity issues is that identities across products and environments are often not uniform. For companies who are born in the cloud, have no legacy on premise footprint maybe this is less of an issue. But those of us who have a large on premise environment still, possibly many on premise forests and domains, it becomes a headache. How do we tie together our legacy applications that love using SamAccountName as a logon identifier, or the cloud application that syncs a legacy attribute for its username, with our modern email address logon? You may even been dealing with multiple iterations of naming standards for some attributes.

Let’s take SamAccountName as an example, that is the ‘bobsmith’ part of bobsmith@yourdomain.com. By default this is synced to Azure AD as the onPremiseSamAccountName attribute, however this attribute is not exposed in the Azure AD PowerShell module and it isn’t revealed in Azure AD Sign-in logs. It is available via MS Graph, but we can’t access MS Graph when hunting in Sentinel directly. So how do we get that data into Sentinel, then make sense of it?

Enter this awesome post about enriching Sentinel with Azure AD attributes. I won’t rehash the post here, but in summary you poll Azure AD with PowerShell and send the data to a custom table to look up. Our problem is, some of the data we want isn’t available in the Azure AD PowerShell module, so we could either get the data from MS Graph and send it via the ingestion API, or in my case, we actually use the same logic that post outlines, but we run it on premise because we know the Active Directory module will surface everything we need. If we hunt through the PowerShell that makes up the solution here, instead of connecting to Azure AD like they do –

Connect-AzureAD -AadAccessToken $aadToken -AccountId $context.Account.Id -TenantId $context.tenant.id
$Users = Get-AzureADUser -All $True

We are instead going to connect to on-premise AD and choose what attributes we want to flow to Sentinel

Get-ADUser -filter {Enabled -eq $TRUE} -SearchBase 'OU=CorporateUsers,DC=YourDomain,DC=COM' -SearchScope 2 -Properties * | select UserPrincipalName, SamAccountName, EmployeeID, Country, Office, EmailAddress, WhenCreated, ProxyAddresses

Maybe we want to filter out disabled users, only search for a particular OU (and those under it) and bring back some specific fields unique to your environment. The key one in terms of identity is having SamAccountName and UserPrincipalName in the same table, using AD as our source, but maybe your application uses EmployeeID in its logs, so bring that up too. Let’s check out our UserDetails_CL table that we sent our data to-

We can see that our test users have different formats for their SamAccountName fields, maybe they lived through a few naming standards and they have different EmployeeID lengths. Now that we have the data we can use KQL to find what we need though. Let’s say we have an alert triggered when someone fails to logon more than 3 times in 5 minutes, looks like our test account has flagged that alert

Unfortunately it’s impossible to really tell who this is, if it’s threatening or where to go from here, there is a good chance this is just noise. So let’s expand our query to bring in our UserDetails_CL table full of our info from on premise AD and see who this is. We use the KQL let operator to assign results to a variable for re-use

let alert=
SecurityEvent
| where TimeGenerated > ago (5m)
| where EventID == "4771"
| summarize count()by TargetAccount
| where count_ > 3
| extend SamAccountName = TargetAccount
| project SamAccountName;
let userdetails=
UserDetails_CL
| where TimeGenerated > ago(24h)
| extend SamAccountName = SamAccountName_s
| extend EmployeeID = EmployeeID_s
| extend UserPrincipalName = UserPrincipalName_s
| project SamAccountName, EmployeeID, UserPrincipalName;
alert
| lookup kind=leftouter userdetails on SamAccountName

So first we run our alert query, then next our UserDetails_CL custom table. If you are going to keep this table up to date, and run your PowerShell nightly, then query that table for the last 24 hours of records so you get the most current data. Then finally we combine our two queries together; there are plenty of ways in KQL to aggregate data across tables – union, join, lookup. I like using lookup in this case because we are going to join on top of this query next.

Now we have a bit more information about this user, in particular their UserPrincipalName which is used in many other places, like Azure AD. We can then join our output to another query, this time looking for Azure AD logs by ‘replaying’ that UserPrincipalName forward.

let alert=
SecurityEvent
| where TimeGenerated > ago (5m)
| where EventID == "4771"
| summarize count()by TargetAccount
| where count_ > 3
| extend SamAccountName = TargetAccount
| project SamAccountName;
let userdetails=
UserDetails_CL
| where TimeGenerated > ago(24h)
| extend SamAccountName = SamAccountName_s
| extend EmployeeID = EmployeeID_s
| extend UserPrincipalName = UserPrincipalName_s
| project SamAccountName, EmployeeID, UserPrincipalName;
alert
| lookup kind=leftouter userdetails on SamAccountName
| join kind=inner 
(SigninLogs
| project TimeGenerated, UserPrincipalName, ResultType, AppDisplayName, IPAddress, Location, UserAgent) on UserPrincipalName

So we have looked up the SecurityEvent data from on-premise, flagged an account that failed to logon more than 3 times in 5 minutes, looked up their current AD details using our custom table we ingested, then joined that data to the Azure AD logs using their UserPrincipalName.

We can see the same user has connected to Exchange Online PowerShell and we get the collated identity information for the event.

You can use the same logic to bind any tables together, use third party MFA that comes in via SysLog or CEF?

let MFA = Syslog_CL
| extend MFAOutcome  = extract("outcome=(.*?) duser", 1, SyslogMessage_s)
| extend SamAccountName = extract("duser=(.*?) cs2", 1, SyslogMessage_s)
| extend MFAMethod = extract("cs2=(.*?) cs3", 1, SyslogMessage_s)
| extend MFAApplication = extract("cs3=(.*?) ca", 1, SyslogMessage_s)
| extend MFAIPaddr = extract("src=(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))",1,SyslogMessage_s) 
| extend MFATime = TimeGenerated
| where MFAOutcome == "FAILURE"
| project MFATime, SamAccountName, MFAOutcome, MFAMethod, MFAApplication, MFAIPaddr;
let UserInfo = 
UserDetails_CL
| extend UserPrincipalName = UserPrincipalName_s
| extend SamAccountName = SamAccountName_s;
MFA
| lookup kind=leftouter UserInfo on SamAccountName
| project MFATime, MFAOutcome, MFAMethod, MFAIPaddr, SamAccountName_s, UserPrincipalName
| join kind=inner 
(SigninLogs
| where ResultType == "50158"
| project TimeGenerated, UserPrincipalName, ResultType, AppDisplayName, IPAddress, Location, UserAgent) on UserPrincipalName
| where MFATime between ((TimeGenerated-timespan(10min)).. (TimeGenerated+timespan(10min))) and IPAddress != MFAIPaddr

In this example the third party MFA uses SamAccountName as an identifier and the logs come into a Syslog table. We parse out the relevant details – MFA outcome (pass/fail), SamAccountName, MFA method (push, phone call, text etc), IP address and time, then find only MFA failures. We lookup our SamAccountNames in our UserDetails_CL table to get the UserPrincipalNames. Finally we query Azure AD for any logon events that have triggered a MFA request (ResultType = 50158). Then we add additional logic where we are only interested in events MFA and logon events within 20 minutes of each and where the IP address that logged onto Azure AD is different to the MFA IP address, which could suggest an account has been compromised but the owner of the account denied the MFA prompt from a different location.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s