Performance improvement tips - block unwanted search enginee bots

3 years ago
It is really important to block bots from some countries if you are not shipping there. For example, after blocking all traffic from China and Russia(Andrei never mind :) ) and Germany on an eCommerce application which only ship into USA, I noticed a significant performance improvement. You block/challenge countries using different website security service tools like incapsula or cloudflare etc. Also, you can block IP from web.config easily.


Fig 1: Look at AWS cloud watch. CPU usages, web traffic significantly reduced.


Fig 2: Server response time incredibly improved - Newrelic.


You can write a simple trigger to catch unwanted bots ip and block it.

Create table to track all search engine ips

CREATE TABLE [dbo].[x_SearchEngine](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [LastIpAddress] [nvarchar](50) NULL,
  [CreatedOnUtc] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create trigger to insert ips into previously created table
CREATE TRIGGER [dbo].[x_searchengine]
   ON [dbo].[Customer]
   AFTER UPDATE
AS
BEGIN

  SET NOCOUNT ON;

   IF EXISTS(SELECT 1 FROM inserted where Id = 42693958)
   BEGIN
   insert into x_SearchEngine
   (LastIPAddress, CreatedOnUtc)
   select LastIpAddress, GETUTCDATE() from inserted where id = 42693958
   END

END

Wait few hours and run following query and find bots who is killing your server
select lastipaddress, count(*)
from x_searchengine
group by lastipaddress
order by count(*) desc


Find country from ip using iplocation.net
and see if the country/ip is relevant for your business otherwise block it.


You can also visit my Quora post for more details
3 years ago
Hi Shahdat,

Thanks a lot! Very good contribution! Maybe, we (or somebody else from our community) can implement it as a plugin.

Here is a work item
3 years ago
RE: "... where Id = 42693958"

Is that a "typo"?
3 years ago
New York wrote:
RE: "... where Id = 42693958"

Is that a "typo"?


Yes, it should be where Id = 2

We can find search engine user id using following query
SELECT Id FROM Customer WHERE email = '[email protected]_engine_record.com'
3 years ago
Does this still apply when you are using a proxi like Cloud Flare?
3 years ago
Tecnofin wrote:
Does this still apply when you are using a proxi like Cloud Flare?


Yes, Cloud Flare does not filter anything unless you tell it do something. In Cloud Flare, you can challenge or apply captcha on specific countries, you can block ips.
3 years ago
shahdat45ict wrote:
Does this still apply when you are using a proxi like Cloud Flare?

Yes, Cloud Flare does not filter anything unless you tell it do something. In Cloud Flare, you can challenge or apply captcha on specific countries, you can block ips.

Thank you Shahdat