Monitoring MS SQL logs from Windows Event viewer

[akshatj2]

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.

[hughkelley]

@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]

Also,

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

[jaredthomason]

Does that regular expression work for you?

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

 I have not been able to get it to work. 

[jaredthomason]

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

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

[chans28]

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.

[hughkelley]

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:

[chans28]

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.

[hughkelley]

Did you happen to share that addon anywhere?

[hughkelley]

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

[hectorvp]

Hi @hughkelley ,

How did you resolve this issue, any idea?