SOLVED

KQL query for brute force against users in certain AAD groups

%3CLINGO-SUB%20id%3D%22lingo-sub-2500791%22%20slang%3D%22en-US%22%3EKQL%20query%20for%20brute%20force%20against%20users%20in%20certain%20AAD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2500791%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20a%20way%20to%20search%20for%20brute%20force%20attacks%20against%20users%20that%20are%20part%20of%20certain%20AAD%20groups.%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20found%20that%20i%20can%20utilize%20brute%20force%20queries%20already%20available%20in%20Azure%20Sentinel%20hunting%20queries%2C%20but%20checking%20this%20information%20up%20against%20group%20membership%20seems%20to%20be%20an%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20find%20some%20information%20about%20group%20membership%20trough%20the%20audit%20logs%2C%20but%20the%20best%20solution%20would%20be%2C%20if%20possible%2C%20to%20just%20query%20straight%20against%20AAD%20group%20membership.%20Is%20this%20information%20possible%20to%20stream%20into%20Azure%20Sentinel%20log%20analytics%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2512618%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20for%20brute%20force%20against%20users%20in%20certain%20AAD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2512618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F819982%22%20target%3D%22_blank%22%3E%40stianhoydal%3C%2FA%3E%26nbsp%3Bthere%20are%20a%20few%20ways%20you%20could%20achieve%20this%20and%20you%20may%20want%20to%20have%20a%20try%20a%20few%20with%20what%20works%20best%2C%20the%20AuditLog%20is%20only%20going%20to%20track%20changes%20to%20your%20groups%2C%20so%20you%20need%20to%20reference%20your%20brute%20force%20attack%20query%20vs%20the%20current%20list%20of%20members%20of%20those%20groups.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20easiest%20may%20be%20having%20a%20Watchlist%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fwatchlists%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20Azure%20Sentinel%20watchlists%20%7C%20Microsoft%20Docs%3C%2FA%3E%26nbsp%3Bwhich%20contains%20the%20list%20of%20users%20who%20are%20a%20part%20of%20that%20group.%20You%20could%20keep%20that%20Watchlist%20current%20by%20using%20a%20Logic%20App%20that%20polls%20Azure%20AD%20%2F%20MS%20Graph%20and%20updates%20the%20watchlist%20every%20hour%20or%20whatever%20suits%20your%20environment%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fazurecloudai.blog%2F2021%2F06%2F30%2Fhow-to-use-the-watchlists-logic-app-connector-for-azure-sentinel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EHow%20to%20Use%20the%20Watchlists%20Logic%20App%20Connector%20for%20Azure%20Sentinel%20%E2%80%93%20Azure%20Cloud%20%26amp%3B%20AI%20Domain%20Blog%20(azurecloudai.blog).%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20you%20created%20a%20watchlist%20called%20'HighRiskUsers'%20with%20the%20list%20of%20UserPrinicpalNames%20of%20the%20users%20in%20those%20groups%20you%20could%20then%20add%20logic%20to%20look%20at%20that%20list%20before%20alerting%20you%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20watchlist%20%3D%20(_GetWatchlist('HighRiskUsers')%20%7C%20project%20UserPrincipalName)%3B%3CBR%20%2F%3E%3CYOUR%20brute%3D%22%22%20force%3D%22%22%20attack%3D%22%22%20query%3D%22%22%3E%3C%2FYOUR%3E%3C%2FP%3E%3CP%3E%7C%20project%20UserPrincipalName%3CBR%20%2F%3E%7C%20where%20UserPrincipalName%20in%20(watchlist)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20you%20could%20look%20at%20using%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazureloganalyticsdatacollector%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Log%20Analytics%20Data%20Collector%20-%20Connectors%20%7C%20Microsoft%20Docs%3C%2FA%3E%26nbsp%3Bor%20the%20ingestion%20API%20to%20send%20that%20data%20to%20a%20custom%20table.%20So%20you%20could%20poll%20AzureAD%20using%20Powershell%2FMS%20Graph%20to%20get%20membership%20of%20those%20groups%2C%20then%20ingest%20what%20you%20wanted%20to%20HighRiskUsers_CL%20as%20an%20example%2C%20then%20in%20your%20query%20you%20could%20use%20a%20join%20to%20match%20against%20your%20brute%20force%20query%20and%20your%20custom%20table%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20Alert1%20%3D%3CBR%20%2F%3ESigninLogs%3CBR%20%2F%3E%3CYOUR%20brute%3D%22%22%20force%3D%22%22%20attack%3D%22%22%20query%3D%22%22%3E%3CBR%20%2F%3E%7C%20project%20TimeGenerated%2C%20UserPrincipalName%3CBR%20%2F%3E%3B%3CBR%20%2F%3Elet%20HighRiskUsers%20%3D%3CBR%20%2F%3EHighRiskUsers_CL%3CBR%20%2F%3E%7C%20project%20UserPrincipalName%3CBR%20%2F%3E%3B%3CBR%20%2F%3EAlert1%3CBR%20%2F%3E%7C%20join%20kind%3Dinner%20HighRiskUsers%20on%20UserPrincipalName%3C%2FYOUR%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2518260%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20for%20brute%20force%20against%20users%20in%20certain%20AAD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2518260%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20great%20response.%20I%20will%20try%20this%20once%20i%20get%20back%20to%20the%20project.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2518264%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20for%20brute%20force%20against%20users%20in%20certain%20AAD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2518264%22%20slang%3D%22en-US%22%3EAnytime!%20Your%20post%20actually%20made%20me%20test%20it%20out%20in%20my%20tenant%20-%20%3CA%20href%3D%22https%3A%2F%2Flearnsentinel.blog%2F2021%2F07%2F04%2Fenrich-hunting-with-data-from-ms-graph-and-azure-ad%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Flearnsentinel.blog%2F2021%2F07%2F04%2Fenrich-hunting-with-data-from-ms-graph-and-azure-ad%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2518290%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20for%20brute%20force%20against%20users%20in%20certain%20AAD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2518290%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1085960%22%20target%3D%22_blank%22%3E%40m_zorich%3C%2FA%3E%26nbsp%3BSweet%2C%20with%20a%20guide%20and%20everything.%20This%20will%20make%20a%20fine%20addition%20to%20my%20collection%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2F%400277EEB71C55CDE7DB26DB254BF2F52B%2Fimages%2Femoticons%2Flaugh_40x40.gif%22%20alt%3D%22%3Alol%3A%22%20title%3D%22%3Alol%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2512704%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20for%20brute%20force%20against%20users%20in%20certain%20AAD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2512704%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20could%20do%20this%20in%20a%20few%20ways%2C%20you%20may%20need%20to%20test%20to%20see%20what%20works%20best%20for%20you%20environment.%20The%20issue%20you%20are%20facing%20is%20that%20the%20AuditLogs%20table%20will%20only%20track%20changes%20to%20groups%2C%20you%20want%20to%20query%20log%20data%20(your%20brute%20force%20attack%20query)%20vs%20static%20data%20(group%20membership)%3CBR%20%2F%3E%3CBR%20%2F%3E1.%20Add%20the%20users%20to%20a%20watchlist%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fwatchlists%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fwatchlists%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20upload%20an%20initial%20csv%20to%20the%20watchlist%20that%20contains%20the%20userprincipalnames%20of%20the%20users%20who%20are%20in%20the%20groups%20you%20care%20about.%20In%20this%20example%20a%20watchlist%20called%20HighRiskUsers%20with%20userprincipalname%20as%20a%20column%20header.%20You%20then%20include%20being%20in%20that%20watchlist%20as%20part%20of%20your%20query%20-%3CBR%20%2F%3E%3CBR%20%2F%3Elet%20watchlist%20%3D%20(_GetWatchlist('HighRiskUsers')%20%7C%20project%20UserPrincipalName)%3B%3CBR%20%2F%3ESigninLogs%3CBR%20%2F%3E*your%20brute%20force%20query%20here*%3CBR%20%2F%3E%7C%20where%20UserPrincipalName%20in%20(watchlist)%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20then%20use%20a%20logic%20app%20to%20keep%20that%20watchlist%20current%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazuresentinel%2F%23watchlists---update-an-existing-watchlist-item%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazuresentinel%2F%23watchlists---update-an-existing-watchlist-item%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20example%2C%20poll%20Azure%20AD%2FMS%20Graph%20for%20the%20membership%20of%20those%20groups%20every%20few%20hours%2C%20then%20have%20the%20logic%20app%20keep%20it%20current%3CBR%20%2F%3E%3CBR%20%2F%3E2.%20You%20could%20ingest%20those%20members%20(and%20potentially%20the%20group%20names%20if%20useful)%20to%20a%20custom%20table%20in%20Sentinel%20then%20use%20a%20join%20operator%20in%20your%20query.%20You%20can%20send%20custom%20data%20using%20the%20ingestion%20API%20or%20Azure%20Log%20Analytics%20Data%20Collector%20logic%20app%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazureloganalyticsdatacollector%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazureloganalyticsdatacollector%2F%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20again%2C%20you%20could%20poll%20Azure%20AD%2FMS%20Graph%2C%20and%20send%20the%20data%20to%20Sentinel%20as%20a%20custom%20log%2C%20using%20the%20same%20example%20you%20could%20call%20it%20HighRiskUsers_CL.%20Then%20when%20you%20write%20your%20hunting%20query%20you%20want%20to%20join%20on%20matches%20between%20your%20brute%20force%20query%20and%20the%20HighRiskUsers_CL%20table%20-%3CBR%20%2F%3E%3CBR%20%2F%3Elet%20Alert1%20%3D%3CBR%20%2F%3ESigninLogs%3CBR%20%2F%3E%3CYOUR%20brute%3D%22%22%20force%3D%22%22%20attack%3D%22%22%20query%3D%22%22%3E%3CBR%20%2F%3E%7C%20project%20UserPrincipalName%3CBR%20%2F%3E%3B%3CBR%20%2F%3Elet%20HighRiskUsers%20%3D%3CBR%20%2F%3EHighRiskUsers_CL%3CBR%20%2F%3E%7C%20project%20UserPrincipalName%2C%20HighRiskGroupName%3CBR%20%2F%3E%3B%3CBR%20%2F%3EAlert1%3CBR%20%2F%3E%7C%20join%20kind%3Dinner%20HighRiskUsers%20on%20UserPrincipalName%3CBR%20%2F%3E%7C%20project%20UserPrincipalName%2C%20HighRiskGroupName%3C%2FYOUR%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Greetings. 

