November 9, 2001 at 5:20 pm
I'm looking to write a stored procedure based on a Proxy log file. I have one field called URI. This field contains all of the different websites that have been hit as well as gif images and other items that are logged by an ISA server. I need to filter out anything that is not a page. So I'm going to keep any records containing the ending of .com, or .%htm%. After it is filtered to just pages I need to count the sites that have been hit more than once, as well as list the site and order them by top ten sites. Ultimately I need a result table that has the page name and how many times it appears in the log. Any ideas?
November 9, 2001 at 5:56 pm
I figured it out. Kind of embarrassed at how easy it was. Except for ordering by top ten here is the code I came up with. If you've got a better way let me know. Thanks.
Select URI, count(uri) as total
FROM WebProxyLog
WHERE uri LIKE '%.com/' or uri LIKE '%.%htm%'
Group by URI
Order By Total DESC
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply