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

[banderson7]

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`

[jamesarmitage]

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.

[banderson7]

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.