Splunk Search

Finding the Duration between two timestamps

tyhopping1
Engager

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!

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

tjago11
Communicator

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

0 Karma

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

0 Karma

sandeepmakkena
Contributor

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.

0 Karma

tyhopping1
Engager

Unfortunately, I get null results from this query.

0 Karma

sandeepmakkena
Contributor

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

0 Karma

tyhopping1
Engager

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!

0 Karma

sandeepmakkena
Contributor

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

Thanks!

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...