Azure SQL DB Auditing allows Users and organizations to track specific database events like (DDL, DML, TCL, DQL,DCL ,Errors etc.) rather than tracking all of the Database default  events. This helps organizations to keep audit imposed only on specific users with predefined Audit actions along with reducing the exponential storage consumptions. The process below provides step by step guide to configure Auditing for specific Events and user lists.

1) Open Azure Cloud shell and Connect to your subscription by using the below PowerShell Cmdlet

set-Azcontext -Subscription <Your subscription ID> 

2) Copy and execute the below PowerShell Script, This script will create a PS function “SET-AUDITING” in the current session.

Function Set-Auditing\n{\nParam\n  (\n    [parameter(Mandatory=$true)][string]$ResourceGroup,\n    [parameter(Mandatory=$true)][string]$SQLServerName,\n    [parameter(Mandatory=$true)][string]$databasename,\n    [parameter(Mandatory=$true)][string]$Storageaccount,\n    [parameter(Mandatory=$true)][INT]$RetentionPeriod\n)\n##Get Storage resource Id \n   $StorageId = Get-AzResource -ResourceGroupName $ResourceGroup  -Name $Storageaccount |select -expandproperty 'ResourceId'\n##Get Actions and users to Audit \n   [string[]] $_actionlist= @()\n   [string[]] $_userlist= @()\n   $_actionlist = READ-HOST \"ENTER LIST OF ACTIONS TO AUDIT(atleast One)\"\n   $_userlist =READ-HOST \"ENTER LIST OF USERS TO AUDIT (Enter A to track all users)\"\n   $preaction= $_actionlist.Split(',').Split(' ')\n   $statements = ForEach($rs in $preaction)\n     {\n       \" \"+\"statement\"+ \" \" + \"like\" +\" \"+\"'\"+$rs+\"%\"+\"'\"+\" \"\n     }\n   $FinalactionStatement = $statements -join(\"OR\")\n   if($_userlist -eq 'A')\n     {\n       $finalstatement = $FinalactionStatement\n\n       }\n    else \n     {\n      $preuser= $_userlist.Split(',').Split(' ')\n      $userstatements = ForEach($rsp in $preuser)\n     {\n       \" \"+\"database_principal_name =\"+\" \"+\"'\"+$rsp+\"'\"+\" \"\n          }\n        $finaluserstatements = $userstatements -join(\"OR\")\n        $finalstatement = \"(\"+$FinalactionStatement+\")\"+\" \"+\"AND\"+\" \"+\"(\"+$finaluserstatements+\")\"\n        \n     }\n  #SET DB AUDITING \n  Set-AzSqlDatabaseAudit -ResourceGroupName $ResourceGroup -ServerName $SQLServerName  -DatabaseName $databasename -BlobStorageTargetState Enabled  -StorageAccountResourceId $StorageId -RetentionInDay $RetentionPeriod  -PredicateExpression \"$finalstatement\"\n  #GET AUDIT STATUS \n  $ST = get-AzSqlDatabaseAudit -ResourceGroupName $ResourceGroup -ServerName $SQLServerName -DatabaseName $databasename|Select  DatabaseName,PredicateExpression,StorageAccountResourceId,RetentionInDays,BlobStorageTargetState\n\nreturn $ST\n } \n

3) Now Execute the function (SET-AUDITING) by using the below Cmdlet in the same PowerShell Session and provide the parameter needed to set auditing along with retention period. (Example below)

Set-Auditing 

Note:

** Azure retention period = 0 , Cities the audit file will remain forever in the storage account 

** If you want to track specific action event (Eg: DDL) for all the user , Please type A when it asks to put user lists to audit in the PS execution

**Separate multiple input with just a comma(,) as mentioned below in example  ,avoid spaces.

Thank you Deepjoy   for helping me testing the script in multiple test cases , Thanks Yochanan_Rachamim  for guidance. 

Azure SQL DB Auditing allows Users and organizations to track specific database events like (DDL, DML, TCL, DQL,DCL ,Errors etc.) rather than tracking all of the Database default  events. This helps organizations to keep audit imposed only on specific users with predefined Audit actions along with reducing the exponential storage consumptions. The process below provides step by step guide to configure Auditing for specific Events and user lists.

1) Open Azure Cloud shell and Connect to your subscription by using the below PowerShell Cmdlet

2) Copy and execute the below PowerShell Script, This script will create a PS function “SET-AUDITING” in the current session.

3) Now Execute the function (SET-AUDITING) by using the below Cmdlet in the same PowerShell Session and provide the parameter needed to set auditing along with retention period. (Example below)

Note:

** Azure retention period = 0 , Cities the audit file will remain forever in the storage account 

** If you want to track specific action event (Eg: DDL) for all the user , Please type A when it asks to put user lists to audit in the PS execution

**Separate multiple input with just a comma(,) as mentioned below in example  ,avoid spaces.

Thank you    for helping me testing the script in multiple test cases , Thanks   for guidance. 

so how to read that .xel files in my storage account?

I trying to change this code , instead of \"the list of users to audit\", I want \"list of users not to audit\". your help is appreciated!

$_userlist =READ-HOST \"ENTER LIST OF USERS NOT TO AUDIT

Hello Tarell01  , . There is actually no restriction in selecting a storage account from a different resource group but the code flow is written as such it automatically tries to look the storage account in the RG defined in the default variable . May be I can add a new variable to define resource group for Storage account . Thanks so much for your valuable feedback , Really appreciate it . 

Nice, thanks!   

Just a note.  The Storage account and SQL Server have to be in the same resource group.   I am new to using Powershell functions in Azure and that was something I did not think about.   It gave me an error during execution, saying the storage account id could not be found. 

Thanks Frank_Ruiter 

Wow! Very nice! thx..