This is the third part of the series of articles to give better overview of Log Analytics used to query Azure SQL DB audit data
In this part 3 we will investigate how to query AUDIT data on Log Analytics.
If you want to know how to create a Log Analytics check previous parts
On Azure SQL DB you can have AUDIT data saved to Storage Account, Log Analytics and Event Hub. Log Analytics will be the easiest way to investigate this data
You just need to go to Azure Portal > Monitor > Logs. Write your Kusto query and run it to get data
You can find a sample Kusto query below and find more information regarding Kusto syntax at: https://docs.microsoft.com/en-us/azure/kusto/query/
let ServerName = "ServerName";
let DBName = "DatabaseName";
AzureDiagnostics
| where TimeGenerated >= ago(1d)
| where LogicalServerName_s =~ ServerName
| where database_name_s =~ DBName
| where Category =~ "SQLSecurityAuditEvents"
| where action_name_s in ("BATCH COMPLETED", "RPC COMPLETED")
| project TimeGenerated, event_time_t, LogicalServerName_s, database_name_s, succeeded_s, session_id_d, action_name_s,
client_ip_s, session_server_principal_name_s , database_principal_name_s, statement_s, additional_information_s, application_name_s
| top 1000 by TimeGenerated desc
Some samples of other usage and kind of information that you can get
Add below to query sample above
| where statement_s contains "DROP" or statement_s contains "TRUNCATE"
In this case we can find who dropped an object, using what application was used, etc. You can even use the date and time of request a restore to a point in time some seconds, before the delete was committed.
Add filter
| where succeeded_s == "false"
We can see that in this case we found and event of failure, we can see error details
With this query you can check if some weird IP have been accessing your database. In this test I commented database to bring all DBs from server
let ServerName = "SERVERNAME";
let DBName = "DATABASENAME";
AzureDiagnostics
| where TimeGenerated >= ago(30d)
| where LogicalServerName_s =~ ServerName
//| where database_name_s =~ DBName
| where Category =~ "SQLSecurityAuditEvents"
| where action_name_s in ("BATCH COMPLETED", "RPC COMPLETED")
| distinct LogicalServerName_s, database_name_s, client_ip_s, session_server_principal_name_s
You can also include in the query filter for known IPs to return only sporadic IPs
| where client_ip_s !in ("167.x.x.200", "10.x.x.5")
As you noticed you can easily change this Kusto query to filter other fields to get additional information like commands that some IP have ran or that a specific user or IP have ran.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.