使用 Web 服务器日志中的数据时出现 SQL Server 日期范围问题

Question

我一直在使用以下命令将原始 IIS 日志文件导入到 SQL Server 表中日志解析器工具已经使用几个月了。这样做是为了能够写入SSRS 根据此日志数据进行报告。

我正在编写的一份报告应该确定来自每个唯一 IP 地址的访问。访问被定义为 IP 地址命中网站上的一个页面，然后在每个请求一小时内再发出 4 个请求其他。所有 5 个请求均在一次网站访问中完成。后来那个晚上同一个 IP 地址访问该网站，只不过现在已经是 3 小时后了，所以我们将来自同一 IP 的新事件视为新访问。这是一个示例数据:

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

通过查看上面的数据，我可以轻松看出 10.1.1.100 IP地址已访问该网站两次，每次访问有 5 次点击。然而，我是不知道如何在 SQL 代码中表达这一点。有没有简单的方法可以组并按 IP 地址计算这些日期范围？

我了解可以使用以下工具捕获此信息AWStats，但我没有能力在上面安装 Perl我们使用的系统。

Answer 1

试运行下面的代码。该代码对来自每个 IP 地址的访问进行分组和编号。然后查看与“阈值”值相比有多少“uristem”命中。我在名为“Foo”的表上测试了代码，您需要在运行测试之前检查表和列名称。

DECLARE @threshold INT;  
SET @threshold = 4;  --this number should not include the initial visit
DECLARE @lookbackdays int; 
SET @lookbackdays = 300; 

;WITH postCTE as  
(  
SELECT   
    ipaddress,  
    uristem,  
    requestdatetime,  
    RowNumber = ROW_NUMBER() OVER (ORDER BY ipaddress,requestdatetime ASC)  
FROM  
    Foo  --put your table name here
WHERE  
    requestdatetime > GETDATE() - @lookbackdays 
)  
--select * from postCTE

SELECT   
    p1.ipaddress AS [ipaddress],   
    p2.RowNumber - p1.RowNumber +1 AS [Requests], 
    p1.requestdatetime AS [DateStart]
FROM  
    postCTE p1  
INNER JOIN  
    postCTE p2   
    ON p1.ipaddress = p2.ipaddress   
    AND p1.Rownumber = p2.RowNumber - (@threshold )  
WHERE  
    DATEDIFF(minute,p1.requestdatetime,p2.requestdatetime) <= 60 

我在 SQL 2008 上的测试输出是

ipaddress   Requests    DateStart
10.1.1.100  5   2010-10-15 13:30:30.000
10.1.1.100  5   2010-10-15 16:55:10.000