Databases
StackOverflow2013
Postsdbo
POSQL Server Date Range Problem When Using Data From Web Server Logs
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Date Range Problem When Using Data From Web Server Logs
    primarykey
    data
    text
    <p>I have been importing my raw IIS log files into a SQL Server table using the Log Parser tool for several months now. This is done to enable the writing of SSRS reports based on this log data.</p> <p>One of the reports that I am working on is supposed to determine the number of Visits from each unique IP address. A Visit is defined as an IP address hitting a page on the site and then making 4 more requests within an hour of each other. All of the 5 requests are within one Visit to the site. Later on that night the same IP address hits the site, except that now it's 3 hours later, so we count this new activity from the same IP as a new Visit. Here is a sample of the data:</p> <pre><code>IPAddress, RequestDateTime, UriStem 10.1.1.100, 2010-10-15 13:30:30, / 10.1.1.100, 2010-10-15 13:30:31, /style.css 10.1.1.100, 2010-10-15 13:30:31, /script.js 10.1.1.100, 2010-10-15 13:30:32, /funny.gif 10.1.1.100, 2010-10-15 13:30:33, /picture.jpg 10.1.1.101, 2010-10-15 13:40:50, /page2.html 10.1.1.101, 2010-10-15 13:40:51, /style.css 10.1.1.102, 2010-10-15 14:10:20, /page4.html 10.1.1.102, 2010-10-15 14:10:21, /style.css 10.1.1.100, 2010-10-15 16:55:10, / 10.1.1.100, 2010-10-15 16:55:11, /style.css 10.1.1.100, 2010-10-15 16:55:11, /script.js 10.1.1.100, 2010-10-15 16:55:12, /funny.gif 10.1.1.100, 2010-10-15 16:55:13, /picture.jpg </code></pre> <p>By looking at the data above I can easily discern that the <code>10.1.1.100</code> IP address has visited the site twice and had 5 hits on each visit. However, I am at a loss as to how to express that in SQL code. Is there an easy way to group and count these date ranges by IP address?</p> <p>I understand that this information can be captured by using tools such as AWStats, but I do not have the luxury of being able to install Perl on the systems we use.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USλ Jonas Gorauskas
    1. USλ Jonas Gorauskas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server Date Range Problem When Using Data From Web Server Logs
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server Date Range Problem When Using Data From Web Server Logs
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server Date Range Problem When Using Data From Web Server Logs
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload