Ok so I have now got it to work and on the way learnt a few things:
1) The GUI doesn't work well in some cases - you are better off editing the inputs.conf file directly
2) The timestamp column doesn't work as the first column returned by the SQL query (at least for me) but does work as the last
3) When debugging timestamp issues with lots of data, try reducing the batch size or do what I had to and add a new field to your SQL query each time so you can recognise the data in Splunk (Select 'first' AS Test). I initially had 10,000 batch size and data spread over several months so it was hard to see where my changes ended up.
4) Restart splunk after each change - for me this flushed the events in the processing queues. It turns out earlier changes HAD worked but the data I was seeing was still the previous batches being processed.
My config which works:
[mi_input://PROD]
connection = PROD
index = test_test
input_timestamp_column_name = st
input_timestamp_column_number = 33
interval = 100
max_rows = 1000
mode = tail
output_timestamp_format = yyyy-MM-dd HH:mm:ss.SSS
query = select 'first' AS Test,EventSequence,TextData,DatabaseID,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,Duration,StartTime,EndTime,Reads,Writes,CPU,RowCounts,Permissions,Severity,Success,EventClass,EventSubClass,ObjectID,ObjectType,ObjectName,DatabaseName,FileName,OwnerName,RoleName,TargetUserName,DBUserName,ParentName,SessionLoginName, CONVERT(varchar,StartTime,121) AS st from fn_trace_gettable ('D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\tracefile.trc',1) where StartTime IS NOT NULL AND EventSequence IS NOT NULL AND EventClass!=14 AND EventClass!=15
source = audit
sourcetype = audit
tail_follow_only = 1
tail_rising_column_name = EventSequence
tail_rising_column_number = 1
ui_query_mode = advanced
tail_rising_column_checkpoint_value = 3429579512
input_timestamp_format = yyyy-MM-dd HH:mm:ss.SSS
... View more