IIS 日志解析器 - 需要查询来查找“总请求数>” x秒” /“总请求数”按 URL 分组

发布于 2024-10-11 05:24:13 字数 274 浏览 5 评论 0原文

我正在支持一个偶尔出现性能问题的应用程序。客户想知道页面缓慢的频率。

即页面花费超过 x 秒的总时间/页面的请求总数

我想编写一个查询来获取所需的数据。

像这样的东西在 SQL 中可能会起作用，但在 IIS 日志解析器中不起作用。

select URL, count(case when time > 100  then 1 else null end), count(*)
from   table1
group by URL

原文

I am supporting an application that is having occasional performance issues. The client wants to know how often a page is slow.

i.e. Total times a page took greater than x secs / Total number of requests for the page

I would like to write a single query to fetch the desired data.

Something like this in SQL would probably work but isn't working in IIS Log parser.

select URL, count(case when time > 100  then 1 else null end), count(*)
from   table1
group by URL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一个人练习一个人 2024-10-18 05:24:13

这里的问题是您需要两个查询。

无论花费多少时间，都可以计算每页的请求总数

SELECT cs-uri-stem, COUNT(*) AS 所有请求 
来自 ex*.log 
按 cs-uri-stem 分组

用于计算花费时间 > 的页面数。 X秒

SELECT cs-uri-stem, COUNT(*) 作为总请求数
来自 ex*.log
所花费的时间> 1000 <- time_taken 是毫秒
按 cs-uri-stem 分组

您所追求的结果需要 JOIN：

SELECT a.cs-uri-stem, COUNT(*) as total-requests, b.all-requests
FROM ex*.log AS a
JOIN (
    SELECT cs-uri-stem, COUNT(*) AS all-requests 
    FROM ex*.log 
    GROUP BY cs-uri-stem
) AS b ON b.cs-uri-stem = a.cs-uri-stem
WHERE a.time-taken >1000 
GROUP BY a.cs-uri-stem

不幸的是 LogParser 中不支持 JOIN。

您可以做的是将两个查询的结果导入 SQL 数据库并在那里运行查询：

SELECT a.cs-uri-stem, COUNT(*) as total-requests, b.all-requests
FROM long_running_pages AS a
JOIN all_pages_grouped b ON ( a.cs-uri-stem = b.cs-uri-stem)
GROUP BY a.cs-uri-stem

The problem here is that you need two queries.

One to count the total number of requests per page regardless of time taken

SELECT cs-uri-stem, COUNT(*) AS all-requests 
FROM ex*.log 
GROUP BY cs-uri-stem

One to count the number of pages where time-taken > X seconds

SELECT cs-uri-stem, COUNT(*) as total-requests
FROM ex*.log
WHERE time-taken > 1000 <- time_taken is milliseconds
GROUP BY cs-uri-stem

The result you're after would require a JOIN:

SELECT a.cs-uri-stem, COUNT(*) as total-requests, b.all-requests
FROM ex*.log AS a
JOIN (
    SELECT cs-uri-stem, COUNT(*) AS all-requests 
    FROM ex*.log 
    GROUP BY cs-uri-stem
) AS b ON b.cs-uri-stem = a.cs-uri-stem
WHERE a.time-taken >1000 
GROUP BY a.cs-uri-stem

Unfortunately there is no support for JOIN's in LogParser.

What you could do is import the results of both queries into a SQL Database and run the query there:

SELECT a.cs-uri-stem, COUNT(*) as total-requests, b.all-requests
FROM long_running_pages AS a
JOIN all_pages_grouped b ON ( a.cs-uri-stem = b.cs-uri-stem)
GROUP BY a.cs-uri-stem

回复收藏 0 原文

~没有更多了~