Steve Fenton

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:

SELECT
    X-Forwarded-For,
    count(X-Forwarded-For) as requestcount
FROM
    '[LOGFILEPATH]'
WHERE
    date = '2016-08-08' 
GROUP BY
    X-Forwarded-For
ORDER BY
    count(X-Forwarded-For) DESC

Don’t forget to change the date when you run this query.

Web Log Importer

If you are using Web Log Importer, you can get the same information using the following query:

SELECT
    [X_Forwarded_For],
    COUNT(1) AS RequestCount
FROM
    LogEntry
WHERE
    [date] = '2020-03-10' 
GROUP BY
    [X_Forwarded_For]
ORDER BY
    COUNT(1) DESC