topic Calculate elapsed time from two events in Splunk Search

Calculate elapsed time from two events

timrich66 — Tue, 22 Jun 2021 11:32:35 GMT

Hi,

I'm sure I'm not the first to ask this question, but I can't seem to find an answer that covers what I am trying to achieve.

I have an index which collects job stats - start, end, fail, success etc

What I would like to do is create a table to display all the jobs I am interested in in one column, then the start, end and run times and a status column. Like this -

Column A Column B Column C Column D Column E

Jobname Start Time End Time Run Time Status

abc 08:00 08:01 1 Success

The search below gives me everything EXCEPT I cannot calculate 'Run Time' because the events are separate. I've tried with 'streamstats' and 'transaction' without any success.

index=foo sourcetype=bar_prd "p-foo*" earliest=-6h

| rex "JOB: (?<j>p-foo-[a-z\-]+)"

| rex "STATUS: (?<s>\w+)\s"

| eval ST=if(s="RUNNING",_time,"")

| eval ET=if(s="SUCCESS",_time,"")

| eval Status=if(s="SUCCESS","Success","")

| eval ST=strftime(ST,"%Y-%m-%d %H:%M:%S.%Q")

| eval ET=strftime(ET,"%Y-%m-%d %H:%M:%S.%Q")

| stats values(ST) as "Start Time", values(ET) as "End Time", values(Status) by j

As ever, I'd be very grateful for assistance.

Re: Calculate elapsed time from two events

kamlesh_vaghela — Tue, 22 Jun 2021 11:50:00 GMT

@timrich66

Try by adding below search at the end of your search.

| eval "Run Time"=round(strptime('End Time',"%H:%M") - strptime('Start Time',"%H:%M"))/60

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Calculate elapsed time from two events

timrich66 — Tue, 22 Jun 2021 13:23:44 GMT

hi @kamlesh_vaghela afraid I've tried that and it doesn't return anything

Re: Calculate elapsed time from two events

kamlesh_vaghela — Tue, 22 Jun 2021 13:33:38 GMT

@timrich66

It should work. Check this.

| makeresults | eval _raw="Jobname,Start Time,End Time,Status abc,08:00,08:01,Success "| multikv forceheader=1 | table Jobname Start_Time End_Time Status | eval "Run Time"=round(strptime('End_Time',"%H:%M") - strptime('Start_Time',"%H:%M"))/60

Can you please share sample OP from your main search?

Re: Calculate elapsed time from two events

timrich66 — Tue, 22 Jun 2021 13:47:19 GMT

@kamlesh_vaghela

That search returns no results in my environment

Here is the (partially) working search

Re: Calculate elapsed time from two events

kamlesh_vaghela — Tue, 22 Jun 2021 14:06:06 GMT

@timrich66

Can you please try this?

| makeresults | eval _raw="Jobname,Start Time,End Time,Status abc,2021-06-22 11:45:00.000,2021-06-22 11:46:21.000,Success "| multikv forceheader=1 | table Jobname Start_Time End_Time Status | eval "Run Time (In Sec)"= round(strptime('End_Time',"%Y-%m-%d %H:%M:%S.%3N") - strptime('Start_Time',"%Y-%m-%d %H:%M:%S.%3N")),"Run Time"=tostring('Run Time (In Sec)',"duration")

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Calculate elapsed time from two events

timrich66 — Tue, 22 Jun 2021 14:21:21 GMT

That works for me -

I have tried adding the eval to my original search but no results found

Re: Calculate elapsed time from two events

kamlesh_vaghela — Tue, 22 Jun 2021 14:23:34 GMT

@timrich66 Can you please check time format of start time and end Time ??

Re: Calculate elapsed time from two events

timrich66 — Wed, 23 Jun 2021 08:18:21 GMT

Hi @kamlesh_vaghela

Both Start and End Time use _time which is in the format %d/%m/%Y %H:%M:%S e.g. 23/06/2021 09:05:05

The strftime format to return to human readable is - "%Y-%m-%d %H:%M:%S.%Q"

Is that what you need?

Re: Calculate elapsed time from two events

ITWhisperer — Wed, 23 Jun 2021 08:30:48 GMT

Re: Calculate elapsed time from two events

timrich66 — Wed, 23 Jun 2021 09:12:10 GMT

hi @ITWhisperer thanks for this. It is one of the variations I tried before posting. In my environment, 'RT' is not being calculated or displayed using that search -

Re: Calculate elapsed time from two events

ITWhisperer — Wed, 23 Jun 2021 09:18:55 GMT

Try using null() instead of ""

Re: Calculate elapsed time from two events

timrich66 — Wed, 23 Jun 2021 09:29:49 GMT

You've cracked it! Thank you very much 😃