SQL – Log Parser Studio – IIS Logs

/* Compare file sizes of the last 10 days of IIS logs */
/* Log Type: FSLog */
SELECT Path,
	Size,
	LastWriteTime
FROM '[LOGFILEPATH]'
ORDER BY LastWriteTime ASC

/* User-Agent Report */
/* Log Type: IISW3CLOG */
SELECT DISTINCT cs(User-Agent),
	count(*) AS hits
FROM '[LOGFILEPATH]'
GROUP BY cs(User-Agent)
ORDER BY hits DESC

/* Hits by IP address */
/* Log Type: IISW3CLOG */
SELECT [c-ip],
	count([c-ip]) AS requestcount
FROM '[LOGFILEPATH]'
WHERE [cs-uri-stem] LIKE '%/%'
GROUP BY [c-ip]
ORDER BY count([c-ip]) DESC

/* All 500 errors to any IIS/.NET Web Service */
/* Log Type: IISW3CLOG */
SELECT [cs-uri-stem] AS Uri,
	[sc-status] AS HttpStatus,
	[sc-substatus] AS SubStatus,
	[sc-win32-status] AS Win32Status,
	COUNT(*) AS Total
FROM '[LOGFILEPATH]'
WHERE ([sc-status] = 500)
	AND ([cs-uri-stem] LIKE '%.asmx')
GROUP BY Uri,
	HttpStatus,
	SubStatus,
	Win32Status
ORDER BY Total DESC

/*  List only Win32 Error codes. Win32 Error codes are errors that 
     were returned to IIS by the OS or other application.  */
/* Log Type: IISW3CLOG */
SELECT [sc-win32-status] AS [Win32-Status],
	COUNT(*) AS Hits,
	WIN32_ERROR_DESCRIPTION([sc-win32-status]) AS Description
FROM '[LOGFILEPATH]'
WHERE [Win32-Status] <> 0
GROUP BY [Win32-Status]
ORDER BY Hits DESC

/* Count and sort all HTTP status codes */
/* Log Type: IISW3CLOG */
SELECT STRCAT(TO_STRING([sc-status]), STRCAT('.', TO_STRING([sc-substatus]))) AS STATUS,
	COUNT(*) AS Hits
FROM '[LOGFILEPATH]'
GROUP BY STATUS
ORDER BY Hits DESC

/* Requests Per Hour */
/* Log Type: IISW3CLOG */
SELECT QUANTIZE(TO_TIMESTAMP([date], [time]), 3600) AS Hour,
	COUNT(*) AS Total,
	SUM([sc-bytes]) AS TotBytesSent
FROM '[LOGFILEPATH]'
GROUP BY Hour
ORDER BY Hour

/* IIS: HTTP Method Totals with Times */
/* Log Type: IISW3CLOG */
SELECT [cs-method],
	COUNT(*) AS Total,
	MAX([time-taken]) AS MaxTime,
	AVG([time-taken]) AS AvgTime,
	MAX([sc-bytes]) AS MAXBytesSent,
	AVG([sc-bytes]) AS AvgBytesSent
FROM '[LOGFILEPATH]'
GROUP BY [cs-method]
ORDER BY Total DESC

/* Log Type: IISW3CLOG */
SELECT [cs-uri-stem],
	[cs-method],
	COUNT(*) AS Total,
	MAX([time-taken]) AS MaxTime,
	AVG([time-taken]) AS AvgTime,
	MAX([sc-bytes]) AS MaxBytes, 
	AVG([sc-bytes]) AS AvgBytes
FROM '[LOGFILEPATH]'
GROUP BY [cs-uri-stem],
	[cs-method]
ORDER BY Total DESC