How To Configure Auditing on Azure SQL Database with Selected Events

Step 1: Setting the with right subscription
Set-AzContext -subscription yoursubscriptionid
Step2:To check the events
get-AzSqlDatabaseAudit -ResourceGroupName "urresorucegroupname" -ServerName "ursqlservername" -DatabaseName "urdbname"
Step3:Configure audit on selected events.
Set-AzSqlDatabaseAudit -ResourceGroupName test -ServerName testdemo07 -DatabaseName test -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/aa9b3e61-1d08-430e-9e19-3ebd6e25bb2/resourceGroups/test/providers/Microsoft.Storage/storageAccounts/testingtest123" -RetentionInDay 10 -PredicateExpression "statement like 'INSERT%' or statement like 'select%'"

[@RajeevRanjan-nf6xc]

statement which are done by API is not capture through this, it is captured for database queries. please let me know how we can capture for activities which are done through any API

[@MSSQLDBATechSupport]

This video is all about to audit selected events only.try default auditing and also diagnostic options as well. still you face any problem please do raise ticket to Microsoft support.the behaviour will be controlled by them only and they will give us RCA as well.

[@ajitavchatterjee7555]

I tried something like this to exclude all sql statements from Audit logs, however it doesn't work.I still get select statements logs in my audit logs. Do you have any suggestion? Set-AzSqlServerAudit -ResourceGroupName "myresourcegroup" -ServerName "mysqlserver" -PredicateExpression "statement 'exec sp_executesql N%select%' or statement 'SELECT 1%'"

[@MSSQLDBATechSupport]

I had done this video almost 2 years back. Since azure sql database are having frequent updates. Some time the new update will break some existing functionality. I need to check this one from my end and then can comment to one conclusion. Thanks & Regards *MS SQL DBA Tech Support *

[@ejsundar2882]

@@MSSQLDBATechSupport - I am trying to replicate the same now and I have a couple of questions. 1. Can we enable this auditing and disable them as per our timeframe. 2. I only need Update and Insert commands alone. 3. How many days these logs can be retained Can you please help me on this.

[@MSSQLDBATechSupport]

@@ejsundar2882 you can disable whenever you want... and need update and insert commands alone ,this thing need to check... both can be done at a time or anyone u want,coming to rentation ....its all depends how many days you mentioned.. in the video i mentioned for 10 days... If you have doubts test it and analyze and then come to conclusion.

[@somanathareddym3467]

where can I get all the available events ?

[@MSSQLDBATechSupport]

If your configure auditing by default it will configure all the events. apart from this if you want like blockings and deadlocks, u need to look into diagnostic settings. Since its azure it will change the functionality from time to time. Thanks & Regards MS SQL DBA TECH SUPPORT