Finding the Duration between two timestamps

[tyhopping1]

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:

NameOfJob = EXAMPLE | spath timestamp | search timestamp=*
| stats earliest(timestamp) as BeginTime, latest(timestamp) as FinishTime
by NameOfJob
| eval BeginTime=substr(BeginTime,1,13)
| eval BeginTimeD=strftime(BeginTime/1000, "%Y-%m-%d %H:%M:%S")
| eval FinishTime=substr(FinishTime,1,13)
| eval FinishTimeD=strftime(FinishTime/1000, "%Y-%m-%d %H:%M:%S")
| eval seconds=FinishTimeD-BeginTimeD | eval seconds=seconds/1000 | eval minutes=seconds/60 | eval hours=minutes/60 | table NameOfJob BeginTime FinishTime minutes | sort column FinishTime

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!

[woodcock · Accepted solution]

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

[tjago11]

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!!

[sandeepmakkena]

| 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.

[sandeepmakkena]

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.

[tyhopping1]

Unfortunately, I get null results from this query.

[sandeepmakkena]

| 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.

[tyhopping1]

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!

[sandeepmakkena]

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

Thanks!