I am trying to figure out a way to search for brute force attacks against users that are part of certain AAD groups.

So far I have found that i can utilize brute force queries already available in Azure Sentinel hunting queries, but checking this information up against group membership seems to be an issue. 

 

I can find some information about group membership trough the audit logs, but the best solution would be, if possible, to just query straight against AAD group membership. Is this information possible to stream into Azure Sentinel log analytics?

5 Replies

@stianhoydal there are a few ways you could achieve this and you may want to have a try a few with what works best, the AuditLog is only going to track changes to your groups, so you need to reference your brute force attack query vs the current list of members of those groups.

 

The easiest may be having a Watchlist - Use Azure Sentinel watchlists | Microsoft Docs which contains the list of users who are a part of that group. You could keep that Watchlist current by using a Logic App that polls Azure AD / MS Graph and updates the watchlist every hour or whatever suits your environment - How to Use the Watchlists Logic App Connector for Azure Sentinel – Azure Cloud & AI Domain Blog (azu...

 

So if you created a watchlist called 'HighRiskUsers' with the list of UserPrinicpalNames of the users in those groups you could then add logic to look at that list before alerting you -

 

let watchlist = (_GetWatchlist('HighRiskUsers') | project UserPrincipalName);
<your brute force attack query>

| project UserPrincipalName
| where UserPrincipalName in (watchlist)

 

Alternatively you could look at using the Azure Log Analytics Data Collector - Connectors | Microsoft Docs or the ingestion API to send that data to a custom table. So you could poll AzureAD using Powershell/MS Graph to get membership of those groups, then ingest what you wanted to HighRiskUsers_CL as an example, then in your query you could use a join to match against your brute force query and your custom table of data.

 

let Alert1 =
SigninLogs
<your brute force attack query>
| project TimeGenerated, UserPrincipalName
;
let HighRiskUsers =
HighRiskUsers_CL
| project UserPrincipalName
;
Alert1
| join kind=inner HighRiskUsers on UserPrincipalName

 

 

best response confirmed by stianhoydal (Contributor)
Solution

You could do this in a few ways, you may need to test to see what works best for you environment. The issue you are facing is that the AuditLogs table will only track changes to groups, you want to query log data (your brute force attack query) vs static data (group membership)

1. Add the users to a watchlist - https://docs.microsoft.com/en-us/azure/sentinel/watchlists

You could upload an initial csv to the watchlist that contains the userprincipalnames of the users who are in the groups you care about. In this example a watchlist called HighRiskUsers with userprincipalname as a column header. You then include being in that watchlist as part of your query -

let watchlist = (_GetWatchlist('HighRiskUsers') | project UserPrincipalName);
SigninLogs
*your brute force query here*
| where UserPrincipalName in (watchlist)

You could then use a logic app to keep that watchlist current - https://docs.microsoft.com/en-us/connectors/azuresentinel/#watchlists---update-an-existing-watchlist...

For example, poll Azure AD/MS Graph for the membership of those groups every few hours, then have the logic app keep it current

2. You could ingest those members (and potentially the group names if useful) to a custom table in Sentinel then use a join operator in your query. You can send custom data using the ingestion API or Azure Log Analytics Data Collector logic app - https://docs.microsoft.com/en-us/connectors/azureloganalyticsdatacollector/

So again, you could poll Azure AD/MS Graph, and send the data to Sentinel as a custom log, using the same example you could call it HighRiskUsers_CL. Then when you write your hunting query you want to join on matches between your brute force query and the HighRiskUsers_CL table -

let Alert1 =
SigninLogs
<your brute force attack query>
| project UserPrincipalName
;
let HighRiskUsers =
HighRiskUsers_CL
| project UserPrincipalName, HighRiskGroupName
;
Alert1
| join kind=inner HighRiskUsers on UserPrincipalName
| project UserPrincipalName, HighRiskGroupName

Thank you for the great response. I will try this once i get back to the project.

@m_zorich Sweet, with a guide and everything. This will make a fine addition to my collection :lol: