Splunk DB Connect -- Checkpoint Set To Null

[yujietay]

I am trying to collect SQL Trace logs using Splunk DB Connect 3.1.1.
I am currently using the Splunk Add-On for Microsoft SQL Server's mssql:trclog template for the query.
I am currently using StartTime as the rising column.
However, after applying the settings, I am only able to collect 2000 events once, and it stopped collecting, even when I set the frequency to 60 seconds.
Upon searching the logs in splunk_app_db_connect_server.log, I realized that the checkpoint is set to null after the first collection.

2017-11-01 16:58:50.447 +0800  [QuartzScheduler_Worker-27] INFO  c.s.d.s.dbinput.task.DbInputCheckpointRepository - action=load_checkpoint_from_cache checkpoint=Checkpoint{value='2017-11-01 16:48:00.000', appVersion='3.1.1', columnType=93, timestamp='2017-11-01T16:58:32.010+08:00'}
2017-11-01 16:58:50.447 +0800  [QuartzScheduler_Worker-27] INFO  c.s.d.s.dbinput.task.DbInputCheckpointRepository - action=load_checkpoint_from_cache checkpoint=Checkpoint{value='2017-11-01 16:48:00.000', appVersion='3.1.1', columnType=93, timestamp='2017-11-01T16:58:32.010+08:00'}
2017-11-01 16:58:50.634 +0800  [QuartzScheduler_Worker-27] INFO  c.s.d.s.dbinput.task.DbInputCheckpointRepository - action=dump_checkpoint file=C:\Program Files\Splunk\var\lib\splunk\modinputs\server\splunk_app_db_connect\sqltrace value={"value":null,"appVersion":"3.1.1","columnType":93,"timestamp":"2017-11-01T16:58:50.447+08:00"}
2017-11-01 16:58:50.712 +0800  [QuartzScheduler_Worker-27] INFO  c.s.d.s.dbinput.task.DbInputCheckpointRepository - action=save_checkpoint_success checkpoint=Checkpoint{value='null', appVersion='3.1.1', columnType=93, timestamp='2017-11-01T16:58:50.447+08:00'}

May I know why is this happening?

[jplasencia]

I ran into this issue today and came upon this post. The error I was getting was "Non-Displayable Column Type BINARY" for several columns in my select query. As it turns out, you can modify your select query to cast columns as different types in MySQL, which I did and it solved my issue

SELECT
     CAST(field_name AS CHAR) AS field_name,
FROM
     table_name

I hope this is helpful to anyone else who encounters the same issue.

[jay_morris]

So it looks like it is a known bug when returning non-indexable columns. I found it in the known issues section under the release notes for 3.1.1 (DBX-4454). The work around is to remove those columns that contain the binary data. If you run your test query while editing inputs you should see some values like "Non-Displayable Column Type image". Remove those columns and your checkpoint will update.

I hope that helps.

[jay_morris · Accepted solution]

So it looks like it is a known bug when returning non-indexable columns. I found it in the known issues section under the release notes for 3.1.1 (DBX-4454). The work around is to remove those columns that contain the binary data. If you run your test query while editing inputs you should see some values like "Non-Displayable Column Type image". Remove those columns and your checkpoint will update.

I hope that helps.

[nick405060]

What if I want to index the column containing the binary data?

[nick405060]

Or do this https://answers.splunk.com/answers/775568/can-splunk-index-gzipd-xmls-from-a-sql-database.html

[nick405060]

I've been told if this is the case... #cribl

[yujietay]

Thanks jay_morris, that works!

I have removed the columns, and this is my query:
SELECT ApplicationName, BigintData1, BigintData2, CPU, ClientProcessID, ColumnPermissions, DBUserName, DatabaseID, DatabaseName, Duration, EndTime, Error, EventClass, EventSequence, EventSubClass, FileName, GUID, GroupID, Handle, HostName, IndexID, IntegerData, IntegerData2, IsSystem, LineNumber, LinkedServerName, LoginName, MethodName, Mode, NTDomainName, NTUserName, NestLevel, ObjectID, ObjectID2, ObjectName, ObjectType, Offset, OwnerID, OwnerName, ParentName, Permissions, ProviderName, Reads, RequestID, RoleName, RowCounts, SPID, ServerName, SessionLoginName, Severity, SourceDatabaseID, StartTime, State, Success, TargetLoginName, TargetUserName, TextData, TransactionID, Type, Writes, XactSequence
FROM fn_trace_gettable('C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Log\\log.trc',default)
WHERE StartTime > ?
ORDER BY StartTime ASC

[jay_morris]

I know this is a bit of an older thread but was wondering if you were able to find a resolution for this issue? I am currently experience the same issue with collecting trace file logs via DB Connect. I get one good pull and then nothing. After reviewing our ../splunk/var/lib/splunk/modinputs/server/splunk_app_db_connect/ file the checkpoint value is "null". TIA

[yujietay]

I have yet to find a resolution to this, but I just submitted a case to Splunk recently. Hopefully this will be resolved soon.

[yujietay]

I changed my rising column to use EventSequence, but it still does not work.

[peterchenadded]

Have you added order by timestamp as part of your SQL query?

[yujietay]

Hi peterchenadded,

Yes, the query goes like this:
SELECT *
FROM fn_trace_gettable('C:\\\\Program Files\\\\Microsoft SQL Server\\\\MSSQL11.MSSQLSERVER\\\\MSSQL\\\\Log\\\\log.trc',default)
WHERE StartTime > ?
ORDER BY StartTime ASC


[peterchenadded]

Can you check the format of StartTime? To make sure they are all valid and consistent. DBX might be failing to parse an unexpected StartTime value.

[yujietay]

The format of StartTime is datetime, which DBX should be able to parse. I also tried the same thing for EventSequence, which is bigint format.