Splunk Search

Calculate elapsed time from two events

timrich66
Communicator

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.

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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"

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
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"
0 Karma

timrich66
Communicator

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 - 

timrich66_0-1624439512546.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Communicator

You've cracked it!  Thank you very much 😃

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

timrich66
Communicator

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

timrich66
Communicator

@kamlesh_vaghela 

That search returns no results in my environment

timrich66_1-1624369588964.png

Here is the (partially) working search 

timrich66_0-1624369548035.png

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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")

 

Screenshot 2021-06-22 at 7.35.54 PM.png

 

Thanks
KV
▄︻̷̿┻̿═━一

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

0 Karma

timrich66
Communicator

That works for me - 

timrich66_0-1624371623632.png

 

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

timrich66
Communicator

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?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...