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)
This post first appeared on MSDN Blogs | Get The Latest Information, Insights, Announcements, And News From Microsoft Experts And Developers In The MSDN Blogs., please read the originial post: here