How do I extract the time from this sample timestamp and convert it into seconds to find the different from the current time?

[ppanchal]

Hi,

This is the timestamp format that I get in the logs: 2016-08-22 14:49:01
I want to separate the date and time from this, convert the time into seconds, and then subtract it with the current time to find the difference.

Please help.

[martin_mueller · Accepted solution]

Assuming that timestamp is already recognized by Splunk as the event's timestamp, I see two ways your question could go:

1. You want to see the difference between the time Splunk indexed the data and the time the event was generated: ... | eval delta = _indextime - _time
2. You want to see the difference between now and the time the event was generated: ... | eval delta = now() - _time

[martin_mueller]

Seconds to minutes would be "divide by 60"... anywhere, not just in Splunk 😄

[ppanchal]

lol, my bad.
Actually, these are existing dashboards which are no more functioning and I am trying to make them working.
Not sure why the creator did divide by 60000 initially...

[ppablo]

Hi @ppanchal

If the the answer and comments by @martin_mueller solved your question, please don't forget to resolve the post by clicking "Accept" directly below his answer. Also, be sure to upvote the answer and/or any of his comments you found especially helpful!

Patrick

[ppanchal]

I have done that thanks 🙂

[martin_mueller]

You can use eval's round(). Why are you dividing by 60000?

[ppanchal]

I want to convert the seconds back to minutes at the end.

Sorry I am new to splunk so just trying to figure out things.

[martin_mueller]

Ah, that's different. You'll need strptime() from eval: docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/CommonEvalFunctions

Something like ... | eval delta = now() - strptime(CREATION_TIME, "%Y-%m-%d %H:%M:%S")

[ppanchal]

Great that worked 🙂

But now I am getting the seconds in the below format,
1471899601.000000
How should I remove the 0's after the decimal point?

Here is my complete query,

index="ocsmonitor"  sourcetype="idle_alert"|  eval a =strptime(CREATION_TIME, "%Y-%m-%d %H:%M:%S")| stats latest(a) as latests |eval tnow=now()| eval b = (tnow-latests)/60000| table b

The output for b is 0.00020000000.

I want to remove the extra 0's.

Please help.

[ppanchal]

I have a field called CREATION_TIME.
CREATION_TIME=2016-08-22 14:49:01

How will I convert this into seconds?