Peak hour count of most Visited Pages

[Shashank_87]

Hi, I am working on a query to get the peak hour count of of the top 100 visited pages on my website and i want this together in a single table.
In simple terms what i want is a table with three columns -
1. req_content (visited page)
2. Total count of this visited page (suppose in last 7 days)
3. Peak hour count of this visited page (suppose in last 7 days).

Mainly the 1st and 3rd column and even if we don't get total count that's okay. I have this below query which gives the top 100 visited pages but I also want the peak hour count alongside it in a separate column

index=test sourcetype=access_combined requested_content="/*" NOT (images OR js OR css OR png OR gif OR json OR jpg OR woff OR eot OR ico OR ttf OR svg OR pdf OR php OR jpeg OR txt) status=200
| stats count by req_content
| sort - count limit=100

Any help is appreciated.
@David @to4kawa @Sukisen1981 @gcusello

[somesoni2 · Accepted solution]

Try this

index=test sourcetype=access_combined requested_content="/*" NOT (images OR js OR css OR png OR gif OR json OR jpg OR woff OR eot OR ico OR ttf OR svg OR pdf OR php OR jpeg OR txt) status=200
| bucket span=1h _time
| stats count as hour_count by _time req_content
| stats sum(hour_count) as total_count max(hour_count) as max_per_hour by req_content
| sort - total_count limit=100

[Shashank_87]

@somesoni2 This is brilliant stuff. Thank you very much. This is exactly what i was looking for. Only disappointed that i couldn't think of this.
Thanks again 🙂

[skoelpin]

Use the date_hour and date_day fields like this..

index=test sourcetype=access_combined requested_content="/*" NOT (images OR js OR css OR png OR gif OR json OR jpg OR woff OR eot OR ico OR ttf OR svg OR pdf OR php OR jpeg OR txt) status=200
| stats count by date_hour, req_content, date_day
| eventstats max(date_hour) AS date_hour by date_day
| sort - count limit=100

This is untested and may need to be adjusted

[Shashank_87]

Thanks @skoelpin for your response. I have just got it working with the query commented by somesoni. But thanks anyways for looking.