Lesson Learned #24: How to read or import Azure SQL Auditing Blob file?

First published on MSDN on Mar 21, 2017
As probably, you know we have two different types for Azure SQL Auditing : Table or Blob. For table type we have several ways to read this Excel , PowerBI , etc..

In this post, we are going to talk about how to read or import the file generated by blob type. When we configured this type, the file generated has the extension XEL that means that will be an Extended Event file. So, you have the option to open the file directly using your SQL Server Management Studio and also, you have sys.fn_xe_file_target_read_file function. For example, running this statement, SELECT CONVERT(XML,event_data) as xml, * FROM sys.fn_xe_file_target_read_file('https://xxxxxx.blob.core.windows.net/sqldbauditlogs/server/database/SqlDbAuditing_ServerAudit_NoRetention/2017-03-17/23_34_35_412_0.xel', null, null, null) I would be able to read all audit rows.

A very good advantage is that we are able to read the data directly from the blob storage without download to our local machine.

Enjoy it!