Last Updated on September 25Th, 2016
Last updated on September 25th, 2016
Background
This paper specifies the Table Audit logs which are generated by Azure SQL Database.Audit logs are collected into Azure StorageTables that are generated periodically in an Azure Storage account, which is owned by the customer.
Audit log table names are always prefixed with SQDBAuditLogs, followed by a user defined string (default value ServernameDatabasename). If log retention is enabled, thetable creation date is also appended to the end of the table name with yyyymmdd format.
Example: An audit log table for database “MayaDB” on server “Server1” created on August 26th, 2016 will be named SQLDBAuditLogsServer1MayaDB20160826
Customers may view the logs using tools such asAzure Storage Explorer,import to Excel 2013/2016 directly from the Azure Table using Power Query, oruse theExcel Report Templatethat can be downloaded from the Azure Portal. Customers may also develop their own logs reporting solution or integrate with a solution of their choice.
“SQLDBAuditLogs” table fields
# (*) / Name / Description1 / PartitionKey / The Partition Key format has 4 sections which are separated by the ‘*’ delimiter:
1)Auditing group (current value is always ‘defaultgroup’)
2)Server fully qualified domain name
3)Database name
4)A number between 0 -9. Required for performance optimization
2 / RowKey / The row key format has 2 section separated by the ‘*’ delimiter
1)The time to max time –refer to section ‘Row Key format details’ for details.
2)A GUID – required to ensure a unique value
3 / TimeStamp / The time at which the record was written /updated in the Azure Table Storage. (Automatically generated). Please use EventTime for reporting
4 / EventTime / The time at which the audited event took place
5 / ServerName / The FQDN of the Azure SQL Database Server
6 / DatabaseName / Database name
7 / ApplicationName / As defined in Application Name MSDN
8 / ClientIP / The source IP of the client application
9 / EventId / A unique identifier for each logged event (GUID). Multiple audit entries that pertain to the same event will have the same EventId. (When pivoting on the AuditLogs table usually use ‘distinct count of EventId as the measure)
10 / EventCategory / A classification of the audit event into several categories:
Plain SQL; Parameterized SQL; Stored Procedure; Login; Transaction Management
11 / ActionStatus / “Success”, “Failure”, “Cancellation” or“Unknown”.
12 / FailureReason / For failed actions, the error description text as returned from the server. Empty for actions that succeeded.
13 / FailureSeverity / “Security” if the SQL statementfailed with security level 14. Empty otherwise
14 / Statement / The full text of the SQL Batch, or the name and parameters of an RPC call.
15 / PrincipalName / “Service Principal Name” – identifier for the user / application account
16 / AffectedRows / The aggregated value of AffectedRows for all the result sets. This value may be used to detect rows that were changed within the database applying the following:
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements including statements which include several SQL commands separated by “;”, the return value should be ignored
17 / ResponseRows / A count of the rows which are returned by the statement response. In the case of multiple result sets, the value is aggregated. Counting is active even if the server NOCOUNT flag is ON (w/o generating load on the server)
18 / ResponseVolume / The volume (in bytes) of data returned by the server. This can also be zero.
19 / ServerDuration / The duration of processing the statement in the server
20 / ConnectionGuid / Identifier for each specific connection (GUID)
21 / SchemaVersion / The version of the Audit log data format (currently 1.1.1.0)
22 / Origin / The layer in which the audit log record was captured. Currently always has the value “TDS”
23 / FragmentIndex / For cases in which the statement is larger than 32,000 characters, the audit log record is split into fragments. The Fragment Index defines the order.
Row Key format details
Definition
The Row key format was defined to enable the newest audit logs to be placed at the beginning of the table (so querying the top 100 records brings the most recent 100 audit logs) and time based server side filtering. This time format is the .NET DateTime. Ticks of 100 nano-seconds from 00:00:00 (midnight), January 1, 0001. The Row Key value uses “Time to max Time” -“Max Time” – “Event Time”. Max Time is 11:59:59 P.M., December 31, 9999, or 3,155,378,975,999,999,999 Ticks
After “Time to Max Time” and the ‘*’ delimiter a globally unique identifier.Is added to ensure the uniqueness of the Row Key value with in the table that may store records of a large number of databases.
Example
We received the following value as the RowKey:
2519864910889184536*d79cd871-96e3-462f-a05f-d6c0f03c4556.
The following PowerShell statement shall extract the Event Time:
PS C:\Users\nadavh> [DateTime]([DateTime]::MaxValue.Ticks - 2519864910889184536)
Wednesday, November 12, 2014 4:28:31 PM
Which is the expected value:
1 | Page