Getting Data In

How to calculate the difference in minutes/seconds between two timestamps in a human readable format?

rijinc
Explorer
Submit Date / Creation Date Time Stamp  Incident Response Date Time
09/14/2016 01:14 AM                    09/14/2016 01:19 AM

I was searching many scenarios in the SPLUNK community, but was not able to find a solution for this. We need to find the difference between the two timestamps above, and I need to display the output in minutes, seconds etc.

Is it possible?

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

Yes, it is possible.

Without an actual sample of the entire event, this may be slightly off. Also, I don't know if you have it in fields or not, nor what fields it may be. As I look at what you have, I'm going to hope you have two fields, a "submit date time stamp" and a "incident response date time". This is highly likely to be wrong.

... your base search here ... 
| eval submit=strptime(submit_date, "%m/%d/%Y %I:%M %p") 
| eval response=strptime(response_date, "%m/%d/%Y %I:%M %p")
| eval difference=response-submit | eval diff_in_minutes=difference/60

You will have to fix the fieldnames (submit_date, response_date) or reply with more information about that.

That uses eval strptime to convert the text strings into actual dates/times in unix epoch. That's just seconds, so we subtract them to get the difference and divide by 60 to get minutes.

Here's a run-anywhere example where I create the two fields, then perform the above calculations on them.

* | stats count | eval submit_date="09/14/2016 01:14 AM" | eval response_date="09/14/2016 01:19 AM"
| eval submit=strptime(submit_date, "%m/%d/%Y %I:%M %p") 
| eval response=strptime(response_date, "%m/%d/%Y %I:%M %p")
| eval difference=response-submit | eval diff_in_minutes=difference/60

Note if you really don't need the difference in seconds you could just simplify:

| eval submit=strptime(submit_date, "%m/%d/%Y %I:%M %p") 
| eval response=strptime(response_date, "%m/%d/%Y %I:%M %p")
| eval diff_in_minutes=(response-submit)/60

Happy Splunking!
-Rich

View solution in original post

sundareshr
Legend

You have to convert the time to epochtime before you calculate difference. Try this

.... | eval submit=strptime("Submit Date / Creation Date Time Stamp", "%m/%d/%Y %H:%M %p") | eval response=strptime("Incident Response Date Time", "%m/%d/%Y %H:%M %p") | eval time_diff=response-submit | eval time_diff=tostring(time_diff, "duration")

Richfez
SplunkTrust
SplunkTrust

Yes, it is possible.

Without an actual sample of the entire event, this may be slightly off. Also, I don't know if you have it in fields or not, nor what fields it may be. As I look at what you have, I'm going to hope you have two fields, a "submit date time stamp" and a "incident response date time". This is highly likely to be wrong.

... your base search here ... 
| eval submit=strptime(submit_date, "%m/%d/%Y %I:%M %p") 
| eval response=strptime(response_date, "%m/%d/%Y %I:%M %p")
| eval difference=response-submit | eval diff_in_minutes=difference/60

You will have to fix the fieldnames (submit_date, response_date) or reply with more information about that.

That uses eval strptime to convert the text strings into actual dates/times in unix epoch. That's just seconds, so we subtract them to get the difference and divide by 60 to get minutes.

Here's a run-anywhere example where I create the two fields, then perform the above calculations on them.

* | stats count | eval submit_date="09/14/2016 01:14 AM" | eval response_date="09/14/2016 01:19 AM"
| eval submit=strptime(submit_date, "%m/%d/%Y %I:%M %p") 
| eval response=strptime(response_date, "%m/%d/%Y %I:%M %p")
| eval difference=response-submit | eval diff_in_minutes=difference/60

Note if you really don't need the difference in seconds you could just simplify:

| eval submit=strptime(submit_date, "%m/%d/%Y %I:%M %p") 
| eval response=strptime(response_date, "%m/%d/%Y %I:%M %p")
| eval diff_in_minutes=(response-submit)/60

Happy Splunking!
-Rich

rakeshyv0807
Explorer

Hi,

I have similar question and your response was with regards to two different fields but in my case we have only single timestamp field but there are two logs with two different timestamps in that field. How to calculate the difference? Can you kindly help.

See below sample of my logs:

11:01:17.876 AM 2019-04-03 tid:XG5HNBNsbPp8uZis90UWNl9vnqQ DEBUG [org.sourceid.util.log.internal.TrackingIdSupport] [cross-reference-message] entityid:ApplicationName subject:null

2019-04-03 11:01:26,579 tid:XG5HNBNsbPp8uZis90UWNl9vnqQ DEBUG [org.sourceid.util.log.internal.TrackingIdSupport] [cross-reference-message] entityid:ApplicationName subject:JohnDoe

The difference between two logs is the time stamp and subject value where in the first log the subject is null and in the second the subject is user id.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...