Splunk Search

Peak hour count of most Visited Pages

Shashank_87
Explorer

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

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

Shashank_87
Explorer

@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 🙂

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

Shashank_87
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...