Splunk Search

Average transaction duration by unique transaction id

zd00191
Communicator
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!

Tags (3)
0 Karma
1 Solution

zd00191
Communicator

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

View solution in original post

0 Karma

zd00191
Communicator

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

View solution in original post

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

zd00191
Communicator

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?

0 Karma

woodcock
Esteemed Legend

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)
0 Karma

zd00191
Communicator

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.

0 Karma

woodcock
Esteemed Legend

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?

0 Karma

zd00191
Communicator

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)"
0 Karma

zd00191
Communicator

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)"
0 Karma

zd00191
Communicator

Thanks for your help though!

0 Karma

woodcock
Esteemed Legend

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.

0 Karma