When monitoring high-traffic web applications, IIS logs contain invaluable data about client behavior. Here are some powerful LogParser queries I've developed through practical experience:

SELECT TOP 100
cs-uri-stem AS Url,
COUNT(*) AS Hits,
SUM(sc-bytes) AS TotalBytes,
AVG(time-taken) AS AvgResponseTime
FROM ex*.log
GROUP BY Url
HAVING COUNT(*) > 50
ORDER BY TotalBytes DESC

SELECT TOP 20
cs(User-Agent) AS UserAgent,
COUNT(*) AS Requests,
SUM(sc-bytes) AS BytesTransferred,
MIN(time) AS FirstRequest,
MAX(time) AS LastRequest
FROM ex*.log
WHERE cs(User-Agent) NOT LIKE '%Mozilla%' 
  AND cs(User-Agent) NOT LIKE '%Google%'
  AND cs(User-Agent) NOT LIKE '%Bing%'
  AND cs(User-Agent) NOT LIKE '%Apple%'
GROUP BY UserAgent
ORDER BY BytesTransferred DESC

This query finds clients making rapid sequential requests:

SELECT 
c-ip AS ClientIP,
COUNT(*) AS RequestsPerMinute,
MIN(time) AS FirstRequest,
MAX(time) AS LastRequest,
DATEDIFF(ss, MIN(time), MAX(time)) AS DurationSeconds,
COUNT(*)/(DATEDIFF(ss, MIN(time), MAX(time))/60.0) AS RequestsPerMinute
FROM ex*.log
GROUP BY ClientIP
HAVING COUNT(*) > 100 AND DATEDIFF(ss, MIN(time), MAX(time)) < 300
ORDER BY RequestsPerMinute DESC

For API endpoints that shouldn't be called frequently:

SELECT TOP 50
c-ip AS ClientIP,
cs-uri-stem AS Endpoint,
COUNT(*) AS Calls,
AVG(time-taken) AS AvgResponseTime
FROM ex*.log
WHERE cs-uri-stem LIKE '/api/%'
GROUP BY ClientIP, Endpoint
HAVING COUNT(*) > 100
ORDER BY Calls DESC

SELECT 
cs-uri-stem AS Url,
sc-status AS StatusCode,
COUNT(*) AS Occurrences
FROM ex*.log
WHERE sc-status >= 400
GROUP BY Url, StatusCode
ORDER BY Occurrences DESC

SELECT
TO_STRING(time, 'yyyy-MM-dd') AS Day,
TO_STRING(time, 'hh') AS Hour,
COUNT(*) AS TotalRequests,
SUM(sc-bytes) AS TotalBytes
FROM ex*.log
GROUP BY Day, Hour
ORDER BY Day, Hour

Combine multiple conditions to pinpoint problematic traffic:

SELECT
c-ip AS ClientIP,
cs(User-Agent) AS UserAgent,
COUNT(*) AS Requests,
SUM(sc-bytes) AS BytesSent
FROM ex*.log
WHERE 
  (sc-status = 200) AND
  (cs-uri-stem NOT LIKE '%.css' AND cs-uri-stem NOT LIKE '%.js') AND
  (cs(User-Agent) NOT LIKE '%bot%' AND cs(User-Agent) NOT LIKE '%crawl%')
GROUP BY ClientIP, UserAgent
HAVING COUNT(*) > 1000
ORDER BY BytesSent DESC

Save query results to CSV for integration with other tools:

logparser.exe "SELECT * INTO report.csv FROM ex*.log 
WHERE c-ip='192.168.1.100'" -o:CSV

As web applications scale, analyzing IIS logs becomes crucial for identifying problematic traffic patterns. Microsoft's LogParser proves invaluable for detecting:

• Aggressive web crawlers
• Excessive refresh patterns
• Malicious scraping attempts
• Bandwidth-hungry clients

These fundamental query structures should be in every administrator's toolkit:

High-Value Target Identification

SELECT TOP 50
cs-uri-stem as TargetURL,
COUNT(*) as RequestCount,
SUM(sc-bytes) as TotalBytesSent,
MAX(time) as LastAccessed
FROM ex*.log
GROUP BY TargetURL
ORDER BY TotalBytesSent DESC

Client Fingerprinting

SELECT
c-ip as ClientIP,
cs(User-Agent) as UserAgent,
COUNT(*) as Requests,
SUM(sc-bytes) as BytesTransferred,
VAR(time) as RequestIntervalVariance
FROM u_ex*.log
GROUP BY ClientIP, UserAgent
HAVING COUNT(*) > 1000
ORDER BY BytesTransferred DESC

Temporal Pattern Analysis

SELECT
TO_LOCALTIME(QUANTIZE(time, 3600)) as TimeSlot,
c-ip as ClientIP,
COUNT(*) as RequestRate,
SUM(sc-bytes) as HourlyBandwidth,
STDEV(TO_TIMESTAMP(time)) as TemporalDeviation
FROM *.log
GROUP BY TimeSlot, ClientIP
HAVING COUNT(*) > 500
ORDER BY RequestRate DESC

Behavioral Fingerprinting

SELECT
c-ip as ClientIP,
COUNT(DISTINCT cs-uri-stem) as UniqueURIs,
AVG(sc-bytes) as AvgResponseSize,
PERCENTILE(sc-bytes, 90) as P90ResponseSize,
SUM(CASE WHEN sc-status >= 400 THEN 1 ELSE 0 END) as ErrorCount
FROM iis*.log
GROUP BY ClientIP
HAVING COUNT(*) > 500
ORDER BY UniqueURIs DESC

For production environments, consider these enhancements:

• Schedule queries via Windows Task Scheduler
• Output to CSV for automated processing
• Combine with PowerShell for alerting
• Maintain historical baselines for comparison

This PowerShell script automates daily log analysis:

$logPath = "C:\inetpub\logs\LogFiles\W3SVC1\"
$outputPath = "C:\Monitor\DailyReport_$(Get-Date -Format 'yyyyMMdd').csv"

logparser.exe "
SELECT TOP 20
c-ip as ClientIP,
SUBSTR(cs(User-Agent), 0, 120) as UserAgent,
COUNT(*) as Requests,
SUM(sc-bytes) as TotalBytes,
MIN(time) as FirstSeen,
MAX(time) as LastSeen
FROM $($logPath)u_ex*.log
GROUP BY ClientIP, UserAgent
HAVING COUNT(*) > 1000
ORDER BY TotalBytes DESC
" -i:IISW3C -o:CSV > $outputPath