I'm currently working in an environment that requires we enable SQL Server Audit, enterprise-wide. We generate
.sqlaudit files as we have retention and access guidelines, and I'm finding that we need to compile trending information about the files generated, such as number created per hour, total volume of audit information, etc. Currently, I'm only aware of DMVs/DMFs that provide audit definition information (the list can be found here), but nothing related to the output of the SQL Server Audit process.
I'm currently running PowerShell scripts to collect this information from the metadata of the
.sqlaudit files, but I'd prefer not to rely on an external process, if possible. I've looked into extended stored procedures such as
xp_dirtree, but that only returns the number of files and not size information, so it doesn't provide as complete a picture as I need here.
xp_cmdshell, while viable, is not allowed for obvious security reasons and because NIST compliance explicitly prohibits its use.
Are there any approaches within the database engine (I'm asking about things I can hit with TSQL) where I can get this information, such as an Extended Event or hidden DMV I've not yet come across? I feel like I've done some extensive digging, but I'm never surprised if I miss something.