IIS Log Parser - 需要查询以查找“请求总数”> x秒“ /“总请求数”按URL分组 [英] IIS Log Parser - Need Query to find "total requests taking > x secs" / "Total Requests" grouped by URL
问题描述
我支持偶尔出现性能问题的应用程序。客户想要知道页面的缓慢频率。
I am supporting an application that is having occasional performance issues. The client wants to know how often a page is slow.
即。页面占用的总时间大于x秒/页面请求总数
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.
SQL中的这样的东西可能会起作用但在IIS日志解析器中不起作用。
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
推荐答案
这里的问题是你需要两个查询。
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
计算时间> X秒
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
不幸的是,LogParser中没有对JOIN的支持。
Unfortunately there is no support for JOIN's in LogParser.
您可以做的是将两个查询的结果导入SQL数据库并在那里运行查询:
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
这篇关于IIS Log Parser - 需要查询以查找“请求总数”> x秒“ /“总请求数”按URL分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!