All Apps and Add-ons

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

BigCosta
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
1 Solution

BigCosta
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

BigCosta
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
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

cmerriman
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

BigCosta
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

cmerriman
Super Champion

add this into your SQL statement:

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

BigCosta
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

bjbrake
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
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...