DBX: Tail from SQL Server missing data

[Dev999]

DBX Tail input with bigint or datetime type rising column to SQL Server 2008. The initial loading would get all the data. But afterwards most data were missed in index. The dbx.log shows resultCount coming in but only a very small fraction (~10%) of data showed in index. It looks working - no error in dbx.log or anywhere else, and the data showed in index is correct. I reduced the columns to only a few and no special characters in data and not much difference between the saved and the missed. If the rising column is an integer primary key (identity) then no problem on the same system. Unfortunately some of my source tables do not have integer primary key. The data load in the source database is very light - could be less than 1000 an hour.

I am pretty sure many out there got it working but I could not figure out the cause and solution.

Splunk version 6.01 and DBX 1.1.3 on Linux VM. Java has 1024 Mb memory. Tried different intervals and batch sizes and no difference.

[Dev999 · Accepted solution]

For the benefit of the community I put this as an answer to my own question.

This most likely is a bug in Splunk DB Connect (DBX) 1.1.3. I was able to reproduce the issue on several systems and against several SQL SERVER 2008. I ended up creating two inputs that only differ in target index name and rising column: int and datetime. The SQL is simple and only select a few small columns. Again int as rising column worked fine but datetime only gave partial data after initial loading. No error in dbx.log in both cases. Tweaking parameters like interval, batch size, etc. would not help. I did not want to test bigint again as it was the initial problem I had.

If I make an int column by converting the bigint timestamp to int, CONVERT(int, dt/1000), it works but it still has potential of losing data as by conversion precision is lost. In addition, it takes a performance hit as now the where clause is essentially a function. For a small table like mine it works ok so far.

[sroback_splunk]

Hi. If you're rising column is a date, you might try wrapping the checkpoint parameter in a "to_date" construct. For example:

{{AND $rising_column$ > to_date (?,'YYYY-MM-DD"T"HH:MI:SS')}}

For more info on this, see this doc:
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#Configure_inpu...

[Dev999]

I had no problem with date setup. convert(datetime,?) is the SQL Server's version of Oracle's to_date(). Without proper construct I would not get partial data. Also, bigint type should not need any conversion. Both give partial data in index even though dbx shows more resultCount. I do not get any error in dbx.log. However, on the same Splunk server, an input with integer primary key type rising column works fine - without any missing data.