question

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 asked VivekSingh-5734 answered

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

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT edited

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

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'



image.png (155.9 KiB)
image.png (179.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered AnuragSharma-MSFT commented

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


image.png (45.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

80650-image.png

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.

80719-image.png

Let me know if that helps or we can discuss further on the same..


0 Votes 0 ·
image.png (20.0 KiB)
image.png (146.5 KiB)
VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

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



image.png (72.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered



image.png (50.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

From the above two pics one can for the database it has 2438 recs but in AZUREDIAGNOSTIC table there's no rows.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered AnuragSharma-MSFT commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VivekSingh-5734, I am checking on all these points and will get back to you at the earliest on these.

0 Votes 0 ·
VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

Thanks Anurag. You've been a great help. We will wait for thee feedback. & appreciate your time.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.