First published on MSDN on Jun 23, 2017
I recently had a case that a customer needed a way to read the blob auditing data from Linux. This was the quickest and easiest way I could think of.
First install msodbcsql following the instructions here
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver...
You will also need to install the pyodbc module.
[code language="python"]import pyodbc
from datetime import datetime, timedelta
##############################################
# Settings, Please edit with your info #
##############################################
#Your server name without .database.windows.net
server_name = ""
#Database name that will do the processing
database_name = ""
#Username and Password for your SQL Azure Database
user_name = ""
password = ""
#The storage account name where your audit data is stored
storage_account_name = ""
#Number of hours of auditing data to query
number_of_hours = 1
##############################################
# End Settings #
##############################################
#Get timestamp based on number_of_hours
timediff = datetime.now() - timedelta(hours = number_of_hours)
#Build connection string
cnxn = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};Server=tcp:'+server_name+'.database.windows.net,1433;Database='+database_name+';Uid='+user_name+'@'+server_name+';Pwd='+password+';Encrypt=yes;TrustServerCertificate=no;Connec
tion Timeout=30;')
cursor = cnxn.cursor()
#Query to fn_get_audit_file function
cursor.execute("SELECT [event_time], [action_id], [succeeded], [session_id], [session_server_principal_name], [server_instance_name], [database_name], [schema_name], [object_name], [statement], [additional_information], [transaction_id],
[client_ip], [application_name], [duration_milliseconds], [response_rows], [affected_rows] FROM sys.fn_get_audit_file('https://"+storage_account_name+".blob.core.windows.net/sqldbauditlogs/"+server_name+"', default, default) WHERE event
_time > '"+timediff.strftime('%Y-%m-%d %H:%M:%S')+"' ORDER BY event_time;")
rows = cursor.fetchall()
#Get column names and print them comma delimited
columns = []
for column in cursor.description:
columns.append(column[0])
print ', '.join(str(x) for x in columns)
#Print data, comma delimited
for row in rows:
print ', '.join(str(x) for x in row)[/code]
