Using Log Parser to List All Blocked IP Requests
In a recent project we needed to block a series of IP addresses from accessing our website. IIS makes this easy with its IPv4 Address and Domain Restrictions feature, which lets the webmaster specify specific or ranges of IP addresses that are either allowed or denied access to the website. See Configure IPv4 Address and Domain Name Rules for more information.
After blocking the IP addresses of interest we wondered, how often are those blocked addresses attempting to access the website? Whenever IIS blocks an IP address it returns a particular HTTP status code - 403.6. Therefore, if we could search the IIS log files for all requests that returned a 403.6 status code we would know what banned IP addresses were attempting to access what pages and when.
Of course we weren’t at all interested in manually pouring through the log files. Fortunately, there is Log Parser. Log Parser is a free command-line tool from Microsoft for searching through IIS log files using a SQL-like syntax. We ended up using the following command, which provides the IP address, the requested URL, and the local date/time of the blocked request ordered from the most recent blocked request to the oldest. The results are outputted as a CSV file. (Note: the extra spaces and carriage returns in the below command are for readability only; remove this whitespace before attempting to run the command from the command line.)
"SELECT c-ip as IP,
cs-uri-stem as URL,
TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS DateTime
WHERE TO_STRING(sc-status) = '403'
AND TO_STRING(sc-substatus) = '6'
ORDER BY TO_LOCALTIME(TO_TIMESTAMP(date, time)) DESC"
Note the SQL-like syntax – very easy to read and understand for a DBA or developer who works regularly with SQL. Log Parser supports the standard SQL clauses, including GROUP BY clauses. Log Parser also supports a variety of output types. Above I request the data to be outputted as a CSV (see the –o:CSV switch) but I could have chosen the output as an XML file, a grid – even a chart!
For more on Log Parser, along with some common queries, check out the following resources:
There is also a Samples folder that is included when you install Log Parser with dozens of sample queries.