topic Re: Average transaction duration by unique transaction id in Splunk Search

Average transaction duration by unique transaction id

zd00191 — Wed, 08 Jul 2015 13:51:32 GMT

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!

Re: Average transaction duration by unique transaction id

woodcock — Wed, 08 Jul 2015 14:00:34 GMT

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)

Re: Average transaction duration by unique transaction id

zd00191 — Wed, 08 Jul 2015 14:04:04 GMT

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.

Re: Average transaction duration by unique transaction id

woodcock — Wed, 08 Jul 2015 14:38:47 GMT

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?

Re: Average transaction duration by unique transaction id

zd00191 — Wed, 08 Jul 2015 14:42:07 GMT

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

Re: Average transaction duration by unique transaction id

zd00191 — Wed, 08 Jul 2015 14:46:30 GMT

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

Re: Average transaction duration by unique transaction id

zd00191 — Wed, 08 Jul 2015 14:46:38 GMT

Thanks for your help though!

Re: Average transaction duration by unique transaction id

zd00191 — Wed, 08 Jul 2015 14:47:09 GMT

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

Re: Average transaction duration by unique transaction id

sideview — Fri, 10 Jul 2015 07:13:39 GMT

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.

Re: Average transaction duration by unique transaction id

zd00191 — Fri, 17 Jul 2015 15:05:21 GMT

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?

Re: Average transaction duration by unique transaction id

woodcock — Fri, 17 Jul 2015 15:38:12 GMT

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.