Splunk Search

Epoch Time to Conventional Time

linu1988
Champion

Hello,
I am having trouble converting to Hour:Minute:Second format from epoch time

First i have made a subtraction which i want it to be in human readable format. But it's giving me wrong result. Below is my query.

|Stats max(_time) as LastBackup,min(_time) as StartBackup,sum(FileSize) as TotalBackupSize | eval TimeDiff=LastBackup-StartBackup| table StartBackup LastBackup TimeDiff|eval Total=strftime(TimeDiff,"%H:%M:%S")

StartBackup LastBackup  TimeDiff    Total

1 1375920004 1375929896 9892 04:44:5

But the value should be 2:44:5, Why am i getting such wrong result?

Any insight will be much appreciated.

Tags (3)
0 Karma

siraj198204
Explorer

source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] |eval exp_date=strftime(relative_time(Lastpasswordchangedate, "+90d@d"), "%Y/%m/%d %H:%M") | eval exp_days=floor((exp_date-Lastpasswordchangedate)/86400) |rename lastPasswordChange AS Lastpasswordchangedate

This is the query ,

Lastpasswordchangedate =2014-08-06 11:20:39
application =TFAYD
exp_date =2004/07/07 00:00
owner =206404613
refreshedDate =2014-09-26 09:15:25
sourcetype =mysql

i need exp_date output 90 days from Lastpasswordchangedate , but iam getting wrong output . any one can help me on this ...

0 Karma

kristian_kolb
Ultra Champion

Yes, Splunk and the source server may be in the same TZ, but epoch is always in UTC, which is why I figured that you may be in central Europe. With daylight saving, CET is two hours ahead of UTC.

So the 2:44 (or 4:44) is not duration expressed as H:M or M:S, but rather the actual time.

0 Karma

linu1988
Champion

Now this comment make some sense, I got the point. On the timezone we have splunk server and The actual servers on the same timezone. How do i get the actual difference of the time on the event's the question as it's 2:44 not 4:44 minutes

0 Karma

kristian_kolb
Ultra Champion

The reason for the 'wrong' result is that you asked for a conversion of the epoch 9892, which occurred on January 1st, 1970, at 02:44:52. That is quite different from a generic duration in seconds. However, since you only present the H:M:S part of the result, that may not be obvious at first glance. Could it be that you are two timezones east of London? That could explain the two extra hours.

MuS
SplunkTrust
SplunkTrust

Hi linu1988,

once did some epoch to human readable converting using this commands:

  convert timeformat="%Y-%m-%d %T" mktime(Message_time) | fieldformat Message_time=strftime(Message_time,"%Y-%m-%d %T")

adapted to your search it should be something like this:

 | stats max(_time) as LastBackup,min(_time) as StartBackup,sum(FileSize) as TotalBackupSize | eval TimeDiff=LastBackup-StartBackup | fieldformat TimeDiff=strftime(TimeDiff,"%T") | table StartBackup LastBackup TimeDiff

does that make sense?

Cheers, MuS

linu1988
Champion

Hey MuS,
My query wasn't that different from yours. It gave me the same answer i was getting. I am not confident why the time difference is coming like this, but having so many great functionality in Splunk this small things should be coming automatically rather this much thinking. At last i ended up with below:

| eval TimeDiff=LastBackup-StartBackup | eval hour=floor(TimeDiff / 3600)|eval min=floor((TimeDiff / 60) % 60)|eval sec=TimeDiff % 60|eval TimeDiff=hour.":".min.":".sec| table StartBackup LastBackup TimeDiff

May look dirty but i am at least getting the result

0 Karma

MuS
SplunkTrust
SplunkTrust

hehe, copy paste error - updated the answer 🙂

0 Karma

kristian_kolb
Ultra Champion

That correct? From the docs on convert:

mktime()
Syntax: mktime()
Description: Convert an human readable time string to an epoch time. Use timeformat option to specify exact format to convert from.

If the docs are right, you'd be trying to convert epoch (treated as %T) into epoch.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...