IIS Log SQL Examples

LogViewPlus has a built-in dashboard with about 8 different reports to display data from an IIS log file, but the power of using SQL to generate reports lies in its ability to dynamically adapt to suit your needs.

We want to give you some ideas of what you can achieve by using SQL to analyze your log files.  All of the examples below will work with the LogViewPlus IIS default parser configuration.   You can copy and paste these examples, but we hope they provide inspiration to help you dig deeper.

Most Active Users

We kept this query out of our built-in report because the REVERSEDNS function can be very slow.  That said - it's also very powerful and the performance impact can be limited by restricting the amount of data processed.

SELECT REVERSEDNS([c-ip]) As Name, [c-ip] As Machine, Hits FROM (
     SELECT TOP 5 [c-ip], COUNT(*) As Hits
     FROM CurrentView
     GROUP BY [c-ip]
     ORDER BY Hits DESC
)

Request Methods

SELECT cs-method As Method, COUNT(*) As Hits
FROM CurrentView
GROUP BY Method

Peak Traffic by Hour

SELECT DATEPART(hour, [Timestamp]) AS Hour, COUNT(*) AS Requests
FROM CV
GROUP BY DATEPART(hour, [Timestamp])
ORDER BY Requests DESC 

Popular User Agent

SELECT COUNT(*) AS Total, [cs_User-Agent_]
FROM CV
GROUP BY [cs_User-Agent_]
ORDER BY Total DESC

Error Status Codes

SELECT TOP 5 sc-status, sc-substatus, sc-win32-status, COUNT(*) AS ErrorCount
FROM CV
WHERE sc-status NOT LIKE '2%'
GROUP BY sc-status, sc-substatus, sc-win32-status
ORDER BY ErrorCount DESC

Top Referring Domains

SELECT COUNT(*) AS Referrals, cs_Referer_ AS Source
FROM CV
WHERE cs_Referer_ IS NOT NULL
GROUP BY cs_Referer_
ORDER BY Referrals DESC

Slowest Requests

SELECT TOP 10 Timestamp, [cs-uri-stem], [time-taken]
FROM CV
ORDER BY [time-taken] DESC

Unique Visitors

SELECT COUNT(DISTINCT [c-ip]) AS UniqueVisitors
FROM CV

Most Popular Pages

SELECT COUNT(*) AS PageHits, [cs-uri-stem]
FROM CV
GROUP BY [cs-uri-stem]
ORDER BY PageHits DESC

Authentication Analysis

SELECT TOP 5 COUNT(*) AS LoginAttempts, cs-username
FROM CV
WHERE cs-username IS NOT NULL
GROUP BY cs-username
ORDER BY LoginAttempts DESC

Traffic Source Analysis

SELECT TOP 5 COUNT(*) AS Visits, [cs_Referer_] AS Referer~
FROM CurrentView
WHERE [Referer] IS NOT NULL AND [Referer] NOT LIKE '%yourwebsite.com%'
GROUP BY [Referer]
ORDER BY Visits DESC

Most Active Users by IP Address

SELECT TOP 5 COUNT(*) AS RequestCount, [c-ip]
FROM CV
GROUP BY [c-ip]
ORDER BY RequestCount DESC

Analysis of Response Status Distribution

SELECT [sc-status], COUNT(*) AS StatusCount
FROM CV
GROUP BY [sc-status]
ORDER BY StatusCount DESC

Request Types by Time

SELECT DATEPART(hour, Timestamp) AS Hour, cs-method, COUNT(*) AS MethodCount
FROM CV
GROUP BY DATEPART(hour, Timestamp), cs-method
ORDER BY Hour, MethodCount DESC

Server Load Analysis

SELECT AVG([time-taken]) AS AverageTime, MAX([time-taken]) AS MaxTime
FROM CV

File Type Analysis

SELECT TOP 5 RIGHT([cs-uri-stem], CHARINDEX('.', REVERSE([cs-uri-stem])) - 1) AS FileType, COUNT(*) AS Requests
FROM CV
WHERE CHARINDEX('.', [cs-uri-stem]) > 0
GROUP BY RIGHT([cs-uri-stem], CHARINDEX('.', REVERSE([cs-uri-stem])) - 1)
ORDER BY Requests DESC

Failed Login Attempts

SELECT TOP 5 [cs-username], [c-ip], COUNT(*) AS FailedAttempts
FROM CV
WHERE sc-status = '401'
GROUP BY [cs-username], [c-ip]
ORDER BY FailedAttempts

Most Common Sub-Status Codes

SELECT TOP 5 [sc-substatus], COUNT(*) AS Occurrences
 FROM CV
GROUP BY [sc-substatus]
ORDER BY Occurrences DESC

Web Requests by Result Code

SELECT [cs-method] AS Method, [sc-status] AS Status, COUNT(*) AS Count
FROM CV
GROUP BY [cs-method], [sc-status]
ORDER BY Method ASC, Count DESC

Average Number of Requests Per User

SELECT AVG(RequestCount) AS AverageRequests
FROM (SELECT [c-ip], COUNT(*) AS RequestCount
      FROM CV
      GROUP BY [c-ip]) AS UserRequests

Users with Error Codes

SELECT TOP 5 [c-ip], sc-status, COUNT(*) AS ErrorCount
FROM CV
WHERE sc-status LIKE '4%' OR sc-status LIKE '5%'
GROUP BY [c-ip], sc-status
ORDER BY ErrorCount DESC

Analysis of Error Codes by Date

SELECT FORMAT(Timestamp, 'yyyy-MM-dd') AS Date, sc-status, COUNT(*) AS StatusCount
FROM CV
WHERE sc-status NOT LIKE '2%'
GROUP BY FORMAT(Timestamp, 'yyyy-MM-dd'), sc-status

Analysis of Error Codes by Hour

SELECT DATEPART(hour, [Timestamp]) AS Hour, sc-status, COUNT(*) AS StatusCount
FROM CV
WHERE sc-status NOT LIKE '2%'
GROUP BY DATEPART(hour, [Timestamp]) AS Hour, sc-status

Top Visited Pages by Unique Visitors

SELECT  TOP 5 COUNT(DISTINCT [c-ip]) AS UniqueVisitors, [cs-uri-stem]
FROM CV
GROUP BY [cs-uri-stem]
ORDER BY UniqueVisitors DESC

Query String Analysis

SELECT TOP 5 COUNT(*) AS QueryCount, [cs-uri-query]
FROM CV
WHERE [cs-uri-query] IS NOT NULL
GROUP BY [cs-uri-query]
ORDER BY QueryCount DESC

Performance Analysis by Server Port

SELECT [s-port] AS Port, AVG([time-taken]) AS AvgTimeTaken, COUNT(*) AS Requests
FROM CV
GROUP BY [s-port]
ORDER BY AvgTimeTaken DESC