Extracting Wordfence Attacker Data
Wordfence is a web application firewall (WAF) designed for WordPress – (the most common website platform on the internet today) – which I have used and trusted for a long time.
Wordfence helps identify attacks and vulnerabilities on your WordPress sites and takes appropriate action to mitigate what it finds. It comes in two forms – a paid and unpaid version, where the paid version gives more rapid updates to its core list of security vulnerabilities and known bad actors, as well as direct support if your attacker does manage to breach your site.
I highly recommend it – it can be a little tricky to configure but is well worth the effort.
When it finds something hitting your site, the most common thing it does is to block the IP address of the attacker, carte blanche.
As I operate a series of WordPress sites – (in both my professional and personal spheres) – it would be nice to be able to extract the list of all of the IP addresses Wordfence has blocked, to ingest that list into other security systems you might have so they can be reused to implement security policy on other internet facing assets.
I do exactly this to block these identified bad IP addresses for accessing any service on any server I am responsible for.
On the surface, the data Wordfence stores in your WordPress database isn’t easily identifiable as an IP address, so it needs to be translated, for which I use the following SQL:
SELECT `raw_data`.`ipaddress` AS `ipaddress`,`raw_data`.`count` AS `count` FROM (SELECT REPLACE(CONVERT(INET6_NTOA(`wordpress_database_name`.`wp_wfBlockedIPLog`.`IP`) USING utf8mb4),'::ffff:','') AS `ipaddress`,SUM(`wordpress_database_name`.`wp_wfBlockedIPLog`.`blockCount`) AS `count` FROM `wordpress_database_name`.`wp_wfBlockedIPLog` GROUP BY `ipaddress`) `raw_data` ORDER BY `raw_data`.`ipaddress`;When using the above, change ‘wordpress_database_name’ to the actual name of the database your WordPress installation is using.
Also, I’ve noticed that sometimes the Wordfence table ‘wp_wfBlockedIPLog’ can have different capitalisation, and can appear to be ‘wp_wfblockediplog’ – just look for the table, and change the SQL above to suit the name as it stands in your database.
This query spits out a list of blocked IP addresses, and a count of how many times it has been blocked. I usually create a database view using this query so that it is queriable in the same way as a table, making it much easier to work with.
Once you have that, you can use the data to spread the knowledge of bad IP addresses across your infrastructure, and use the intelligence Wordfence provides to help secure that infrastructure.