topic Re: DB Connect 2 millisecond timestamp fields Frankenstien _time in All Apps and Add-ons

DB Connect 2 millisecond timestamp fields Frankenstien _time

phoenixdigital — Tue, 29 Sep 2020 08:00:32 GMT

Hi All,

Seeing some very strange results from a DB input.

[mi_input://SVR-DEV]
connection = SVR-DEV
index = server_application
interval = 43200
max_rows = 10000
mode = tail
output_timestamp_format = YYYY-MM-dd HH:mm:ss
query = Select * from log.log_event_logs
source = dbx2:svr-dev
sourcetype = db:svr
tail_follow_only = 1
tail_rising_column_name = EVENT_LOG_ID
tail_rising_column_number = 4
ui_query_catalog = NULL
ui_query_mode = advanced
ui_query_schema = undefined
ui_query_table = undefined
input_timestamp_column_name = EVENT_TIMESTAMP
input_timestamp_column_number = 11
tail_rising_column_checkpoint_value = 215663
disabled = 1

Now for the interesting part. DB Connect 2 performs the following

"2015-12-30 08:50:10" EVENT_LOG_ID=157931, EVENT_TIMESTAMP=1419889810000, EVENT_TYPE="INVALID_ROLE", EVENT_SEVERITY_CODE="ERROR", EVENT_TEXT="Message authorization failed for id:", EVENT_DETAIL_DATA="A............

Note EVENT_TIMESTAMP=1419889810000

which is a millisecond timestamp of 1419889810.000
which equates to Mon, 29 Dec 2014 21:50:10 GMT
adjusting for timezone +10 hours = Mon, 30 Dec 2014 8:50:10

So the first issue here is DB Connect 2 added a year to the date!

The second issue is _time for this event is 24/11/2015 08:50:10.000!

I can see the time 8:50:10 is right but where did it get 24/11/2015 from???

Further investigation into the event in Splunk 24/11/2015 was the index time.

So does anyone know to resolve this

The timestamp is getting broken in ingestion of the data (adding one year)
_time is a Frankenstein combination of index_time (date) and the timestamp (time)

To be clear no changes have been made to MAX_DAYS_AGO (default 2000 days) and MAX_DAYS_HENCE (default 2 days) so these should be fine with defaults.

The only other thing I can think of would be a props.conf entry for the TIME_FORMAT to specifically tell Splunk to match the output_timestamp_format in the DB input definition. But it is a pretty standard format I would have thought Splunk would recognise it in an instant.

Re: DB Connect 2 millisecond timestamp fields Frankenstien _time

Richfez — Mon, 30 Nov 2015 03:13:52 GMT

Which timestamp do you want to use?

It might help to double-check the "timestamp" section of the Troubleshooting section of DBx2.

Re: DB Connect 2 millisecond timestamp fields Frankenstien _time

phoenixdigital — Tue, 29 Sep 2020 08:00:35 GMT

I thought this line handled setting the timestamp as that column for my input?

input_timestamp_column_name = EVENT_TIMESTAMP

It should be noted that the config I showed above was the result from using the web GUI. I did not try to tweak anything.

It should also be noted the old DBX 1 app handled a millisecond timestamp just fine. So unsure why DB Connect 2 is breaking so spectacularly?

Looking at my old working DB Connect 1 inputs.conf it was

[dbmon-tail://SVR-DEV/SVR-DEV]
host = dev-oracle
index = application
interval = auto
output.format = mkv
output.timestamp = 1
output.timestamp.column = EVENT_TIMESTAMP
query = Select * from log.log_event_logs {{WHERE $rising_column$ > ?}}
sourcetype = db:svr
table = SVR-DEV
tail.rising.column = EVENT_LOG_ID
disabled = 0

Re: DB Connect 2 millisecond timestamp fields Frankenstien _time

phoenixdigital — Mon, 30 Nov 2015 03:35:20 GMT

Also that troubleshooting section you linked to is peppered with DBX 1 config commands. In particular "If your timestamp is not of type datetime/timestamp"

The documentation is wrong or the web GUI config creation when entering timestamp columns is wrong. Either way that is pretty poor form for a troubleshooting page having a red herring in there like that.

Re: DB Connect 2 millisecond timestamp fields Frankenstien _time

Rjaramillom — Tue, 05 Jan 2016 17:02:36 GMT

Try yyyy instead of YYYY. The fomat YYYY is weakyear, which can give weird results

Re: DB Connect 2 millisecond timestamp fields Frankenstien _time

evinasco — Tue, 05 Jan 2016 17:05:26 GMT

Dude

please try yyyy instead of YYYY in your inputs

regards