Average transaction duration by unique transaction id

[zd00191]

index=ko_autosys sourcetype=autosys_applog_scheduler_events host="usatlb98" OR host="usatlb91" System="*" | transaction JOB_NAME startswith="START*" unifyends=true | eval durationNew=duration |eval x=tostring(duration,"duration") |stats count(JOB_NAME) AS "Job_Run_Total" avg(x) by JOB_NAME

I have the search above. I want to find all the transactions. After getting all the transactions, calculate the average duration by job name (the trans id) and then display a table with the job_name, # of transactions , and the average duration

Please help. Thanks!

[zd00191 · Accepted solution]

This is the answer to the question above.

 index=ko_autosys sourcetype=autosys_applog_scheduler_events host="usatlb98" OR host="usatlb91" System="$System$" | transaction JOB_NAME startswith="START*" unifyends=true | eval durationNew=duration |stats count(JOB_NAME) AS "Job_Run_Total" avg(durationNew) as x  by JOB_NAME |eval y = tostring(x, "duration") |eval "Average Duration(hh:mm:ss)"=substr(y, 1,8) |table JOB_NAME Job_Run_Total "Average Duration(hh:mm:ss)"

[sideview]

Cleaning it up a bit for you gives -

index=ko_autosys sourcetype=autosys_applog_scheduler_events host="usatlb98" OR host="usatlb91" System="$System$" 
| transaction JOB_NAME startswith="START*" unifyends=true 
| eval durationNew=duration 
| stats count AS "Job_Run_Total" avg(durationNew) as duration  by JOB_NAME 
| eval duration = tostring(duration, "duration") 
| eval duration=substr(duration,1,8)
| table JOB_NAME Job_Run_Total duration
| rename duration as "Average Duration(hh:mm:ss)"

little things like stats count(foo) by foo is redundant and always the same as stats count by foo, and just simplifying your eval's a bit. cheers.

[zd00191]

Thisis awesome. Thank you. I need to only the results of the search above if any of the durations of the specific job name are greater the average by 40% or more. Do I need to create a whole new search?

[woodcock]

Try this instead (much faster)

 index=ko_autosys sourcetype=autosys_applog_scheduler_events host="usatlb98" OR host="usatlb91" System="*" |stats  earliest(_time) AS start_time latest(_time) AS end_time count AS "Events In Job" BY JOB_NAME | eval duration=end_time-start_time

Then you can add this to the end, too:

| stats avg(duration) avg(Events In Job)

[zd00191]

Thanks for responding. I want the table to look like this.

Job Name Job Run Total Average duration

Job names should not show up twice. The job run total is the number of transactions for that job name. The average duration is the average duration of the number of transactions by job name.

[woodcock]

I assumed that "JOB_NAME" is unique and if it is not, my approach cannot be made to work but this should:

index=ko_autosys sourcetype=autosys_applog_scheduler_events host="usatlb98" OR host="usatlb91" System="*" | transaction JOB_NAME startswith="START*" unifyends=true | stats count AS "Job Run Total" avg(duration) AS "Average duration" BY JOB_NAME | rename "JOB_NAME" AS "Job Name"

This is pretty much the same as what you did; does it not work as you expect?

[zd00191]

I actually got it working with this. I just need to trim all the extra zeros on the end of the average duration.`

    index=ko_autosys sourcetype=autosys_applog_scheduler_events host="usatlb98" OR host="usatlb91"     System="*" | transaction JOB_NAME startswith="START*" unifyends=true | eval durationNew=duration |stats count(JOB_NAME) AS "Job_Run_Total" avg(durationNew) as x  by JOB_NAME |eval "Average Duration(hh:mm:ss)" = tostring(x, "duration") |table JOB_NAME Job_Run_Total "Average Duration(hh:mm:ss)"

[zd00191]

Got it.

index=ko_autosys sourcetype=autosys_applog_scheduler_events host="usatlb98" OR host="usatlb91" System="$System$" | transaction JOB_NAME startswith="START*" unifyends=true | eval durationNew=duration |stats count(JOB_NAME) AS "Job_Run_Total" avg(durationNew) as x  by JOB_NAME |eval y = tostring(x, "duration") |eval "Average Duration(hh:mm:ss)"=substr(y, 1,8) |table JOB_NAME Job_Run_Total "Average Duration(hh:mm:ss)"

[zd00191]

Thanks for your help though!

[woodcock]

You should "Accept" an answer (even if it is one you add which says "I figured it out", like you just said in your last comment) to close out the Question.