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?
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?
Hi @VivekSingh-5734, 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'
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 ?????
Hi @VivekSingh-5734, thanks for replying back.
I was looking into the query you provided and you have MASTER db mentioned there hence it is returning the data related to MASTER DB only. I have highlighted it in the below screenshot.

Please change to the name of appropriate database where you need to capture the queries of.
Also to see the SQL Database, we need to see the scope of the particular database. I have highlighted the 'Select Scope' in below screenshot.

Let me know if that helps or we can discuss further on the same..
Also I don't see SQL DATABASES under favorites as seen from your screeshot....
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
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.

From the above two pics one can for the database it has 2438 recs but in AZUREDIAGNOSTIC table there's no rows.
Also Anurag some queries which we would request your attention please,
1) is there a list of security audit related queries for login fails after 3 attempts so that we can create an alert.
2) If we need to find out all DISTINCT user logins for last three months
3) If earlier SQLDBAUDITLOGS are in the blob (log analytics was not setup), can we now access those OLD XEL files from LOG Analytics to run the queries
Thanks
Hi @VivekSingh-5734, I am checking on all these points and will get back to you at the earliest on these.
Hi Anurag, sorry for so many questions. We are in the process of setting up the Log Analytic properly so that it serves as a monitoring tool as well w/ custom alerts.
Thanks Anurag. You've been a great help. We will wait for thee feedback. & appreciate your time.
9 people are following this question.