db connect timestamp issues

[a212830]

Hi,

I want to do a tail on an Oracle table, using db connect. The table contains a column that appears to be in epoch time, so it should be good for the rising column value. However, whenever I get the data, it is always appearing as midnight of that day, which is incorrect. I'm not sure what I'm doing wrong to get Splunk to recognize the data properly.

Here's some output from the db connect tool:

CREATIONTIME HOST
1277956800.000 cpu2
1277956800.000 ftgbosbb02vwin
1277956800.000 cmsvrtpcva1win
1277956800.000 ftgbos018vwin
1277956800.000 amro348
1277956800.000 cpu4

I have CREATIONTIME set as the Rising Column, and my props.conf has the following:

[jmagicAlerts]
MAX_TIMESTAMP_LOOKAHEAD = 300
NO_BINARY_CHECK = 1
SHOULD_LINEMERGE = false
pulldown_type = 1
TIME_PREFIX = CREATIONTIME=
TIME_FORMAT = %s.%3N

If I execute "SELECT CREATIONTIME from..." via sqlplus, I get the following, which shows specific timeframes:

CREATIONTIME

07-01-2010-00:07:41
07-01-2010-00:07:45
07-01-2010-00:07:48
07-01-2010-00:07:48
07-01-2010-00:07:48
07-01-2010-00:07:48
07-01-2010-00:07:53
07-01-2010-00:07:54

Does anyone have suggestions?

[lukejadamec]

Oracle and timestamps can be somewhat frustrating.

1) You need to use to_char in your query to convert the timestamp (see the link)

2) The timestamp column should be first in your query (see the link)

3) You may need to add the output.timestamp.parse.format= in your inputs.conf. I did not, but this fellow did (see the link).

4) This oracle timestamp format works for me: inputs.conf yyyy-MMM-dd HH:mm:ss.SSS, props.conf %Y-%b-%d %H:%M:%S.%3N

http://splunk-base.splunk.com/answers/71485/splunk-db-connect-timestamp-not-working

[MaverickT]

Well, I have solved issue with unix timestamp problem by getting "splunk friendly" date format directly from database (in my case mysqls function 'from_unixtime'). This is the configuration that works in my case:

inputs.conf:
[dbmon-dump://dbname/users]
host = alter.si
index = alterbi
output.format = kv
output.timestamp = 1
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
output.timestamp.column = U_RegisteredTime
output.timestamp.format = yyyy-MM-dd HH:mm:ss
query = SELECT U_Number, U_Username, FROM_UNIXTIME(U_Registered) as U_RegisteredTime FROM w3t_Users ORDER BY U_Registered
sourcetype = dbusers
interval = 11 * * * *
table = users
disabled = 1

props.conf:
[dbusers]
TIME_FORMAT=%Y-%m-%d %H:%M:%S
MAX_DAYS_AGO = 3650

I guess there is some other way to do it without converting everything to iso format, but I havent managed to sort it out...

[jcoates_splunk]

Hi, my guess is that you're getting execution time because it doesn't recognize your time column. Try the output time stamp values here: http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/inputsspec

[a212830]

Thanks. I had looked at this - I don't see anything wrong with my setup.