These examples assume the log format used is W3C Extended. If a different format is used, the field names may need to be changed.

Get the number of requests and total bytes sent during each hour

SELECT	TO_STRING(TO_TIMESTAMP(date, time), 'yyyy-MM-dd hh') AS Hour, 
	COUNT(*) AS Total,  
	SUM(sc-bytes) AS TotBytesSent 
FROM ex*.log 
GROUP BY Hour 
ORDER BY Hour

Get the Top 20 verbs with the maximum and average time taken, and with the average number of bytes sent

SELECT TOP 20 	cs-method, 
		COUNT(*) AS Total, 
		MAX(time-taken) AS MaxTime, 
		AVG(time-taken) AS AvgTime, 
		AVG(sc-bytes) AS AvgBytesSent 
FROM ex*.log 
GROUP BY cs-method 
ORDER BY Total DESC

Get the Top 20 URIs with the maximum and average time taken, and with the average number of bytes sent

SELECT TOP 20 	cs-uri-stem, 
		COUNT(*) AS Total, 
		MAX(time-taken) AS MaxTime, 
		AVG(time-taken) AS AvgTime, 
		AVG(sc-bytes) AS AvgBytesSent 
FROM ex*.log 
GROUP BY cs-uri-stem
ORDER BY Total DESC

Get the full HTTP status codes by number of hits

SELECT 	STRCAT(TO_STRING(sc-status), STRCAT('.',TO_STRING(sc-substatus))) AS Status, 
	COUNT(*) AS Total 
FROM ex*.log 
GROUP BY Status 
ORDER BY Total DESC

For each ASP error, get the Url and the ASP script line number

SELECT	SUBSTR(
		STRCAT(	cs-uri-stem,
			REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?', cs-uri-query))
	      		),
		0,
		INDEX_OF(	STRCAT(	cs-uri-stem, 
				REPLACE_IF_NOT_NULL(cs-uri-query,STRCAT('?',cs-uri-query))
					),
				'|'
				) 
		) AS Uri, 
	SUBSTR(
		cs-uri-query,
		ADD(1, LAST_INDEX_OF(cs-uri-query,'|'))
		) AS ErrorMsg, 
	REPLACE_IF_NOT_NULL(
				cs-uri-query,
				substr(	cs-uri-query, 					
					ADD(INDEX_OF(cs-uri-query,'|'),1), 
					INDEX_OF(	SUBSTR(	cs-uri-query,
								ADD(INDEX_OF(cs-uri-query,'|'),1)
								),
							'|'
							)
					)
			) AS LineNo, 
	COUNT(*) AS Total 
FROM ex*.log 
WHERE (sc-status = 500) AND (cs-uri-stem LIKE '%.asp') 
GROUP BY Uri,ErrorMsg,LineNo 
ORDER BY Total DESC

Get requests and full status by number of hits

SELECT 	STRCAT(	cs-uri-stem, 
		REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?',cs-uri-query))
		) AS Request, 
	STRCAT(	TO_STRING(sc-status), 		
		STRCAT(	'.',
			REPLACE_IF_NULL(TO_STRING(sc-substatus),
					'?'
					)
			)
		) AS Status, 
	COUNT(*) AS Total 
FROM ex*.log 
WHERE (sc-status >= 400) 
GROUP BY Request, Status 
ORDER BY Total DESC

Get hit counts for each extension

SELECT  SUBSTR(	cs-uri-stem, 
		LAST_INDEX_OF(cs-uri-stem,'.'),
		STRLEN(cs-uri-stem)
		) AS Extension, 
	COUNT(*) AS Total 
FROM ex*.log 
GROUP BY Extension 
ORDER BY Total DESC

Get authentication and authorization failures

SELECT 	cs-username, 
	sc-status, 
	COUNT(*) AS Total 
FROM ex*.log 
WHERE cs-username IS NOT NULL AND sc-status>400 AND sc-status < 404 
GROUP BY cs-username,sc-status 
ORDER BY Total DESC

Get logon failures from the Security Event Log

