Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1034222%22%20slang%3D%22en-US%22%3EAZURE%20SQL%20DB%20AND%20LOG%20ANALYTICS%20BETTER%20TOGETHER%20%E2%80%93%20PART%20%233%20-%20Query%20AUDIT%20data%20or%20Who%20dropped%20my%20TABLE%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1034222%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20the%20third%20part%20of%20the%20series%20of%20articles%20to%20give%20better%20overview%20of%20Log%20Analytics%20used%20to%20query%20Azure%20SQL%20DB%20audit%20data%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20title%3D%22AZURE%20SQL%20DB%20AND%20LOG%20ANALYTICS%20BETTER%20TOGETHER%20%E2%80%93%20PART%20%231%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Database-Support-Blog%2FAZURE-SQL-DB-AND-LOG-ANALYTICS-BETTER-TOGETHER-PART-1%2Fba-p%2F794833%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EAZURE%20SQL%20DB%20AND%20LOG%20ANALYTICS%20BETTER%20TOGETHER%20%E2%80%93%20PART%20%231%3C%2FA%3E%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3E%3CA%20title%3D%22AZURE%20SQL%20DB%20AND%20LOG%20ANALYTICS%20BETTER%20TOGETHER%20%E2%80%93%20PART%20%232%20%E2%80%93%20ALERTS%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Database-Support-Blog%2FAZURE-SQL-DB-AND-LOG-ANALYTICS-BETTER-TOGETHER-PART-2-ALERTS%2Fba-p%2F795360%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EAZURE%20SQL%20DB%20AND%20LOG%20ANALYTICS%20BETTER%20TOGETHER%20%E2%80%93%20PART%20%232%20%E2%80%93%20ALERTS%3C%2FA%3E%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EIn%20this%20part%203%20we%20will%20investigate%20how%20to%20%3CSTRONG%3Equery%20AUDIT%20data%20on%20Log%20Analytics%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20know%20how%20to%20create%20a%20Log%20Analytics%20check%20previous%20parts%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20Azure%20SQL%20DB%20you%20can%20have%20AUDIT%20data%20saved%20to%20Storage%20Account%2C%20Log%20Analytics%20and%20Event%20Hub.%20%3CSTRONG%3ELog%20Analytics%20will%20be%20the%20easiest%20way%20to%20investigate%20this%20data%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20just%20need%20to%20go%20to%20%3CSTRONG%3EAzure%20Portal%20%26gt%3B%20Monitor%20%26gt%3B%20Logs%3C%2FSTRONG%3E.%20Write%20your%20Kusto%20query%20and%20run%20it%20to%20get%20data%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158993iEE1A7E89BD65182C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20find%20a%20sample%20Kusto%20query%20below%20and%20find%20more%20information%20regarding%20Kusto%20syntax%20at%3A%26nbsp%3B%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2F%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3Elet%20ServerName%20%3D%20%22ServerName%22%3B%0Alet%20DBName%20%3D%20%22DatabaseName%22%3B%0AAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(1d)%0A%7C%20where%20LogicalServerName_s%20%3D~%20ServerName%0A%7C%20where%20database_name_s%20%3D~%20DBName%0A%7C%20where%20Category%20%3D~%20%22SQLSecurityAuditEvents%22%0A%7C%20where%20action_name_s%20in%20(%22BATCH%20COMPLETED%22%2C%20%22RPC%20COMPLETED%22)%0A%7C%20project%20TimeGenerated%2C%20event_time_t%2C%20LogicalServerName_s%2C%20database_name_s%2C%20succeeded_s%2C%20session_id_d%2C%20action_name_s%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20client_ip_s%2C%20session_server_principal_name_s%20%2C%20database_principal_name_s%2C%20statement_s%2C%20additional_information_s%2C%20application_name_s%0A%7C%20top%201000%20by%20TimeGenerated%20desc%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESome%20samples%20of%20other%20usage%20and%20kind%20of%20information%20that%20you%20can%20get%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1055874765%22%20id%3D%22toc-hId-1055874765%22%3E%26nbsp%3B%3C%2FH3%3E%0A%3CH3%20id%3D%22toc-hId--751579698%22%20id%3D%22toc-hId--751579698%22%3EFilter%20specific%20statements%20texts%20(Who%20DROPPED%20my%20table%2C%20Who%20deleted%20ALL%20rows)%3C%2FH3%3E%0A%3CP%3EAdd%20below%20to%20query%20sample%20above%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%7C%20where%20statement_s%20contains%20%22DROP%22%20or%20statement_s%20contains%20%22TRUNCATE%22%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%20we%20can%20find%20who%20dropped%20an%20object%2C%20using%20what%20application%20was%20used%2C%20etc.%26nbsp%3BYou%20can%20even%20use%20the%20%3CSTRONG%3Edate%20and%20time%20of%20request%3C%2FSTRONG%3E%20a%20%3CSTRONG%3Erestore%20to%20a%20point%20in%20time%20some%20seconds%2C%20before%20the%20delete%20was%20committed%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159202iF5716DF100083E38%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1735933135%22%20id%3D%22toc-hId-1735933135%22%3EChecking%20commands%20that%20failed%3C%2FH3%3E%0A%3CP%3EAdd%20filter%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%7C%20where%20succeeded_s%20%3D%3D%20%22false%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CP%3EWe%20can%20see%20that%20in%20this%20case%20we%20found%20and%20event%20of%20failure%2C%20we%20can%20see%20error%20details%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159188i222B748643748675%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--71521328%22%20id%3D%22toc-hId--71521328%22%3EWhat%20IPs%20have%20connected%20to%20my%20server%3C%2FH3%3E%0A%3CP%3EWith%20this%20query%20you%20can%20check%20if%20some%20weird%20IP%20have%20been%20accessing%20your%20database.%20In%20this%20test%20I%20commented%20database%20to%20bring%20all%20DBs%20from%20server%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3Elet%20ServerName%20%3D%20%22SERVERNAME%22%3B%0Alet%20DBName%20%3D%20%22DATABASENAME%22%3B%0AAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(30d)%0A%7C%20where%20LogicalServerName_s%20%3D~%20ServerName%0A%2F%2F%7C%20where%20database_name_s%20%3D~%20DBName%0A%7C%20where%20Category%20%3D~%20%22SQLSecurityAuditEvents%22%0A%7C%20where%20action_name_s%20in%20(%22BATCH%20COMPLETED%22%2C%20%22RPC%20COMPLETED%22)%0A%7C%20distinct%20LogicalServerName_s%2C%20database_name_s%2C%20client_ip_s%2C%20session_server_principal_name_s%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20include%20in%20the%20query%20filter%20for%20known%20IPs%20to%20return%20only%20sporadic%20IPs%3C%2FP%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%7C%20where%20client_ip_s%20!in%20(%22167.x.x.200%22%2C%20%2210.x.x.5%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159204iA1FD7FCCB60A18B9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20you%20noticed%20you%20can%20easily%20change%20this%20Kusto%20query%20to%20filter%26nbsp%3Bother%20fields%20to%20get%20additional%20information%20like%20commands%20that%20some%20IP%20have%20ran%20or%20that%20a%20specific%20user%20or%20IP%20have%20ran.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1034222%22%20slang%3D%22en-US%22%3E%3CP%3EOn%20Azure%20SQL%20DB%20you%20can%20have%20AUDIT%20data%20saved%20to%20Storage%20Account%2C%20Log%20Analytics%20and%20Event%20Hub.%20%3CSTRONG%3ELog%20Analytics%20will%20be%20the%20easiest%20way%20to%20investigate%20this%20data%3C%2FSTRONG%3E.%26nbsp%3BIn%20this%20post%20I%20will%20show%20how%20to%20%3CSTRONG%3Equery%20AUDIT%20data%20on%20Log%20Analytics%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1034222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20DB%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

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

 

clipboard_image_0.png

 

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

 

 

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.

clipboard_image_0.png

 

 

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

clipboard_image_0.png

 

 

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

clipboard_image_1.png

 

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.