Share via

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

Vivek Singh 1 Reputation point
2021-03-23T00:18:35.137+00:00

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?

Azure SQL Database
0 comments

15 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vivek Singh 1 Reputation point
    2021-03-23T17:45:54.997+00:00

    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.

    80740-image.png

    Was this answer helpful?

    0 comments
  2. Vivek Singh 1 Reputation point
    2021-03-23T14:56:13.97+00:00

    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

    Was this answer helpful?

    0 comments
  3. Vivek Singh 1 Reputation point
    2021-03-23T14:54:06.047+00:00

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

    Was this answer helpful?

    0 comments
  4. Vivek Singh 1 Reputation point
    2021-03-23T14:52:17.573+00:00

    Hi Anurag,

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

    80730-image.png

    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 ?????

    Was this answer helpful?

  5. Anurag Sharma 17,636 Reputation points
    2021-03-23T05:47:18.837+00:00

    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

    81783-image.png

    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'

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.