Splunk Enterprise

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

mikefg
Communicator

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?

Labels (1)
Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust
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.
---
If this reply helps you, Karma would be appreciated.
0 Karma

mikefg
Communicator

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

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
Communicator

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

SNAG-0000 6-25-2020.png

 

0 Karma

isoutamo
SplunkTrust
SplunkTrust
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
0 Karma

isoutamo
SplunkTrust
SplunkTrust
0 Karma

mikefg
Communicator

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.

Tags (1)
0 Karma

katrey
New Member

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

0 Karma
Get Updates on the Splunk Community!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...