Extra Systems Ban Software (ESBANS)

ES-RDP

Database rdp-ban

Creating a database and tables

Below is an SQL script for creating the database structure required for the ES-RDP system. We recommend using MySQL version 5.7 or higher. To deploy the system, run the following SQL query in your server console or via phpMyAdmin.

The rdp-ban database of our original Windows Remote Desktop Protection system has the following structure:

-- Creating a database
CREATE DATABASE IF NOT EXISTS rdp_ban;
USE rdp_ban;

-- 1. Primary Ban Log (Main Table for Analysis)
CREATE TABLE ban_log (
  ban_addr int unsigned,
  ban_subnet int unsigned,
  ban_time timestamp DEFAULT CURRENT_TIMESTAMP,
  KEY (ban_time, ban_addr),
  KEY (ban_time, ban_subnet, ban_addr)
);

-- 2. Log of blocked subnets
CREATE TABLE net_log (
  net_addr int unsigned,
  ban_time timestamp DEFAULT CURRENT_TIMESTAMP,
  KEY (ban_time, net_addr)
);

-- 3. Intermediate statistics (filled by the rdp-log.ps1 script)
CREATE TABLE ban_stats (
  ban_count int NOT NULL,
  ban_date timestamp DEFAULT CURRENT_TIMESTAMP,
  ban_type int,
  KEY (ban_date)
);

-- 4. Aggregated data by day (filled by the rdp-log.php script)
CREATE TABLE days_data (
  ban_date date,
  ban_count int,
  ban_type int,
  KEY (ban_date)
);

Purpose of tables

ban_log: "raw" data about each blocked IP; contains the address and time; based on this data, PowerShell calculates "repeat offenders" and hostile subnets;
net_log: this includes subnets (/24 format) that have been observed in mass attacks; it is used to generate "hell" level bans;
ban_stats: a technical table where the current number of rules in the firewall for each ban type is recorded once per hour;
days_data: the final table with average statistics by day; this is the one typically used to create graphs in the admin panel;

The content of this page is also available in Russian.