Reading SQL Server .sqlaudit Files Stored in S3
I set up an audit in SQL Server that saves audit logs (.sqlaudit files) to RDS, which we then copy over to S3 using a standard process. All of the documentation I can find on reading these audit files uses the following query to read them from RDS:
SELECT * FROM msdb.dbo.rds_fn_get_audit_file ('D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit' ,default,default)
RDS has an option setting called RETENTION_TIME for SQL Server Audit which allows you to specify a maximum of 840 hours before the files are removed from the RDS instance. I need to to be able to read the audit files for a longer period of time, so I am looking for a way to query the audit files in S3 instead, where they are retained.
One way may be to use sys.fn_get_audit_file, but I cannot do this with any user attached to this server that my organization has access to, even the admin user. None of the users have proper permissions for this and I don't see any way they can be granted.
With the .sqlaudit files sitting in the S3 bucket, how could I go about reading the files? Perhaps S3 Select could do it somehow, or maybe there's another obvious solution I'm overlooking?
Depending on the format of the audit file you could query it directly with https://aws.amazon.com/athena/faqs/ from S3 if it’s a supported format. If the format isn’t directly supposed by AWS Athena you can use https://aws.amazon.com/glue/faqs/ to do a transformation as some of the answers on https://stackoverflow.com/questions/55284580/how-to-query-nested-xml-file-in-aws-athena-via-glue suggest.