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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...