Calculate elapsed time from two events

[timrich66]

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.

[ITWhisperer]

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","")
| stats values(ST) as ST, values(ET) as ET, values(Status) as Status by j
| eval RT=ET-ST
| eval ST=strftime(ST,"%Y-%m-%d %H:%M:%S.%Q")
| eval ET=strftime(ET,"%Y-%m-%d %H:%M:%S.%Q")
| rename ST as "Start Time", ET as "End Time", RT as "Run Time"

[timrich66]

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 - 

[ITWhisperer · Accepted solution]

Try using null() instead of ""

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,null())
| eval ET=if(s="SUCCESS",_time,null())
| eval Status=if(s="SUCCESS","Success","")
| stats values(ST) as ST, values(ET) as ET, values(Status) as Status by j
| eval RT=ET-ST
| eval ST=strftime(ST,"%Y-%m-%d %H:%M:%S.%Q")
| eval ET=strftime(ET,"%Y-%m-%d %H:%M:%S.%Q")
| rename ST as "Start Time", ET as "End Time", RT as "Run Time"

[timrich66]

You've cracked it!  Thank you very much 😃

[kamlesh_vaghela]

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

[timrich66]

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

[kamlesh_vaghela]

@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?

KV

[timrich66]

@kamlesh_vaghela 

That search returns no results in my environment

Here is the (partially) working search 

[kamlesh_vaghela]

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

[timrich66]

That works for me - 

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

[kamlesh_vaghela]

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

[timrich66]

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?