All Apps and Add-ons

Splunk DB Connect -- Checkpoint Set To Null

yujietay
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 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?

1 Solution

jay_morris
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

jay_morris
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

jay_morris
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

nick405060
Motivator

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

0 Karma

nick405060
Motivator
0 Karma

nick405060
Motivator

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

0 Karma

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

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

0 Karma

yujietay
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

yujietay
Path Finder

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

0 Karma

peterchenadded
Path Finder

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

0 Karma

yujietay
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

peterchenadded
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

yujietay
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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!