SOLVED

KQL for AAD Group Add & Remove User

%3CLINGO-SUB%20id%3D%22lingo-sub-2116131%22%20slang%3D%22en-US%22%3EKQL%20for%20AAD%20Group%20Add%20%26amp%3B%20Remove%20User%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116131%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20would%20like%20to%20create%20a%20KQL%20query%20that%20can%20alert%20when%20a%20user%20has%20been%20added%20to%20a%20Azure%20Security%20Group.%26nbsp%3B%20%26nbsp%3BWhat%20would%20be%20the%20best%20way%20to%20create%20this%20query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3EJanice%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119630%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20for%20AAD%20Group%20Add%20%26amp%3B%20Remove%20User%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299647%22%20target%3D%22_blank%22%3E%40JCSBCH123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20i'm%20assuming%20that%20you%20have%20already%20Log%20analytics%20and%20you%20have%20integrated%20Azure%20AD%20logs%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20not%20you%20can%20to%20your%20directory%26nbsp%3B%20blade%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%2F%23blade%2FMicrosoft_AAD_IAM%2FActiveDirectoryMenuBlade%2FOverview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fportal.azure.com%2F%23blade%2FMicrosoft_AAD_IAM%2FActiveDirectoryMenuBlade%2FOverview%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20monitoring%20section%20go%20to%20Sign-ins%26nbsp%3B%20and%20then%20Export%20Data%20Settings%20.%3C%2FP%3E%3CP%3EYou%20will%20be%20able%20to%20add%20the%20following%20diagnostic%20settings%20%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CUL%20class%3D%22%22%3E%3CLI%3E%3CSPAN%3EAuditLogs%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESignInLogs%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ENonInteractiveUserSignInLogs%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EServicePrincipalSignInLogs%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EManagedIdentitySignInLogs%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EProvisioningLogs%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%3EClick%20on%20%2BAdd%20diagnostic%20setting%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EGive%20a%20name%20to%20the%20diagnostic%20setting%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20the%20category%20details%20Select%20at%20least%20Audit%20Logs%20and%20SignLogs%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20the%20Destination%20select%20at%20least%26nbsp%3BSend%20to%20Log%20Analytics%20workspace%20(%20if%20it's%20a%20prod%20subscription%20i%20strongly%20recommend%20to%20archive%20the%20logs%20also%20)%20.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThen%20select%20the%20subscription%20and%20an%20existing%20workspace%20will%20be%20populated%20.If%20not%20you%20have%20to%20create%20it.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EOnce%20done%20Azure%20Monitor%26nbsp%3B%3C%2FSPAN%3E%26nbsp%3Bblade%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%2F%23blade%2FMicrosoft_Azure_Monitoring%2FAzureMonitoringBrowseBlade%2Foverview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fportal.azure.com%2F%23blade%2FMicrosoft_Azure_Monitoring%2FAzureMonitoringBrowseBlade%2Foverview%3C%2FA%3E%3C%2FP%3E%3CP%3EGo%20to%20alerts%20then%20click%20on%20New%20alert%20rule%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20Scope%20section%20select%20the%20resource%20that%20should%20be%20the%20log%20analytics%20where%20you%20are%20sending%20the%20Azure%20Active%20Directory%20logs%3C%2FP%3E%3CP%3EIn%20the%20condition%20section%20you%20configure%20the%20signal%20logic%20as%20Custom%20Log%20Search%20(%20by%20default%206%20evaluations%20are%20done%20in%2030%20min%20but%20you%20can%20customize%20the%20time%20range%20.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20search%20query%20block%20copy%20paste%20the%20following%20query%20(formatted)%26nbsp%3B%20%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAuditLogs%3CBR%20%2F%3E%7C%20where%20OperationName%20in%20('Add%20member%20to%20group'%2C%20'Add%20owner%20to%20group'%2C%20'Remove%20member%20from%20group'%2C%20'Remove%20owner%20from%20group')%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20the%20alert%20logic%26nbsp%3B%20put%200%20for%20the%20value%20of%20Threshold%20and%20click%20on%20done%20.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENow%20the%20alert%20need%20to%20be%20send%20to%20someone%20or%20a%20group%20for%20that%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eyou%20can%20configure%20and%20action%20group%20where%20notification%20can%20be%20Email%2FSMS%20message%2FPush%2FVoice%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20action%20type%20can%20be%20Automation%20Runbook%20ITSM%26nbsp%3B%20Webhook%20...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFinally%20you%20can%20define%20the%20alert%20rule%20details%20(example%20in%20attached%20files)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EOnce%20done%20you%20can%20do%20the%20test%20to%20verify%20if%20you%20can%20have%20a%20result%20to%20your%20query%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAdd%20a%20member%20to%20a%20group%20and%20remove%20it%26nbsp%3B%3C%2FP%3E%3CP%3EAdd%20an%20owner%20to%20a%20group%20and%20remove%20it%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20should%20receive%20an%20email%20like%20the%20one%20in%20attachments%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20will%20help%20if%20yes%20you%20can%20mark%20it%20as%20anwser%3C%2FP%3E%3CP%3E%26nbsp%3B%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-2119493%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20for%20AAD%20Group%20Add%20%26amp%3B%20Remove%20User%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20help.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117104%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20for%20AAD%20Group%20Add%20%26amp%3B%20Remove%20User%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299647%22%20target%3D%22_blank%22%3E%40JCSBCH123%3C%2FA%3E%26nbsp%3BLook%20at%20the%20AuditLogs%20table%20and%20check%20for%20the%20%22Add%20member%20to%20group%22%20and%20probably%20%22Add%20owner%20to%20group%22%20in%20the%20OperationName%20field%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EAuditLogs%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3BOperationName%26nbsp%3Bin%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E'Add%26nbsp%3Bmember%26nbsp%3Bto%26nbsp%3Bgroup'%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E'Add%26nbsp%3Bowner%26nbsp%3Bto%26nbsp%3Bgroup'%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

I would like to create a KQL query that can alert when a user has been added to a Azure Security Group.   What would be the best way to create this query?

 

Thanks in advance,

Janice

3 Replies

@JCSBCH123 Look at the AuditLogs table and check for the "Add member to group" and probably "Add owner to group" in the OperationName field

 

AuditLogs
where  OperationName in ('Add member to group','Add owner to group')

@Gary Bushey Thank you for your help.  

best response confirmed by JCSBCH123 (New Contributor)
Solution

@JCSBCH123 

Hi

 

Hi i'm assuming that you have already Log analytics and you have integrated Azure AD logs 

 

If not you can to your directory  blade 

https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview

 

In the monitoring section go to Sign-ins  and then Export Data Settings .

You will be able to add the following diagnostic settings :  

  • AuditLogs
  • SignInLogs
  • NonInteractiveUserSignInLogs
  • ServicePrincipalSignInLogs
  • ManagedIdentitySignInLogs
  • ProvisioningLogs

Click on +Add diagnostic setting 

Give a name to the diagnostic setting 

In the category details Select at least Audit Logs and SignLogs 

In the Destination select at least Send to Log Analytics workspace ( if it's a prod subscription i strongly recommend to archive the logs also ) .

Then select the subscription and an existing workspace will be populated .If not you have to create it. 

 

Once done Azure Monitor  blade 

https://portal.azure.com/#blade/Microsoft_Azure_Monitoring/AzureMonitoringBrowseBlade/overview

Go to alerts then click on New alert rule 

In the Scope section select the resource that should be the log analytics where you are sending the Azure Active Directory logs

In the condition section you configure the signal logic as Custom Log Search ( by default 6 evaluations are done in 30 min but you can customize the time range . 

In the search query block copy paste the following query (formatted)  :  

 

AuditLogs
| where OperationName in ('Add member to group', 'Add owner to group', 'Remove member from group', 'Remove owner from group')

 

For the alert logic  put 0 for the value of Threshold and click on done . 

 

Now the alert need to be send to someone or a group for that 

you can configure and action group where notification can be Email/SMS message/Push/Voice

The action type can be Automation Runbook ITSM  Webhook ...

 

Finally you can define the alert rule details (example in attached files)

 

Once done you can do the test to verify if you can have a result to your query 

Add a member to a group and remove it 

Add an owner to a group and remove it 

You should receive an email like the one in attachments  

 

Hope that will help if yes you can mark it as anwser