Splunk DB Connect 2: Why is Splunk unable to consume some field timestamp results?

[imanpoeiri]

Hi Experts,

Apparently, I noticed this issue while using SPLUNK DB Connect 2 with behavior as following:

1. Use DB Connect 2 to poll the data
2. SQL as follows: select order_id, date_created, to_char(date_created, 'mm/dd/yyyy hh24:mi:ss') as dt_created, to_date(last_updated, 'mm/dd/yyyy hh24:mi:ss') as last_upd_dt, status from table_name where last_updated > sysdate -1
3. I choose column date_created as DB Connect 2 will read this returned field as epoch time.
4. Selected timeformat YYYY-MM-dd HH:mm:ss
5. And finalizing the setup with cron job and the stuffs.
6. Practice above was gave me approx 99 mb data that I can notice from the index size on the first polling.

When I do data verification by use of following search formula, I noticed not all _time consumed the date_created that I chose earlier.
Here is the search sample to validate:
index=sample_index | table _time, date_created, dt_created

This issue causes deviations for most of the panels/charts that I created earlier, and to fix the dashboard as the workaround is not a good choice as most of the time pickers would not work at all, and it will ended in extra cost of extended development and code maintenance.

My question is: Is there anyone was had the same issue as above? If so did you manage to get the workaround to fix the issue? How?

Or if the developer of SPLUNK DB Connect 2 reads this, could you confirm this on your side and tell us what should we do to fix that issue?

Thanks a lot and appreciate on your responses.

[einkebil]

I see sometimes the same kind of behaviour and same kind of solution. To complement @imanpoerir suggestion

[SOURCETYPE]
MAX_TIME_STAMP_LOOKAHEAD=0
TIME_PREFIX=dt_created="

just because your time start 0 character after your TimePrefix. beware to have time in an manageable order for splunk (and adjust your sourcetype time recognition accordingly).
( http://docs.splunk.com/Documentation/Splunk/latest/Admin/propsconf )

[jcoates_splunk]

I don't know what you mean by "not all _time consumed the date_created that I chose earlier." Splunk can't make an event without a _time, so are you saying that you've got events with the wrong time, duplicated events, or missing events?

Possible sources of error: you're using a time-based filter in your SQL (where last_updated > sysdate-1). If that's on a different scale than your events, you could miss some events (IOW, if events happen on microseconds but query is on seconds). Then there's timezone problems and daylight savings problems... I see you've got an order id, but updates, so I assume that the data allows multiple events to happen to a single order. Is there an event id column? That would be a good candidate for rising column, which would make this a simpler tail input and avoid the resolution problem.

[imanpoeiri]

Hi @jcoates,

I've got events with the wrong time. where some of the _time use the extraction time rather than follow the date_created time.

Apparently we managed to find the solution by added the TIME_PREFIX on the props.conf by declare with the field name to it, so it looks like below more/less.

[SOURCETYPE]
MAX_TIME_STAMP_LOOKAHEAD=20
TIME_PREFIX=dt_created="

[jcoates_splunk]

That indicates to me that DB Connect and/or Splunk are not figuring out that you want _time to be based on date_created... I'd double-check the input that DB Connect created, if you asked for _time to be generated from date_created and it isn't happening, you might be looking at a bug.