How do I CIM tag SQL server audit logs read by the DB Connect application?

[gjanders]

This is actually a question I already the answer for, I just want to use the question/answer style to ensure it complies to the way this forum is setup.

This is how I achieved the CIM compliance for the SQL server audit logs that were read in via the database using the DB Connect application for Splunk.

Please see the answer for the solution information, hopefully this will be available in a future version of the application.

[shadfa_splunk]

Hi @gjanders,

We have started development for the upcoming release of MS SQL and part of this we are planning to include this request as well.

In order to verify the approach suggested and implement mapping for the same in add-on, we need sample events with the mentioned action_id. Can you please share the sample events for the above action_id mentioned? 

Thanks

[gjanders]

Unfortunately I've since moved employers and don't have the logs handy anymore

[jcoates_splunk]

Hi, it's not entirely clear to me what's going on here... Bug, story, or other?

Are you saying that the Splunk Add-on for Microsoft SQL Server didn't gather and tag the data from the audit log files per its intention at http://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/Datatypes ?

Or are you providing an alternate approach to tag the audit data? Did you change the SQL statement? I've seen a lot of MS-SQL administrators provide a custom view of audit information instead of allowing direct audit log access, is that what you're dealing with?

What version of Microsoft SQL Server are we talking about?

[gjanders]

The add on does not tag audit logs as per it's intention. Ingestion of the log themselves is relatively easy, however the props.conf, eventtypes.conf and tags.conf entries are non-existent for the audit data sourcetype!

The transforms.conf are optional, you can obtain the same information by using an inner join on the SQL query on MS SQL Server (which is a change to what the application provides) however a lookup makes more sense as you do not need to index a repetitive field.

[gjanders · Accepted solution]

This is the props.conf I used to achieve CIM compliance:

props.conf
[mssql:audit]
LOOKUP-action = sqlserver_audit_actions_lookup action_id OUTPUT action_name
LOOKUP-classtype = sqlserver_audit_class_type_map_lookup class_type OUTPUT class_type_desc, securable_class_desc
FIELDALIAS-mssqlaudit = object_name AS object server_principal_name AS user source AS dest statement AS query
EVAL-action = if(action_id=="LGFL" OR action_id=="LGIF" OR action_id=="LGIS" OR action_id=="LGO" OR action_id=="LO" OR action_id=="LGSD",if(succeeded==1,"success","failure"),null())
EVAL-status = if(action_id!="LGFL" AND action_id!="LGIF" AND action_id!="LGIS" AND action_id!="LGO" AND action_id!="LO" AND action_id!="LGSD",if(succeeded==1,"success","failure"),null())

transforms.conf
[sqlserver_audit_actions_lookup]
filename = dm_audit_actions.csv

[sqlserver_audit_class_type_map_lookup]
filename = dm_audit_class_type_map.csv

These files consist of :
select class_type,class_type_desc,securable_class_desc from sys.dm_audit_class_type_map
and
select DISTINCT action_id,name from sys.dm_audit_actions

Note that I renamed the “name” column to action_name in the lookup file! I then dumped the results of the queries and the headers into the lookup file named mentioned in the transforms.conf files. This was to reduce the amount of data sent in by the SQL queries...

eventtypes.conf
[mssql_audit_log_auth]
search = sourcetype=mssql:audit action_id=="LGFL" OR action_id=="LGIF" OR action_id=="LGIS" OR action_id=="LGO" OR action_id=="LO" OR action_id=="LGSD"

[mssql_audit_log_change]
search = sourcetype=mssql:audit action_id!="LGFL" AND action_id!="LGIF" AND action_id!="LGIS" AND action_id!="LGO" AND action_id!="LO" AND action_id!="LGSD"

tags.conf
[eventtype=mssql_audit_log_auth]
authentication = enabled

[eventtype=mssql_audit_log_change]
change = enabled