Audit Filtering SQL Azure -PredicateExpression

Question

Hi,

I have turned on Azure SQL Auditing at the Server level.

This is generating a lot of Audit logs from our web application as well as our own technicians who access the SQL Azure DBs for support purposes.
Is there anyway to filter the Azure SQL Audit logs to exclude certain usernames.
I have come across that you can use Set-AzSqlDatabaseAudit -PredicateExpression but I am struggling to find any examples how to use this, especially to filter the username audit logs. Ideally we would exclude the SQL Azure Audit logs for any entries from our web application as that is audited elsewhere and just audit logs for real users who access the Azure SQL DBs.

Thanks

Andy

Answer 1

Hi, AndyWalsh-1616 Welcome to the Microsoft Q&A forum, thanks for posting your query,
Thanks, @Alberto Morillo for your continued support and for providing the query.

You can use -PredicateExpression to filter audit events. server_principal_name is the column name for logins in SQL audit, something like below should work. I have not tested the below statement but I tested the predicate expression to exclude select statements from the audit.

Set-AzSqlServerAudit -ResourceGroupName "test-rg" -ServerName "sravstestdb" -PredicateExpression "server_prinicipal_name <> 'dba'"

Exclude select statements from auditing (something that I tested )

Set-AzSqlServerAudit -ResourceGroupName "srsaluru-rg-india" -ServerName "sravstestdb" -PredicateExpression "statement not like '[select ]%'"

Please let us know if this helps.

Regards
Geetha

Answer 2

You can specify the login to monitor on the AuditAction

Set-AzureRmSqlDatabaseAuditing `
 -State Enabled `
 -ResourceGroupName "resourcegroupname" `
 -ServerName "ssqlinstancename" `  #ssqlinstancename.database.windows.net
 -StorageAccountName "strageaccountname" `
 -DatabaseName "dbname" `
 -AuditActionGroup 'SCHEMA_OBJECT_CHANGE_GROUP' `
 -RetentionInDays 8 `
 -AuditAction "SELECT ON schema::dbo BY [TheSpecificLogin]"