All Apps and Add-ons
Highlighted

Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

Path Finder

Hello!
I used Splunk DB Connect v1 and now I updated it to Splunk DB Connect v3. I get data from oracle sql.
I have several TIMESTAMP type columns that in version 1 were saved in the epoch format, but in version 3 they are stored in human readable format.

Example (CREATED is TIMESTAMP type):

Version 1: CREATED = 1495450599.159
Version 3: CREATED = 2017-05-22 13:56:39.159

How to make so that in version 3 TIMESTAMP type was in epoch format?

0 Karma
Highlighted

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

Super Champion

The best workaround I have for that is to just write an eval after I bring in my dbxquery. |eval CREATED=strptime(CREATED,"%Y-%m-%d %H:%M:%S.%3N")

you could also use convert. |convert mktime(CREATED) timeformat="%Y-%m-%d %H:%M:%S.%3N"
https://docs.splunk.com/Documentation/Splunk/6.6.0/SearchReference/Convert
There might be another way, but that's what I do.

0 Karma
Highlighted

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

Path Finder

Thanks for the quick response!
I need to be in the index TIMESTAMP was in epoch.
I already have an old index in which TIMESTAMP is in epoch and there are complex searches and applications that use epoch. In my situation, the use of this workaround is very difficult.

0 Karma
Highlighted

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

Super Champion

add this into your SQL statement:

trunc((CREATED-TO_DATE('01-01-1970','MM-DD-YYYY')) * 86400) as indexTime
0 Karma
Highlighted

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

Path Finder

After adding, I get an error:

SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

Here it works without error:

(trunc(CREATED) - TO_DATE('01-01-1970', 'MM-DD-YYYY')) * 86400 as indexTime

BUT time is rounded to one day. This is unacceptable to me. I need original accuracy.
In fact, DB Connect v3 converts a TIMESTAMP type to a DATATIME type.

Can I make DB Connect v3 not do this with TIMESTAMP types, but just keep it in the form in which it is shown in oracle (1495450599.159)?

0 Karma
Highlighted

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

New Member

This worked for me:

trunc((extract(day from (CREATED - to_date('01-JAN-1970','DD-MON-YYYY')))86400+extract(hour from CREATED)3600+extract(minute from CREATED)*60+extract(second from CREATED))) as indexTime,

0 Karma
Highlighted

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

Path Finder

I found a workaround for the problem.
I changed the SQL query

round((cast(CREATED as date)-to_date('01011970','MMDDYYYY'))*86400)||'.'||to_char(CREATED, 'FF') as CREATED_EPOCH

View solution in original post

0 Karma
Highlighted

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

SplunkTrust
SplunkTrust

If your problem is resolved, please accept an answer to help future readers.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.