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.
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 ...
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.
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
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.
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
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
hehe, copy paste error - updated the answer 🙂
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.