topic Re: Get data from an MS SQL audit file using DB Connect or UF? in Splunk Enterprise

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

mikefg — Thu, 25 Jun 2020 14:07:18 GMT

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?

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

isoutamo — Thu, 25 Jun 2020 14:15:34 GMT

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

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

richgalloway — Thu, 25 Jun 2020 14:20:31 GMT

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.

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

mikefg — Thu, 25 Jun 2020 14:41:47 GMT

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.

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

mikefg — Thu, 25 Jun 2020 14:42:53 GMT

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

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

isoutamo — Thu, 25 Jun 2020 14:48:06 GMT

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

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

mikefg — Thu, 25 Jun 2020 15:24:13 GMT

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

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

isoutamo — Thu, 25 Jun 2020 15:50:14 GMT

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

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

katrey — Thu, 18 Mar 2021 02:02:20 GMT

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);