SELECT 	STRCAT(	SUBSTR(	SUBSTR(	Strings, 
				ADD(INDEX_OF(Strings,'|'),1), 
				INDEX_OF( 	SUBSTR(	Strings,
							ADD(INDEX_OF(Strings,'|'),1)
							),
						'|'
					)
				),
			0, 			
			INDEX_OF(	SUBSTR(	Strings, 
						ADD(INDEX_OF(Strings,'|'),1), 
						INDEX_OF(	SUBSTR(	Strings,
									ADD(INDEX_OF(Strings,'|'),1)
									),
								'|'
							)
						),
					'.'
					)
			), 
		STRCAT(	'\\', 
			SUBSTR(	Strings,
				0,
				INDEX_OF(Strings,'|')
				) 
			) 
		) AS User, 
		COUNT(*) AS Total 
FROM Security 
WHERE EventType = 16 AND EventCategory = 2 
GROUP BY User 
ORDER BY Total DESC

Get logon successes from the Security Event Log

SELECT 	STRCAT(	SUBSTR(	SUBSTR(	Strings, 
				ADD(INDEX_OF(Strings,'|'),1), 
				INDEX_OF( 	SUBSTR(	Strings,
							ADD(INDEX_OF(Strings,'|'),1)
							),
						'|'
					)
				),
			0, 			
			INDEX_OF(	SUBSTR(	Strings, 
						ADD(INDEX_OF(Strings,'|'),1), 
						INDEX_OF(	SUBSTR(	Strings,
									ADD(INDEX_OF(Strings,'|'),1)
									),
								'|'
							)
						),
					'.'
					)
			), 
		STRCAT(	'\\', 
			SUBSTR(	Strings,
				0,
				INDEX_OF(Strings,'|')
				) 
			) 
		) AS User, 
		COUNT(*) AS Total 
FROM Security 
WHERE EventType = 8 AND EventCategory = 2 
GROUP BY User 
ORDER BY Total DESC

Get the clients whose requests have been rejected by UrlScan

SELECT	ClientIP,
	COUNT(*)
FROM URLSCAN
WHERE Comment LIKE 'Url%'
GROUP BY ClientIP

Get all the UrlScan comments

SELECT DISTINCT Comment
FROM URLSCAN

Get the number of hits for each IIS AppPool

SELECT 	SUBSTR(	User\u0020Data,
		ADD(	INDEX_OF(User\u0020Data, '"'),
			1
			),
		LAST_INDEX_OF(	SUBSTR(	User\u0020Data,
					ADD(	INDEX_OF(User\u0020Data, '"'),
						1
						)
					),
				'"'		
				)
		) AS AppPool,
	COUNT(*) AS Total
FROM *.csv
WHERE Event\u0020Name='HttpRequest' AND Type='Deliver'
GROUP BY AppPool
ORDER BY Total DESC

TEXTWORD Input: Get a list of all the words in these text files ordered by number of times they appear
To use: "LogParser file:Words.sql -i:TEXTWORD"

SELECT	Text,
	COUNT(*) AS Total
FROM C:\*.txt
GROUP BY Text
ORDER BY Total DESC

FS Input: Get a list of all the hidden files in the C: drive ordered by their size
To use: "LogParser file:HiddenFiles.sql -i:FS"

SELECT 	Path,
	Size
FROM C:\*.*
WHERE NOT Attributes LIKE '%D%' AND Attributes LIKE '%H%'
ORDER BY Size DESC

FS Input: Get the distribution of file name lengths in the C: drive
To use: "LogParser file:FilenameLenghts.sql -i:FS"

SELECT 	STRLEN(Name) AS NameLen,
	COUNT(*) AS Total
FROM C:\*.*
GROUP BY NameLen
ORDER BY Total

Get the status code in the "status.substatus" form:

STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS Status

Get the request in the "uri-stem?uri-query" form:

STRCAT(cs-uri-stem, REPLACE_IF_NOT_NULL(cs-uri-query,STRCAT('?',cs-uri-query))) AS Request

Get the request extension:

SUBSTR(cs-uri-stem, LAST_INDEX_OF(cs-uri-stem,'.'), STRLEN(cs-uri-stem)) AS Extension