topic Re: Finding the Duration between two timestamps in Splunk Search

Finding the Duration between two timestamps

tyhopping1 — Tue, 08 Oct 2019 20:42:05 GMT

I am currently attempting to create a query that returns the Name of the job, Begin Time, Finish Time, and Duration.

Here is my attempt:

However, the "minutes" a.k.a duration is returning empty. Does this have something to do with the format of timestamp?
Here is an example of the timestamp format I am dealing with:

timestamp: 2019-07-28T04:01:22:041Z

I need this duration column to return the time between BeginTime and FinishTime. Any help is appreciated. Thank you!

Re: Finding the Duration between two timestamps

sandeepmakkena — Tue, 08 Oct 2019 21:17:47 GMT

| eval BeginTime = strptime(BeginTime, "%Y-%m-%d %H:%M:%S")
| eval FinishTime = strptime(FinishTime, "%Y-%m-%d %H:%M:%S")
| eval dur=FinishTime-BeginTime

This is working fine, can you share some sample data. I not sure why you're doing this | eval BeginTime=substr(BeginTime,1,13). sample data would make more sense.

Re: Finding the Duration between two timestamps

tyhopping1 — Wed, 09 Oct 2019 19:45:31 GMT

I am limited to what I can share. Here is a sample event:
counters: { [-]
CountOfRecords: 4
}
extract: FAAFS
NameOfJob: EXAMPLE
level: Information
message: POPULATED
microService: { [+]
}
OutputName: FAAFD
DateOfProcess: 2019123
timestamp: 2019-07-28T04:01:22.041Z

Hope this helps!

Re: Finding the Duration between two timestamps

sandeepmakkena — Wed, 09 Oct 2019 19:57:25 GMT

| eval Time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| stats earliest(Time) as BeginTime, latest(Time) as FinishTime by NameOfJob
| eval dur=FinishTime-BeginTime

Try this.

Re: Finding the Duration between two timestamps

sandeepmakkena — Wed, 09 Oct 2019 20:00:42 GMT

It's okay, Just wanted to time format. Can you try below one and let me know if you have any questions.

Thanks!

Re: Finding the Duration between two timestamps

tyhopping1 — Thu, 10 Oct 2019 15:43:36 GMT

Unfortunately, I get null results from this query.

Re: Finding the Duration between two timestamps

tjago11 — Thu, 10 Oct 2019 16:28:36 GMT

Easiest way is to use strptime to format your time into epoch and then do the math to convert to minutes/hours/days whatever. Here is a run anywhere example using your date format:

| makeresults
| eval beginTime="2019-07-28T04:01:22.041Z"
| eval endTime="2019-07-28T05:01:22.041Z"
| eval beginTimeEpoch=strptime(beginTime, "%Y-%m-%dT%H:%M:%S")
| eval endTimeEpoch=strptime(endTime, "%Y-%m-%dT%H:%M:%S")
| eval durationInMinutes=(endTimeEpoch-beginTimeEpoch)/60

If you have multiple events then you'll need to do a stats with min/max on the epochs before you calculate the duration, something like:

| stats min(beginTimeEpoch) as beginTimeEpoch, max(endTimeEpoch) as endTimeEpoch by yourField

Enjoy!!

Re: Finding the Duration between two timestamps

sandeepmakkena — Fri, 11 Oct 2019 19:36:27 GMT

Is your timestamp field is extracted?
Or As for your data in JSON format, you might also want to use | spath on that field.
I did a test on my data it works find.

Re: Finding the Duration between two timestamps

woodcock — Sat, 12 Oct 2019 21:24:03 GMT

Like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo AND NameOfJob=EXAMPLE
| spath timestamp
| eval timestamp=tonumber(substr(timestamp,1,13))/1000
| stats min(timestamp) AS BeginTime, max(timestamp) AS FinishTime BY NameOfJob
| fieldformat BeginTime=strftime(BeginTime, "%Y-%m-%d %H:%M:%S")
| fieldformat FinishTime=strftime(FinishTime, "%Y-%m-%d %H:%M:%S")
| eval minutes=(FinishTime-BeginTime)/60
| sort 0 FinishTime
| table NameOfJob BeginTime FinishTime minutes