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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...