Example Python Program Reading SQL Azure Blob Auditing Data


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-for-sql-server

You will also need to install the pyodbc module.

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)
Comments (0)

Skip to main content