How to get the list of users who are not accessed to azure SQL Database from last 30 days?

Question

How to get the list of users who are not accessed to azure SQL Database from last 60 days? Appreciate your assistance.

Answer 1

To get a list of users who have not accessed an Azure SQL Database in the last 60 days, you can leverage Azure's auditing and logging capabilities along with querying the audit logs. Here's a general approach you can follow:

1. Enable Auditing: Ensure that auditing is enabled for your Azure SQL Database. Auditing captures activities and events that occur at the database level, including logins and access attempts.

2. Query Audit Logs: Use Azure Monitor Logs to query the audit logs. You can use Kusto Query Language (KQL) to write queries against the logs.

3. Identify User Activity: Write a KQL query to filter audit logs for user activity related to database access. You can filter based on event types like "Successful SQL Database logins" or "Failed SQL Database logins".

4. Filter by Timestamp: Add a filter to the query to only include audit records within the last 60 days.

5. Group and Aggregate: Group the results by the user identity and aggregate the count of access events for each user.

6. Identify Inactive Users: Filter the results further to identify users who have not accessed the database within the last 60 days.

Here's a sample KQL query to get you started:

AzureDiagnostics
| where ResourceType == "SQLSERVER"
| where TimeGenerated >= ago(60d)
| where ActionType == "DATABASE_AUTHENTICATION_SUCCESS" or ActionType == "DATABASE_AUTHENTICATION_FAILED"
| summarize AccessCount = count() by UserId
| where AccessCount == 0

This query filters audit logs for successful and failed authentication events within the last 60 days and then groups them by user identity. Finally, it filters users who have not accessed the database during this period. Please note that you may need to adjust the query based on your specific audit log schema and requirements. Additionally, ensure that you have appropriate permissions to access audit logs and Azure Monitor Logs.