Calculate max min average response time and Peak hour by URL

[shashank_24]

Hi, I am trying to put together a table like this - Need to calculate the max TPM, max response time and average response time

url	max_per_hour	total_count	AverageRespTime	MaxRespTime
/test1	314	1514
/test2	777	2876

My peak hour count and total count are coming but AverageRespTime and MaxRespTime are blank. I want to calculate this for last 30 days and below is the query I am using -

index=myindex sourcetype=access_combined_wcookie status=200 
| bucket span=1h _time 
| stats count as hour_count by _time url 
| stats max(hour_count) as max_per_hour sum(hour_count) as total_count avg(time_serve) as AverageRespTime max(time_serve) as MaxRespTime by url

Can someone advice what I am doing wring here OR may be some other way to achieve this task?

NOTE - time_serve field is available in my interesting fields.

[anilchaithu]

@shashank_24 

time_serve field is not available for the second stats command 

[shashank_24]

@anilchaithu It is available. Sorry I should have mentioned it.

[anilchaithu]

@shashank_24 

The output from first stats command will be the output for second stats command.

In this query the first stats command output hour_count, _time & url. So the time_server is not available for the second stats command

| stats count as hour_count by _time url
| stats max(hour_count) as max_per_hour sum(hour_count) as total_count avg(time_serve) as AverageRespTime max(time_serve) as MaxRespTime by url

index=myindex sourcetype=access_combined_wcookie status=200 
| bucket span=1h _time 
| stats count as hour_count by _time url 
| stats max(hour_count) as max_per_hour sum(hour_count) as total_count avg(time_serve) as AverageRespTime max(time_serve) as MaxRespTime by url

hope this clears your doubt

[shashank_24]

@anilchaithu ah yes you are right. Thanks. So how do i achieve it?

Because I want to display in a tabular format with along with the url's.

[anilchaithu]

@shashank_24 

try this 

| stats count as hour_count avg(time_serve) as avg_timeserve max(time_serve) as max_timeserve  by _time url
| stats max(hour_count) as max_per_hour sum(hour_count) as total_count avg(avg_timeserve) as AverageRespTime max(max_timeserve ) as MaxRespTime by url