Problem dealing with epoch values - splunk does not recognize the date format and gives wrong dates.

[suhasyodlee]

Hi,
I am trying to index a date column of my table which is stored in epoch format.
No matter what i do, when i try to view the index, it gives me wrong value.
Ex : if the event had occured at 14:22 on Aug 8th, the time column gives me the current time(i.e sysdate) and the event column gives me "1970-01-01 05:29:59" as the standard value, no matter what conversions i do in my SQL query .

the output timefomat i have set to :
yyyy-MM-dd'T'HH:mm:ss.SSSZ

My DB is in PDT timezone.

Different flavors of SQL query used :

SELECT *
FROM (SELECT TO_CHAR (NEW_TIME (epoch_to_date (a.created), 'GMT', 'PDT'),'YYYY-MM-DD HH24:MI:SS') create_date,a.cobrand_id, a.cache_item_id, a.sum_info_id, a.server_type,a.server_stats_id FROM temp_sg_server_stats a)

--Raising column = create_date

SELECT * FROM (SELECT TO_CHAR(epoch_to_date_for_tz (a.created),'YYYY-MM-DD HH24:MI:SS') createdate,a.cobrand_id, a.cache_item_id, a.sum_info_id, a.server_type, a.server_stats_id FROM temp_sg_server_stats a) {{WHERE $rising_column$ > ?}}

Raising column = create_date

and when i issue the query without any coversions, and index it on a.created directly, it gives me the same result as well.

And to add upon it,
i added the following in props.conf.
[source::dbmon-tail://Perf-OLTP/mon_server_stats_with_epoch_conversion]
TIME_FORMAT=%S
MAX_DAYS_AGO = 3650
TZ = US/Pacific

and in inputs.conf, i added these two columns.
[dbmon-tail://Perf-OLTP/mon_server_stats_with_epoch_conversion]
host = perf70
index = try_props_epoch
output.format = kv
output.timestamp = 1
output.timestamp.column = created_date
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
query = SELECT *\r\n FROM (SELECT TO_CHAR (NEW_TIME (epoch_to_date (a.created), 'GMT', 'PDT'),\r\n 'YYYY-MM-DD HH24:MI:SS'\r\n ) created_date,\r\n a.cobrand_id, a.cache_item_id, a.sum_info_id, a.server_type,\r\n a.server_stats_id\r\n FROM temp_sg_server_stats a) {{WHERE $rising_column$ > ?}}
sourcetype = monitor_server_stats_epoch_conversion
tail.rising.column = created_date

Experts, please provide a solution for this.

[suhasyodlee]

finally got the resolution after a lot of P&Cs.

in the query gave the epoch value as epoch_to_date_for_tz(column name) alias name.
Raising column = alias name.

inputs.conf
-added the following :
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss

props.conf
-add the following :
TIME_FORMAT="%Y-%m-%d %H:%M:%S"
MAX_DAYS_AGO = 3650
TZ=UTC

Restarted splunk and boom - it started working like a charm 🙂 🙂

[suhasyodlee]

Hi,
thanks for the feed back and i tried both the suggested ways, but no luck 😞

1) directly indexing the epoch field.
o/p - i get time as today's time and event date as 01-01-1970 5:59:59

2) specified UTC in the props.conf instead of US/Pacific
o/P - got the same as above.

Used one more thing, but it isn't working.
was trying to issue the raising column as "to_char(epoch_to_Date_for_tz(CREATED),'YYYY-MM-DD HH24:MI:SS')" but getting an exception and it is not allowing me to create the database input.

Our DB epoch value is a digit integer like - 1407853860,1407853860 etc..
please lemme know if i am missing anything here.

[bmacias84]

Epoch is normally given in UTC. Looking at your props you specify US/Pacific. Within your props.conf specify UTC or GMT.

About Epoch Time

[guilmxm]

Hi, you say you have a timestamp column in your table which is in epochtime format, have you simply tried to use this field as the timestamp field ? Splunk should automatically recognize the epochtime as the timestamp format