topic Re: Error event time (one more year) in Getting Data In

Error event time (one more year)

lcblucas — Sun, 25 Oct 2015 22:06:56 GMT

Hi all,

In DB Input of DB CONNECT, inside PARAMETERS, I configured to CHOOSE COLUMN on timestamp, instead default option (CURRENT INDEX TIME), and select my column that have a date.

When I go on search page, any event are one more year. For example: The correct date of event is 29/12/2014, but the splunk event show 29/10/2015 (December of this year).

Does anybody can help me, please?

Thanks!

Re: Error event time (one more year)

Richfez — Mon, 26 Oct 2015 01:16:52 GMT

Could you post one full row of your database as dbquery would display it?

And also can you post one full event as Splunk has it? It would be awesome if you could paste in the same event/row!

Then last, let us know which column you used as your time stamp column.

Thanks!

Re: Error event time (one more year)

lcblucas — Mon, 26 Oct 2015 14:51:30 GMT

Hi Rich7177!

Thanks for your attention! Below the image links for your requests:

02 files (db query and event search) of incorrect date
- http://imageshack.com/a/img908/4213/H77my6.png
- http://imageshack.com/a/img911/7484/OWugur.png

02 files (db query and event search) of correct date
- http://imagizer.imageshack.us/a/img903/6195/g8Sdtq.png
- http://imageshack.com/a/img908/1958/ty4va3.png

01 file of "set parameters" inside DB Ipunt configuration.
- http://imageshack.com/a/img910/7704/JD28WZ.png

Let me know if you need another data.

Thanks again for your attention!

Luis Carlos
Skype: lcb.lucas

Re: Error event time (one more year)

Richfez — Mon, 26 Oct 2015 15:03:05 GMT

In your example 02 incorrect, second image: that date at the front of the event is in the future - this is the issue?

Re: Error event time (one more year)

Richfez — Mon, 26 Oct 2015 15:12:21 GMT

Given the screenshots, it appears your source database has those "wrong" dates in it. Splunk is using the date/time when it's valid (which means it is not newer than two days in the future by default). When that date falls farther in the future than two days from now (a default you can change if required) it instead uses the current date/time as the timestamp.

So, if those are actually correct and you want them to show up as December 30th 2015, you can likely adjust your timestamp recognition to allow a bigger MAX_DAYS_HENCE as documented here.

If that data is wrong in the original DB (like if that should actually be December 30th, 2014 - or indeed any time in the past), then if you correct it in the original DB Splunk should be able to interpret it properly.

Edit: Accidental premature "Post" before proofing and tweaking phrasing. Then rephrased for clarity. Sorry!

Re: Error event time (one more year)

lcblucas — Mon, 26 Oct 2015 16:24:33 GMT

yes. But only in a few events, not total of events.

Re: Error event time (one more year)

Richfez — Mon, 26 Oct 2015 16:28:35 GMT

Right. See answer below. Are you the DBA responsible for the DB itself, or is someone else in that role? From the information you've supplied, there appears to be bad data inside the database. Splunk will read a timestamp up to two days in the future by default, if it's farther ahead than 2 days it doesn't use that timestamp and instead uses the current time as of when it ingested that data.

If that data is correct and it's REALLY supposed to be 2 months in the future, I outline what to look for to fix it so Splunk will read that correctly.

If that data is incorrect in the original DB, then it should be fixed there. Splunk isn't doing anything wrong, the original DB is wrong so is giving Splunk the wrong information.

Re: Error event time (one more year)

lcblucas — Mon, 26 Oct 2015 16:38:44 GMT

Hi Rich!

I have validated on MySQL command line and there we are the correct date and time of event (December 30, 2014). This is the correct data.

On MySQL query inside splunk (befor index) the timestamp on DATA COLUMN its correct (December 30, 2014). The incorrect date is on search event, according to the images that I sent you.

Please, take a look ate the timeendpos and timestartpos fields on files. The events with incorrect date, the timestartpos are ALWAYS 12 value.

Please, take a look at the new image below:

http://imageshack.com/a/img903/4308/1qoIWH.png

From 21.590 events, only 54 have incorrect date, that match with the timestartpos = 12 value. Do you understood? I hope this help us to troubleshooting.

Thanks and regards!

Luis Carlos
Skype: lcb.lucas

Re: Error event time (one more year)

lcblucas — Tue, 27 Oct 2015 12:37:34 GMT

Hi Rich!

I have validated on MySQL command line and there we are the correct date and time of event (December 30, 2014). This is the correct data.

On MySQL query inside splunk (befor index) the timestamp on DATA COLUMN its correct (December 30, 2014). The incorrect date is on search event, according to the images that I sent you.

Please, take a look ate the timeendpos and timestartpos fields on files. The events with incorrect date, the timestartpos are ALWAYS 12 value.

Please, take a look at the new image below:

http://imageshack.com/a/img903/4308/1qoIWH.png
From 21.590 events, only 54 have incorrect date, that match with the timestartpos = 12 value. Do you understood? I hope this help us to troubleshooting.

Thanks and regards!

Luis Carlos
Skype: lcb.lucas

Re: Error event time (one more year)

henchrm — Thu, 31 Mar 2016 14:47:19 GMT

Please take a look at this post
https://answers.splunk.com/answers/235558/wrong-datetime-conversion-from-epoch.html

I believe it is probably the same issue.... (I cannot validate, as I cannot view the posted screenshots from here).
I believe there is a bug in the splunk db connect app when converting epoch timestamps to human readable form. If left in epoch format, splunk indexes these without issue.