Steve Fenton

Using Log Parser Studio to get requests per hour or minute

After a load test of some web servers, I needed to have a look through the IIS logs to find out how many requests per minute were being generated on a specific web farm server.

The following Log Parser Studio query will group the results by minute, but you can adjust the QUANTIZE call to pass in 3600 rather than 60 if you wanted per-hour numbers.

SELECT
    QUANTIZE(TO_TIMESTAMP(date, time), 60) AS M, 
    COUNT(*) AS Total,  
    SUM(sc-bytes) AS TotBytesSent 
FROM
    '[LOGFILEPATH]'
WHERE
    date > '2016-04-23'
GROUP BY M 
ORDER BY M

Web Log Importer

If you are using Web Log Importer, you can get the same information using the following query (the function will move dates to the nearest minute, the last argument is how many minutes – so in this case every “1” minute):

SELECT
    dbo.RoundToMinutes([date], [time], 1) AS M,
    COUNT(1) AS Total 
FROM
    LogEntry
WHERE
    [date] = '2020-03-10'
GROUP BY
    dbo.RoundToMinutes([date], [time], 1)
ORDER BY
    dbo.RoundToMinutes([date], [time], 1)

And it’s easy to switch from minutes to hours by changing the size of the round call, like so (rounds to “60” minute chunks):

SELECT
    dbo.RoundToMinutes([date], [time], 60) AS H,
    COUNT(1) AS Total 
FROM
    LogEntry
WHERE
    [date] = '2020-03-10'
GROUP BY
    dbo.RoundToMinutes([date], [time], 60)
ORDER BY
    dbo.RoundToMinutes([date], [time], 60)