All Apps and Add-ons

Splunk DB Connect -- Checkpoint Set To Null

Path Finder

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 splunkappdbconnectserver.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?

1 Solution

Explorer

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.

View solution in original post

Explorer

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.

0 Karma

Explorer

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.

View solution in original post

Motivator

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

0 Karma

Motivator
0 Karma

Motivator

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

0 Karma

Path Finder

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 fntracegettable('C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Log\\log.trc',default)
WHERE StartTime > ?
ORDER BY StartTime ASC

Explorer

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/splunkappdb_connect/ file the checkpoint value is "null". TIA

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma

Path Finder

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.

0 Karma

Path Finder

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.

0 Karma