List of top URLs with hourly count > 50

[Shashank_87]

Hi, I have a ask where I need to find out top 100 URL's who have hourly hits more than 50 on the server means if a particular URL is requested more than 50 times in an hour then I need to list it.
And I need to list these kind of top 100 URL's which are most visited.
Any help is appreciated. Below is the query I have but it is not giving what i want -

index=temp_index source="/app/request.log" host="server-1b*" GET
| rex field=_raw "GET (?[^\s]+)"
| bucket span=1h _time
| stats count as hour_count by _time requested_content

[somesoni2]

Try like this

index=temp_index source="/app/request.log" host="server-1b*" GET
| rex field=_raw "GET (?<requested_content>[^\s]+)"
| bucket span=1h _time
| stats count as hour_count by _time requested_content
| where hour_content>50 
| sort 100 -hour_content

OR

index=temp_index source="/app/request.log" host="server-1b*" GET
| rex field=_raw "GET (?<requested_content>[^\s]+)"
| bucket span=1h _time
| stats count as hour_count by _time requested_content
| where hour_content>50 | stats max(hour_content) as hour_content by requested_content
| sort 100 -hour_content

[Shashank_87]

@somesoni2 Hi, this is also one of the solution and works in my situation but it gives multiple rows with the same URL which is fine because duplicates can be removed.
Thanks very much for the response.

[manjunathmeti · Accepted solution]

Hi @Shashank_87,

If you are extracting URL from _raw and counting it then try this:

index=temp_index source="/app/request.log" host="server-1b*" GET
| rex field=_raw "GET (?<URL>[^\s]+)"
| bucket span=1h _time
| stats count as hour_count by _time URL
| where hour_count > 50
| top 100 URL

[Shashank_87]

@manjunathmeti Hi Manju, this has worked perfectly. Thanks very much.

[manjunathmeti]

You're welcome!