In the past I have used Log Parser Studio to run SQL style queries against IIS logs, but it can take a fair bit of time to do this. Sometimes you just want to run a quick SQL query against the log data that IIS has collected. SQL Server is a super quick way ofContinue reading “Import IIS Log Files to SQL Server with Web Log Importer”
Tag Archives: iis
Secure Octopus Deploy with an Auto-Updating Let’s Encrypt Certificate
Octopus Deploy has a web portal that runs a self-hosted website on whatever port you specify. You can even have Octopus generate a self-signed certificate in order to use HTTPS when browsing the web portal… but if you connect other application that are strictly validating certificates, they won’t be too pleased with self-signed certificates. PurchasingContinue reading “Secure Octopus Deploy with an Auto-Updating Let’s Encrypt Certificate”
Using Log Parser Studio to Find Common 500 Errors
The following Log Parser Studio query will find common erroring URLs. You could adapt this to find common addresses for other status codes too. SELECT TOP 20 cs-uri-stem, COUNT(*) AS Total, MAX(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime FROM ‘[LOGFILEPATH]’ WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP(‘0000-01-02 00:00’, ‘yyyy-MM-dd HH:mm’)) AND sc-status = ‘500’ GROUP BY cs-uri-stem ORDERContinue reading “Using Log Parser Studio to Find Common 500 Errors”
Use Log Parser Studio To Get List Of Top IP Ranges
If you are getting suspicious traffic, it often comes from a range of IP addresses. The below queries get the count using the first three octets of an IP address, so for “192.168.0.1” it will return “192.168.0” and a count of all traffic for the 192.168.0.1/24 range (192.168.0.1 – 192.168.0.255). To get the first threeContinue reading “Use Log Parser Studio To Get List Of Top IP Ranges”
Log Parser Studio Custom IIS Log Queries
If you want to add my set of Log Parser Studio custom queries to your LPS library, follow these instructions… Note: if you want to merge these with your existing queries, just copy the inner nodes into your LPS library file – if you just want to use my custom set without searching through theContinue reading “Log Parser Studio Custom IIS Log Queries”
Using Log Parser Studio To Find Guilty IP Addresses From X-Forwarded-For
You may have seen how to find guilty IP addresses in my post Using Log Parser Studio to Find Guilty IP Addresses, but if you have enabled the logging of X-Forwarded-For IP addresses in IIS you may want to use this updated version, which gets the top offending IP addresses based on the X-Forwarded-For header:Continue reading “Using Log Parser Studio To Find Guilty IP Addresses From X-Forwarded-For”
Add X-Forwarded-For IP Address to IIS Logs
If you are using a load balancer, the chances are your IIS Logs are full of entries with the IP address of your load balancer, rather than the IP address of your end user. Most load balancers will allow you to send the IP address in an alternate header, for example the X-Forwarded-For header. ThisContinue reading “Add X-Forwarded-For IP Address to IIS Logs”
Using Log Parser Studio To Find User Agents
If you want to find out which user agents are used to visit your site, you can use the below query. You can use this to find out about browsers, and honest-ish robots (i.e. ones that identify themselves via the user agent). SELECT cs(User-Agent), count(cs(User-Agent)) as requestcount FROM ‘[LOGFILEPATH]’ WHERE date = ‘2016-08-02’ GROUP BYContinue reading “Using Log Parser Studio To Find User Agents”
Using Log Parser Studio to Get Requests Per Hour or Minute
After a load test of some web servers, I needed to have a look through the IIS logs to find out how many requests per minute were being generated on a specific web farm server. The following Log Parser Studio query will group the results by minute, but you can adjust the QUANTIZE call toContinue reading “Using Log Parser Studio to Get Requests Per Hour or Minute”
Using Log Parser Studio to Get Request By Host Name
I am working on an application that accepts request for many different domain names, and sends back different content for each one. This means there is one log file for multiple logical sites, because they all run within the same instance of the web application. To make diagnostics easier, I have enabled the “cs-host” columnContinue reading “Using Log Parser Studio to Get Request By Host Name”