Show average response time of top 20 URLs

[herbie]

Hey Guys, I thought this would be simple, but doesn't seem so. From our HTTP logs, I want to get a list of the top 20 hit URLs and show the average response time for each of those within the one search.

Something like this:

index=prod sourcetype="odr" | top limit=20 cs_uri_stem | stats avg(time_taken) by cs_uri_stem

However, the time_taken field doesn't get passed to the stats command so it displays blank.

Basically, this is the output I want to show:

I was looking at the documentation on subsearches, but I can't figure out how to do it with that either.

Any ideas?

Thanks in advance.

[Ayn · Accepted solution]

You could just skip the top clause and use stats directly:

index=prod sourcetype="odr" | stats count,avg(time_taken) by cs_uri_stem | sort - count

The only caveat is that this will return results for all URL's, not just the top 20. However with the sort you'll get the top 20 first in the results anyhow.

[araitz · Accepted solution]

There are a few ways to do this, but this is probably the best:

index=prod sourcetype="odr" earliest=-1h [search index=prod sourcetype="odr" earliest=-1h | top limit=20 cs_uri_stem | fields cs_uri_stem] | stats avg(time_taken) by cs_uri_stem

To add to Ayn's answer:

index=prod sourcetype="odr" | stats count,avg(time_taken) by cs_uri_stem | sort - count | head 20

[herbie]

Thanks for the responses guys, both solutions work well.

[Ayn]

Doh, of course 🙂 Thanks for filling that in.

[Ayn · Accepted solution]

You could just skip the top clause and use stats directly:

index=prod sourcetype="odr" | stats count,avg(time_taken) by cs_uri_stem | sort - count

The only caveat is that this will return results for all URL's, not just the top 20. However with the sort you'll get the top 20 first in the results anyhow.