Published 11-27-2019 09:37 AM 2,690 Views

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: 




let ServerName = "ServerName";
let DBName = "DatabaseName";
| 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



Filter specific statements texts (Who DROPPED my table, Who deleted ALL rows)

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.




Checking commands that failed

Add filter 

| where succeeded_s == "false"

We can see that in this case we found and event of failure, we can see error details




What IPs have connected to my server

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";
| 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.

Regular Visitor

Hi @FonsecaSergio ,

It's nice to see you through this platform and all the three series of articles are very good. 

I have one request, could you please let me know is there a way to find the idle sessions (connection to the database persists but no activity since days), long running queries or heavy blocking in Azure SQL Data Warehouse (Synapse) using KQL?


@balineprem some part of this can be checked on Log Analytics and you can use a tool like Azure SQL Analytics (Preview) at least for SQL and SQL MI. But as Synapse may have its particularities and if you are having issues I recommend opening a Support Case for further investigation and advisory.

Occasional Visitor
Thank you for the very informative contribution. 
I am thinking about how to monitor job errors in Azure Managed Instance SQL Agent so that they can be queried via Kusto.
In order to generate alerts from it.
In general, I ask myself how T-SQL query results on, for example, system databases can flow into the Log Analytic Workspace.
This could be used to generate custom monitoring.
But I do not know how?
Version history
Last update:
‎Nov 27 2019 09:43 AM
Updated by: