Search code examples
azureazure-sql-databaseazure-storage

How are Azure SQL Audit blob file names generated?

I have Azure SQL Audit enabled on one of my databases. I would like to be able to automate a process whereby the contents of the file(s) for a given day are stored in a SQL table. However, to do that, I need to know the names of the blob files to process for a given day. For example in my blob folder I see the following:

enter image description here

In order for me to automate this process, I need to know the files that are there, and what they are named. How would I go about doing this? I will probably be using T-SQL to process these files.

Solution

  • Blob filename format: CreationTime_FileNumberInSession.xel

    • CreationTime – time of day in UTC (hh_mm_ss_ms format)
    • FileNumberInSession – a running index in case session log spans across multiple Blob files

    As you can see, the file names are random as they are based on the time of day when they were created.


    Audit blob files are stored in the following directories hierarchy within the "sqldbauditlogs" container (‘/’ delimiter signifies directory hierarchy):

    <ServerName>/<DatabaseName>/<AuditName>/<CreationDate>/


    To process all logs for a given day, simply collect all blobs in the relevant ../<CreationDate>/ hierarchy.


    Thanks,

    Gilad (MSFT)