My technique for coping with spam comments on

Monty Python – Spam

‘Wibble’ began in 2007; a lifetime ago in Internet terms (and of course also for those now aged 15 and under, duh). In all those years, I only rarely had any spam comments to deal with here on Wibble, and I handled them in the way that defenders of UCE often advocated in the early days, which is to say: I ‘just hit delete’.

Of course, the flaw with ‘just hit delete’ is that it becomes a full-time job when a trickle turns into a flood. And that’s just what I’ve been seeing in recent months; I’m getting swamped with the blessed things. Maybe that’s due to the Covid pandemic, or perhaps <spit> Brexit. Or it could even be because Wibble is attracting more attention than it used to (chance would be a fine thing). Whatever the reason for this upsurge, it soon became clear that I needed a better solution than ‘just hit delete’.

This is what I came up with, and I share it in the hope that you may find it of use.

Two strikes and yer out!

I’m not exactly fond of maintenance. Washing dishes, dusting, vacuuming; that sort of thing. I’m not much of a gardener, either. But I do recognise that a small regular time investment helps prevent molehills becoming mountains.

I like to consider myself a fair-minded person. And I think it’s always a good idea to guard against the possibility of making mistakes. So, I figure that if someone leaves a comment that looks like spam just once, well, I can shrug that off (and maybe they’ll give up after the first attempt anyway). But if they do it a second time, I reckon it’s worth putting in that little extra effort to bolt the door, as it’ll save me time in the future.

  1. I regularly check the ‘Spam’ sections in both the ‘Comments’ and ‘Feedback > Form Responses’ areas in wp-admin.
  2. If a message is a genuine one, I set it as ‘Not Spam’.
  3. If it’s clearly spam, I copy-paste the commenter’s IP address into my ‘Wibble spammers’ spreadsheet (see below).
  4. If that IP address is already listed in my spreadsheet, that means I’ve had spam from that address before, so I add it to the ‘Disallowed Comment Keys’ section of the Discussion Settings (not forgetting to hit ‘Save Changes’). This will automatically route further messages from that address to ‘Trash’.
  5. Once done, I hit the ‘Empty Spam’ button.
  6. Finally, I check the ‘Trash’ section. A cursory glance here is all that’s needed to verify that there’s nothing there that shouldn’t be there.
  7. Then I hit the ‘Empty Trash’ button to clear the lot in one swell foop. Job done.
#2: mystery links (they only show on mouseover)
#3: location of the IP address

About the ‘Wibble spammers’ spreadsheet

I use the free open source LibreOffice (but I’m sure MS-Excel would work just as well).

