Get data from an MS SQL audit file using DB Connect or UF?

[mikefg]

I have an MS SQL server writing audit data to a .sqlaudit file. I need to get this data into Splunk. I have DB Connect installed, but I'm not sure how to ingest the .sqlaudit file data. Do I use DB Connect or the UF?

[richgalloway]

DB Connect is for extracting data directly from a database, much as you would use a SQL client to run queries. If you can write a query to read the .sqlfile then DBX should be able to read it, too.
As I understand it, .sqlaudit files are not text-based so they can't be onboarded as-is.

[mikefg]

Please see my reply above. The audit files are being created per Splunk instructions, but how do I get them into Splunk?

[isoutamo]

Those should be a “regular” text files, (I haven’t any MS SQL server on my hands) so just use UF as any other files.

ismo

[mikefg]

Using the MS SQL addon I see this reference to audit logs 'uses the sys.fn_get_audit_file function via DB Connect.'
https://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/Datatypes

[isoutamo]

If I recall right there are two ways to get audit events from MS SQL. First way is that export to text file and second way is direct access / queries from DB.
R. Ismo

[isoutamo]

Hi

here is old answers https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-collect-SQL-audit-data/td-p/350002 

r. Ismo

[mikefg]

Thank you, I have been through this article and either I'm missing something or the article is missing something.

Following the steps to 'Create audit objects in Microsoft SQL Server for the Splunk Add-on for Microsoft SQL Server' I now have audit files being written to disk.

CREATE SERVER AUDIT MSSQL_Database_Audit TO FILE ( FILEPATH = 'C:\\SQLAudit' ) ;

 However, I do not see steps in the article for how to get the file data into Splunk.

[katrey]

1. create an identity in splunk with an account that has access to SQL Server
- account will require server Control (USE master;  GRANT CONTROL SERVER TO SplunkUSER;)
2. Create a new connection using identity created in step1
3. Create a data lab and specify your Connection created in step 2.
     In the data lab you specify your query
     SELECT * FROM sys.fn_get_audit_file ('\\\<servername>\<sharename>\*.sqlaudit',null,null);