Use Azure SQL Audit policy to filter Audit data.

[Guest tarashee]

The Azure SQL Auditing feature is used to track database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.

 

If you enable SQL Audit on your Azure SQL database, there will be default settings that your Audit is configured with, including the default Action Groups "BATCH_COMPLETED_GROUP", "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP","FAILED_DATABASE_AUTHENTICATION_GROUP"

 

 

 

If you use Log Analytics, Blob Storage, or both, you will see different Audit Actions as below:

 

- On Log Analytics:

 

 

 

AzureDiagnostics

| where Category == 'SQLSecurityAuditEvents'

|where LogicalServerName_s =~ 'ServerName'

|summarize count() by action_name_s,action_id_s

 

 

 

 

 

- From the Audit xel files on Blob Storage:

 

 

 

SELECT count(*) as count,action_id FROM sys.fn_get_audit_file ('https://mystorageaccount.blob.core.windows.net/sqldbauditlogs/',default,default)

group by action_id

 

 

 

An example of the Result set:

 

 

 

 

You can filter the Audit Logs, on Log analytics by adding a where clause |where action_name_s != 'Audit Action name' as below:

 

 

 

AzureDiagnostics

| where Category == 'SQLSecurityAuditEvents'

|where LogicalServerName_s =~ 'ServerName'

//| where ResourceId =~ '/subscriptions/...’

|where action_name_s != 'RPC COMPLETED'

| project action_name_s,action_id_s, event_time_t, statement_s, succeeded_s, affected_rows_d, server_principal_name_s, client_ip_s, application_name_s, additional_information_s, data_sensitivity_information_s

| order by event_time_t desc

 

 

 

 

 

But, if you to change the Audit Policies to exclude RPC completed completely, the filtering will be by a T-SQL predicate added to your PowerShell script. as the following steps:

 

1- Identify the Action ID of the action ID ‘RCM’ ( RPC COMPLETED) or any other action, by running the following TSQL script on SSMS:

 

 

 

declare @class_type varchar(4)

 

set @class_type= 'RCM'

 

declare @x int

 

SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 1, 1))))

 

if LEN(@class_type)>=2

 

SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 2, 1)))) * power(2,8) + @x

 

else

 

SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x

 

select @x

 

 

 

For more info: Filter SQL Server Audit on action_id / class_type predicate | Microsoft Learn

 

 

 

2- Use the returned result (17234) for Set-AzSqlDatabaseAudit powershell command.

 

 

 

 

 

 

3- The PowerShell command will be as the following:

 

 

 

Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroupName" -ServerName "ServerName" -databasename "DatabaseName" -PredicateExpression "action_id != 17234"

 

 

 

 

 

For more info:

 

Set-AzSqlDatabaseAudit (Az.Sql) | Microsoft Learn

 

Configure Auditing for Azure SQL Database series - part1 - Microsoft Community Hub

 

Configure Auditing for Azure SQL Database series - Part2 - Microsoft Community Hub

 

 

 

4- You can remove the predicate expression when needed by running the command with -PredicateExpression "" as below:

 

 

 

Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroupName" -ServerName "ServerName" -DatabaseName "DatabaseName" -PredicateExpression ""

 

 

 

 

 

For more information:

 

Set-AzSqlDatabaseAudit (Az.Sql)

 

Continue reading...