All Apps and Add-ons

DB Connect 2 millisecond timestamp fields Frankenstien _time

phoenixdigital
Builder

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

  1. The timestamp is getting broken in ingestion of the data (adding one year)
  2. _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.

Tags (1)
0 Karma

evinasco
Communicator

Dude

please try yyyy instead of YYYY in your inputs

regards

0 Karma

Rjaramillom
Engager

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

0 Karma

Richfez
SplunkTrust
SplunkTrust

Which timestamp do you want to use?

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

0 Karma

phoenixdigital
Builder

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
0 Karma

phoenixdigital
Builder

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...