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.
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"
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"
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 -
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"
You've cracked it! Thank you very much 😃
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.
hi @kamlesh_vaghela afraid I've tried that and it doesn't return anything
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
That search returns no results in my environment
Here is the (partially) working search
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.
That works for me -
I have tried adding the eval to my original search but no results found
@timrich66 Can you please check time format of start time and end Time ??
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?