How to analyze IIS logs with SQL

Find recent server errors

Start with status codes in the 500 range and return the URL, client, status and request duration.

SELECT TOP 100
  date, time, c_ip, cs_uri_stem,
  sc_status, sc_substatus, time_taken
FROM 'C:\inetpub\logs\LogFiles\W3SVC1\*.log'
WHERE sc_status >= 500
ORDER BY date DESC, time DESC;

Find the slowest URLs

SELECT cs_uri_stem, COUNT(*) AS requests,
  AVG(time_taken) AS avg_ms,
  MAX(time_taken) AS max_ms
FROM 'C:\inetpub\logs\LogFiles\W3SVC1\*.log'
GROUP BY cs_uri_stem
HAVING COUNT(*) > 20
ORDER BY avg_ms DESC;

Investigate a client IP

Filter by c_ip, then group by URL and status code to understand what the client requested.