topic Re: Splunk DB Connect -- Checkpoint Set To Null in All Apps and Add-ons

Splunk DB Connect -- Checkpoint Set To Null

yujietay — Tue, 29 Sep 2020 16:32:19 GMT

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?

Re: Splunk DB Connect -- Checkpoint Set To Null

yujietay — Thu, 02 Nov 2017 04:31:43 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

peterchenadded — Thu, 02 Nov 2017 07:42:37 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

yujietay — Thu, 02 Nov 2017 08:36:00 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

peterchenadded — Thu, 02 Nov 2017 21:21:00 GMT

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.

Re: Splunk DB Connect -- Checkpoint Set To Null

yujietay — Fri, 03 Nov 2017 01:35:55 GMT

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.

Re: Splunk DB Connect -- Checkpoint Set To Null

jay_morris — Tue, 29 Sep 2020 17:40:20 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

yujietay — Wed, 17 Jan 2018 02:31:58 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

jay_morris — Wed, 17 Jan 2018 21:58:52 GMT

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.

Re: Splunk DB Connect -- Checkpoint Set To Null

jay_morris — Wed, 17 Jan 2018 22:08:30 GMT

I hope that helps.

Re: Splunk DB Connect -- Checkpoint Set To Null

yujietay — Tue, 29 Sep 2020 17:40:42 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

nick405060 — Wed, 09 Oct 2019 18:02:36 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

nick405060 — Thu, 10 Oct 2019 19:42:17 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

nick405060 — Thu, 10 Oct 2019 19:42:55 GMT

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

Re: Splunk DB Connect -- Checkpoint Set To Null

jplasencia — Tue, 08 Oct 2024 18:43:38 GMT

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.