Реализация системы анализа IP посетителей

На сайт заходят с разных IP. Некоторые из посетителей являются злоумышленниками (хакеры, боты и т.д., исследующие сайт на  предмет уязвимостей). 

В as_trace фиксируются некоторые события, которые косвенно указывают, что это было недружественное посещение (коды blockip, blockipTrap и bottrylogin).

Идея состоит в том, чтобы отдельно собирать информацию по таким проблемным IP для последующей выгрузки этих данных в брандмауэр (добавить через список блокированных IP). 

Данные хранятся в таблице as_ip: id PK, ip, type, hostname, city, region, country, created.

Раз в день загружаем через /syssp данные в таблицу: 

В дневной процедуре вызываем: 

  if(DATEPART(HOUR, GETDATE())=4) begin
    	-- обновляем базу проблемных IP
    	exec [dbo].[ip_refill]
        -- обогощаем данные через API
    	select top 1 'apirequest' type, 'ipinfo' code, 'ip' p1_name, ip p1_value 
        from as_ip where country is null 
        order by id desc
  end 

Процедура  ip_refill:

CREATE OR ALTER PROCEDURE [dbo].[ip_refill]	
AS
BEGIN
	-- пополнение базы IP блоков 
	declare @minutes int =  60* 24 
	declare @minDate datetime = dateadd(minute, -@minutes, getdate())


	insert into as_ip (ip, type, created)
	select ip, code, created from as_trace 
	where created > @minDate 
		and code in 
			('bottrylogin','blockIP','blockIPTrap')
	and ip not in (select ip from as_ip)
END

и обогощаем данные по API - находим по IP что за страна, город. 

Исходящий метод API ipinfo (необходимо поставить свой токен из личного кабинета на ipinfo.io): 

CREATE PROCEDURE [dbo].[api_ipinfo_request]
	@parameters ExtendedDictionaryParameter READONLY,  
	@username nvarchar(32)  
AS
BEGIN
	-- SELECT 1  Msg, Result, Url (адрес, куда будет идти запрос)
    declare @ip nvarchar(max) = (select value2 from @parameters where [key]='ip')
	select '' Msg, 1 Result, 'https://ipinfo.io/'+@ip+'?token='+ dbo.as_setting('ipinfoToken', '') Url 
END

Response сохраняет в базу данные по IP: 

CREATE PROCEDURE [dbo].[api_ipinfo_response]
	@response nvarchar(max),
	@parameters ExtendedDictionaryParameter READONLY,  	
	@username nvarchar(32)
AS
BEGIN
	-- SELECT 1
	select '' Msg, 1 Result, @response Response
	 
    update as_ip
    set city = JSON_VALUE(@response, '$.city') ,
		region = JSON_VALUE(@response, '$.region'),
		country = JSON_VALUE(@response, '$.country')
    where ip = JSON_VALUE(@response, '$.ip') 	
END

В итоге у нас в таблице as_ip скапливаются данные по проблемным IP, которые затем можно использовать либо на брандмауэре, либо через GetLayout процедуру ограничивать доступ подобным IP. 

Как блокировать посещения с IP из черного списка через GetLayout:

declare @ip nvarchar(128) = (select value2 from @parameters where [key]='userIP')
if(exists (select ip from as_ip where ip=@ip))begin 
	select '/offline.html' RedirectUrl
    return
end