Splunk Enterprise Security

Monitoring MS SQL logs from Windows Event viewer

akshatj2
Path Finder

Hi All,

We need to integrate MS SQL logs with Splunk. The current default add-on supports logs via DB Connect but we do not have database connectivity directly. Rather, all the logs are written in Application logs for Windows Event viewer with most of the details in the Message field.

Currently all the fields are not being parsed, please suggest how can we integrate the same.

0 Karma

hughkelley
Path Finder

@hectorvp,  we never fully got it working.  

This is where we ended up.   The idea was to extract the SQL detail into the zzz field and then split it out from there.  I'm still trying to remember what didn't work about this but can't find my notes. 

https://regex101.com/r/vrb7OK/1

#inputs.conf
renderXml = true


#props.conf
[SQL-Audit-via-xml]
# search time extractions
EXTRACT-xmleventdata = \<Data\>(?<zzz_sql_audit_data>.+?)\<\/Data\>
REPORT-000 = wel-col-kv-sqlaudit-atsearch

#transforms.conf
[wel-col-kv-sqlaudit-atsearch]
SOURCE_KEY = zzz_sql_audit_data
REGEX      = ([a-z_]+):(.*?)[\r\n]+
FORMAT     = $1::$2

 

jaredthomason
Explorer

Also,

Which .conf files are you editing? Is it in a specific app on the SQL server or an app on the indexer?

0 Karma

jaredthomason
Explorer

Does that regular expression work for you?

\<Data\>(?<zzz_sql_audit_data>.+?)\<\/Data\>

 I have not been able to get it to work. 

Tags (1)
0 Karma

jaredthomason
Explorer

I had to change the first regular expression to the following.

\<Data\>(?<zzz_sql_audit_data>[^*]*)\<\/Data\>

0 Karma

chans28
Explorer

We had the same challenge here is what we ended up doing:

  1. Get your DBA to deploy database and server audit objects for the events you want to monitor.

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-an...

  1. Have those events sent to Win Event Log under Application
  2. Deploy a UF on the SQL servers to collect all the Win Event Logs.
0 Karma

hughkelley
Path Finder

We can get the logs into Splunk. I'm particularly asking about the peculiar log format of the audit data.

Audit event: audit_schema_version:1
event_time:2019-10-23 20:57:13.1337990
sequence_number:1
action_id:LGIF
succeeded:false
is_column_permission:false
session_id:0
server_principal_id:0
database_principal_id:0
target_server_principal_id:0
target_database_principal_id:0
object_id:0
user_defined_event_id:0
transaction_id:0
class_type:LX
duration_milliseconds:0
response_rows:0
affected_rows:0
client_ip:10.99.99.999
permission_bitmask:00000000000000000000000000000000
sequence_group_id:954EDDBB-F5A2-45B2-AAC5-2E9797EC3859
session_server_principal_name:
server_principal_name:iamfake
server_principal_sid:
0 Karma

chans28
Explorer

Oh I see. My apologies. So we had to throw away the SQL Server addon and just build our own CIM compliant addon. We used action_id, class_type and succeeded to map to Authentication CIM.

0 Karma

hughkelley
Path Finder

Did you happen to share that addon anywhere?

0 Karma

hughkelley
Path Finder

Same challenge here, I had this add-on would have parsing rules for SQL audit data in Windows Event Logs.

https://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/Datatypes

0 Karma

hectorvp
Communicator

Hi @hughkelley ,

How did you resolve this issue, any idea?

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!