This spreadsheet is really simple. It’s just two columns:

  • Column A: IP addresses, copy-pasted from the ‘Spam’ section (#3 above).
  • Column B: cell B2 contains a formula:
=IF(COUNTIF(A:A, A2)>1,"Spammer","")

That formula is copied down so there’s an entry in each ‘B’ cell adjacent to every non-blank ‘A’ cell. It compares the value in the adjacent cell in column A with all the other column A entries, and if it finds an identical value elsewhere it puts ‘Spammer’ in column B against the matching entries (otherwise, it leaves it blank).

The ‘Wibble spammers’ spreadsheet

Once a ‘Spammer’ is identified, the IP address can be added to the ‘Disallowed Comment Keys’ section of ‘Discussion Settings’ (I enter them in numerical order, but that’s because I have CDO‡; you don’t have to do that), and then simply deleted from the spreadsheet.

A couple of extra wrinkles makes life a little easier:

  • Setting an AutoFilter on column A allows the values to be sorted, which brings ‘Spammer’ values together.
  • Adding highlight to the cells in column B that contain the formula makes it easy to see them… so when (as inevitably happens) the list in column A outruns that, it’s a simple matter to grab that little square widget at the lower right corner of any one of those cells (you should be able to see that on cell B2 in the above image) and drag it down to copy the formula down as far as needed (the highlight is automagically copied too).

Benefits of this technique

  • It promotes early identification of genuine comments that have been incorrectly categorised as ‘Spam’. Hmm… that’s actually an important point. Maybe I should make that bold – there we go.
  • It doesn’t clog the ‘Disallowed Comment Keys’ list with:
    • duplicate entries (which is what would happen if I were to simply copy-paste IP addresses from the ‘Spam’ section, as there are often several spam comments from the same IP address in there)
    • unnecessary entries – there’s no point blocking ‘lightwieght’ spammers who only ever send me a single spam comment (there are currently 195 entries in my ‘Wibble spammers’ spreadsheet)

Well, there you have it; I hope it’s clear – if not, please do let me know, so that I can fix it. And I’d love to hear from you whether you think it’s useful.

Monty Python – Spam Song (Official Lyric Video)

‡ I have CDO†, which is like OCD but the letters are in alphabetical order, as they should be.
† Although looking around now at the untidiness of my room… perhaps I don’t.

About peNdantry

Phlyarologist (part-time) and pendant. Campaigner for action against anthropogenic global warming (AGW) and injustice in all its forms. Humanist, atheist, notoftenpist. Wannabe poet, writer and astronaut.
This entry was posted in balance, Computers and Internet, Phlyarology, Strategy, Tech tips and tagged , , , , , , , . Bookmark the permalink.

17 Responses to My technique for coping with spam comments on

  1. Anita Bowden says:

    This is helpful to know! Thanks so much!


  2. Good ideas! One of the best comedy sketches ever! I’ll be singing the Spam song for the rest of the day.

    Liked by 1 person

  3. Hetty Eliot says:

    I’m very impressed with this, but there’s simply no way in hell I’d ever put in that amount of work.

    Liked by 1 person

  4. I actually gets lots of spam comment and sometimes some genuine ones goes into spam, without me realising that good comments are into Spam.
    This is also happening with my comments, maybe because I comment too much and that’s my wp send my comments into the spam. Then the writer never got any idea that I even commented. 🤷🏻‍♂️
    Checking spam regularly is a good idea

    Liked by 1 person

    • peNdantry says:

      I too find that sometimes my own comments are designated ‘spam’ by Akismet (most commonly those that relate to 💥 ?Random Raiders! 💥). The clue seems to be: if the comment appears with an ‘in moderation’ label, that’s fine; but if it disappears without trace after hitting ‘Submit’ then it’s been categorised as spam. I queried it with Akismet, and they (understandably) wouldn’t provide any information about what specifically I might be doing wrongly‡, but they suggested that in such cases the best course of action would be to leave another comment simply alerting the site owner that I’d left a comment that might be in their ‘Spam’ folder, and that’s what I do these days.

      ‡ I suspect the problem is copy-paste of identical content (especially when that contains links), which I was doing a lot in the early days of ?Random Raiders!.

      Variety’s the very spice of life,
      That gives it all its flavour.

      William Cowper (17311800)

      Liked by 1 person

  5. And peNdantry is back with an awesome tip.
    I will have to adopt that method. My Spam popularity seems to have trends I am yet to fully understand. There was a time I would get hundreds of them every day. And I went through all of them to make sure I don’t delete a comment by a fellow blogger (it’s interesting how WP throws them into the Spam purgatory sometimes…).
    Thank you!

    Liked by 1 person

    • peNdantry says:

      You’re very welcome, Goldie! Please do try it out and let me know if you find this technique of benefit.

      Liked by 1 person

      • Definitely. I cleared my Spam out right now and added a couple of repeated offenders to the blocked box.
        The only thing is that I believe those real spammer change their IP quite often and so I wonder how effective this will be in the long run. But I shall wait and see. But, every little bit helps! How long have you been doing this for?

        Liked by 1 person

        • peNdantry says:

          You’re not wrong that this may only be a temporary solution; everything changes. The roll-out of IPv6 may have an impact. Such things as dynamic IP address, VPNs and the possibility of spoofing all complicate matters.

          I’ve been using the technique for at least a year, maybe two, originally checking for matching numbers by eye as I went along. One day‡ I thought, “to hell with this, I need a quicker method” and set up a spreadsheet to do the donkey work.

          Some numbers for you…
          # IP address entries at today’s date in:
          ‘wibble spammers’: 208
          Disallowed Comment Keys: 91

          I’m going to start logging the numbers of Spam and Trash I’m getting from now on, to see if there’s a trend there. It feels like those numbers are on the increase. ‘Trash’ definitely is, which proves to me at least that the technique works, as it allows quick identification and removal of proven garbage.

          ‡ File>Properties on ‘wibble spammers’ tells me the file was created on 03Jan2022, so that was probably the day :)

          Liked by 1 person

  6. Forestwood says:

    Great stuff! Spammers begone!

    Liked by 1 person

I'd love to hear your thoughts...

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.