All Apps and Add-ons

Splunk Add-on for MIcrosoft SQL Server and DB Connect 2: How to extract and index mssql:audit "additional information" field?

banderson7
Communicator

We're polling an audit file from our SQL server, that includes a field called additional information. This field has a field inside it:

<address>field</address>

that I need to be indexed. I may have done something wrong in setting up the input, because I kind of expected this to be an indexed field from the beginning.

This is the input:

[mi_input://mssql:audit]
connection = SQLServer
index = main
interval = 60
max_rows = 10000
mode = tail
output_timestamp_format = YYYY-MM-dd HH:mm:ss
query = SELECT * FROM sys.fn_get_audit_file ('M:\\\\AuditFiles\\\\*',default,default)
source = dbx2
sourcetype = mssql:audit
tail_follow_only = 1
tail_rising_column_name = event_time
tail_rising_column_number = 1
ui_query_mode = advanced
disabled = 0
tail_rising_column_checkpoint_value = 1449605957973`

and this is the result:

"2015-12-08 11:15:19" event_time=1449609223316, sequence_number=1, action_id="LGIS", succeeded=1, permission_bitmask=## NOT SUPPORTED TYPE ##, is_column_permission=0, session_id=65, server_principal_id=274, database_principal_id=0, target_server_principal_id=0, target_database_principal_id=0, object_id=0, class_type="LX", session_server_principal_name="xxxx\svcSQLxxxx", server_principal_name="xxxx\svcSQLxxxx", server_principal_sid=## NOT SUPPORTED TYPE ##, target_server_principal_sid=## NOT SUPPORTED TYPE ##, server_instance_name="xxxxxxxxxx", statement="-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed", additional_information="<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><pooled_connection>1</pooled_connection><client_options>0x28000020</client_options><client_options1>0x0001f438</client_options1><connect_options>0x00000000</connect_options><packet_data_size>8000</packet_data_size><address>local machine</address><is_dac>0</is_dac></action_info>", file_name="M:\AuditFiles\Audit_Logins_Fail_Success_Log_42C90784-3268-445A-94B3-4CD7D392B997_0_130934732017490000.sqlaudit", audit_file_offset=191027200, user_defined_event_id=0`
0 Karma

jamesarmitage
Path Finder

Have you tried to create a search query that gives you what you need? Your best tools are probably spath or xpath in this case. Something like:

sourcetype=mssql_audit | spath output=action_info_address path=action_info.address | table action_info_address

Xpath syntax is similar, but not exactly the same.

Once you have a working extraction at search time, you should be able to create a calculated field in your props.conf so it's indexed ahead of time.

0 Karma

banderson7
Communicator

I see that it should be working, but it's not. The field isn't extracting.
http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/spath

<action_info
    xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data">
    <pooled_connection>1</pooled_connection>
    <client_options>0x28000020</client_options>
    <client_options1>0x0001f438</client_options1>
    <connect_options>0x00000000</connect_options>
    <packet_data_size>8000</packet_data_size>
    <address>local machine</address>
    <is_dac>0</is_dac>
</action_info>", file_name="M:\AuditFiles\Audit_Logins_Fail_Success_Log_42C90784-3268-445A-94B3-4CD7D392B997_0_130934732017490000.sqlaudit", audit_file_offset=223147008, user_defined_event_id=0

But the action_info_address field isn't extracted for some reason.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...