Create audit on azure SQL Database for delete command

Question

I´m trying to create a new audit on Azure SQL Database with command below:

Set-AzSqlDatabaseAudit -ResourceGroupName "NAME" -ServerName "SERVER" -DatabaseName "DATABASE" -AuditActionGroup "BATCH_COMPLETED_GROUP" -AuditAction "DELETE ON TABLE BY PUBLIC" -BlobStorageTargetState Enabled -StorageAccountResourceId "/..."

In audit log I noticed that all comandos are being registered, not just the delete commands in object desired and informed in cmdlet.

I can´t submit the comand withou option -AuditActionGroup "BATCH_COMPLETED_GROUP". I think the problem is here but I can´t submit without this part.

Alberto Morillo · Accepted Answer · 2022-04-06 15:15:16Z

0

Please try auditing the action at the database level, that has worked for me.

Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup"
 -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT"  
 -StorageAccountName "storageAccount" 
-AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP" 
 -AuditAction "DELETE ON database::[AdventureWorksLT] BY [public]"  
 -RetentionInDays 60

answered Apr 6, 2022 at 15:15

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LMC Over a year ago

Thanks for the answer. In my case I need to filter only DELETE commands in just only one table. When I create audit with the option: -AuditActionGroup "BATCH_COMPLETED_GROUP" the result is that SQL Database logs all commands e not only the DELETE on table that I specified. I tried to inform only the -AuditAction but not works too.

Alberto Morillo Over a year ago

What about saving the Audit log to Azure Log Analytics. You can query there what you want. You just need to go to Azure Portal > Monitor > Logs. Write your Kusto query and run it to get data

LMC Over a year ago

I already have an audit configured as a server side on Azure. The log is saved in blob storage. In this specific case I would like to save just only DELETE comands on just one specific table. But in my case after audit created all commands are being saved.

Collectives™ on Stack Overflow

Create audit on azure SQL Database for delete command

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related