How to get Users Logging Information Using Log Analytics in Azure SQL Database

user_17155786 0
Viewed 580

I am trying to get Users logged in information in Azure SQL Database Using Log Analytics. Can it be possible if so, can you please help me on this ?

Below are Options available in Diagnostic Settings for Azure SQL Database.

Click 'Add Diagnostic setting' above to configure the collection of the following data: DmsWorkers ExecRequests RequestSteps SqlRequests Waits Basic InstanceAndAppAdvanced WorkloadManagement

I want to achieve this without using Sys schemas objects related to Azure SQL Databases.

Thanks, Brahma

azure-sql-database azure
1 Answers

You need to enable Auditing in Azure SQL Server using and then you can check the logs in Azure Log Analytics.

Easiest way to enable auditing is through the Azure Portal. However, it can be easily set up through ARM templates, Azure Powershell, Azure CLI. Auditing can be enabled either at the individual database level or at the logical server level. If enabled at the server level then it automatically applies to existing databases and any new databases that are created.

However, enabling both at the server and database level leads to duplicate logs.

In the homepage of the desired Azure Sql server, in the left pane there is an option for “Auditing”.

By default, Auditing is off. Enable it. Choose the Log Analytics Workspace where you need to store the logs. Click on Save.

enter image description here

Click on Add diagnostics setting. Let us enable diagnostics for errors and InstanceAndAppAdvanced. Send this data to the log analytics workspace using your subscription and log analytics workspace. Click on Save for the configuration.

To view the logs, open up the Log Analytics workspace that was configured as a sink and choose logs and select the scope.

Summarizing the connection attempts by caller IP addresses

AzureDiagnostics
|summarize count() by client_ip_s

Source: https://www.mssqltips.com/sqlservertip/6782/kusto-query-language-query-audit-data-azure-sql-database/

user_159690410
Related