topic Re: Monitoring MS SQL logs from Windows Event viewer in Splunk Enterprise Security

Monitoring MS SQL logs from Windows Event viewer

akshatj2 — Mon, 03 Sep 2018 13:20:59 GMT

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.

Re: Monitoring MS SQL logs from Windows Event viewer

hughkelley — Thu, 24 Oct 2019 20:39:40 GMT

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

Re: Monitoring MS SQL logs from Windows Event viewer

chans28 — Thu, 24 Oct 2019 20:53:29 GMT

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

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-and-server-audit-specification?view=sql-server-2017

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

Re: Monitoring MS SQL logs from Windows Event viewer

hughkelley — Thu, 24 Oct 2019 20:59:56 GMT

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:

Re: Monitoring MS SQL logs from Windows Event viewer

chans28 — Wed, 30 Sep 2020 02:41:54 GMT

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.

Re: Monitoring MS SQL logs from Windows Event viewer

hughkelley — Wed, 18 Dec 2019 17:07:50 GMT

Did you happen to share that addon anywhere?

Re: Monitoring MS SQL logs from Windows Event viewer

hectorvp — Fri, 30 Oct 2020 06:04:04 GMT

Hi @hughkelley ,

How did you resolve this issue, any idea?

Re: Monitoring MS SQL logs from Windows Event viewer

hughkelley — Fri, 30 Oct 2020 11:20:42 GMT

@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

Re: Monitoring MS SQL logs from Windows Event viewer

jaredthomason — Thu, 22 Jul 2021 18:36:10 GMT

Does that regular expression work for you?

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

I have not been able to get it to work.

Re: Monitoring MS SQL logs from Windows Event viewer

jaredthomason — Thu, 22 Jul 2021 20:08:59 GMT

Also,

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

Re: Monitoring MS SQL logs from Windows Event viewer

jaredthomason — Tue, 27 Jul 2021 14:19:36 GMT

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

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