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
Revered Legend

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
Revered Legend

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!

Splunk Lantern | Spotlight on Security: Adoption Motions, War Stories, and More

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...

Tech Talk | One Log to Rule Them All

One log to rule them all: how you can centralize your troubleshooting with Splunk logs We know how important ...