All Apps and Add-ons

db connect timestamp issues

a212830
Champion

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?

0 Karma

lukejadamec
Super Champion

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

0 Karma

MaverickT
Communicator

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...

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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

0 Karma

a212830
Champion

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...