Azure SQL audit set for Log Analytics. Cannot find the SQLDBAUDITLOGS in Log Analytics Workspace under LOGS blade

Question

We have set the Azure SQL audit log for log Analytics and saved the configuration.

Burt from the Log Analytics Workspace & under LOGS blade cannot find the AUDIT logs for query purpose?

Answer 1

Hi @Vivek Singh , welcome to Microsoft Q&A forum.

You can find the audit logs by running the queries on to Log Analytics Workspace as shown below:

AzureDiagnostics   
| where ResourceId == '/SUBSCRIPTIONS/xxxxxxxxx/RESOURCEGROUPS/ANURSHAR-DATABASES/PROVIDERS/MICROSOFT.SQL/SERVERS/YOURSERVERNAME/DATABASES/YOURDBNAME'   
| project 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  
| take 100  

Also, you can set the scope as mentioned in above screenshot to the Azure SQL Database. There are multiple queries you can write and they will show the appropriate results accordingly.

Please let us know if this helps or else we can discuss further.

----------

If answer helps, please mark it 'Accept Answer'

Answer 2

Hi Anurag,

Thanks for the response. I ran the query provided by you but still no rows returned . Pl see below.

AzureDiagnostics | where ResourceId == 'SUBSCRIPTIONS/B14473C6-5CC7-43AB-B698-07208272F045/RESOURCEGROUPS/IM-CRM_PRODUCTION/PROVIDERS/MICROSOFT.SQL/SERVERS/xvxvxvxvx/DATABASES/MASTER' | project 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 | take 100

i got the resource id from this query
AzureDiagnostics | summarize totalNumberofRecords = count() by ResourceId | sort by totalNumberofRecords desc" which just returned only the MASTER database but not other ones ?????

Answer 3

Also I don't see SQL DATABASES under favorites as seen from your screeshot....

Answer 4

zureDiagnostics | summarize totalNumberofRecords = count() by ResourceId | sort by totalNumberofRecords desc" which just returned only the MASTER database but not other ones ????? this had returned 8000+ recs for the MASTER database but not others

Answer 5

Thanks for the prompt reply. I have changed the scope and selected the exact DB and also made changes to the query as well. No results. Yet another query says there are 8k+ recs.