Stored procedure dilemma

  • actinicblue

    Mr or Mrs. 500

    Points: 525

    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?

  • actinicblue

    Mr or Mrs. 500

    Points: 525

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply