Suricata IDS/IPS integration with Mikrotik (now with OSSEC)

RouterOS General
1

Update 7-December-2017 For those who don’t want to fuss with MySQL, I’ve added fast2mikrotik.php that will read the suricata events from fast.log and create the firewall rules.

Update 26-November-2017 Look at my post Historical IP address analysis for Intrusion Prevention on how event history can be used to identify clusters of frequent offenders and create firewall rules to prevent those frequent offenders from accessing your network.

Updated 10-March-2017

  • The list of signatures that I use, there are now 51 signatures.
  • suricata_block.php has been updated to rebuild the list of blocked addresses after the Mikrotik is rebooted (e.g. firmware upgrade). When updating the firmware on the router, stop the suricata_block process, perform the update, then start the suricata_block process. The list of blocked addresses will be repopulated

Updated 9-April-2017

  • Added in OSSEC IPS functionality (after suricata section)

Updated 27-April-2017

  • Added email notification function in suricata_block.php

Updated 6-June-2017

  • Included snort/snorby database schema

Updated 19-June-2017

  • fixed bug in mikrotik-fw.sh for ossec active response

I’ve seen a few posts on this forum asking for integration of an IDS/IPS into the Mikrotik platform. While this would be convenient, I find the arguments made against doing such pretty compelling. As Robert Penz pointed out in his blog (http://robert.penz.name/849/howto-setup-a-mikrotik-routeros-with-suricata-as-ids/) it is quite easy to set up a Mikrotik router to stream the packets into an IDS platform (suricata/snort). The part that is missing in this scenario is the IPS portion of the solution. In order to implement an IPS, the alerts that the IDS signals need to modify the firewall rules in order to block the offending traffic. In this article, I’ll layout what I did in order to introduce an IPS solution into the mix.

My IDS installation for my rather extensive home network is pretty straight-forward. It is a run of the mill Suricata implementation, using the Emerging Threat rule set, which, when fired, are taken from the Unified2 format into a MySQL database using barnyard2. I use Aanval as my console to monitor alerts. My Suricata platform is a Solid Run CuBox I4 (quad core arm, 4gb memory, Debian Jessie) and a 1TB hard drive (overkill, but it was spare parts) attached via eSata. I have this directly connected to one of the ports on my RB2011UiAS-IN.

Mikrotik Set-up

Here are the steps that you can take on the Mikrotik side of the IPS equation.

    1. Stream packets to the IDS platform (CuBox in my case):


/tool sniffer set filter-interface=ether1 filter-ip-address=!1.2.3.4/32 filter-stream=yes streaming-enabled=yes streaming-server=192.168.3.12

I filter out a specific remote address (in this example of 1.2.3.4) as this traffic is a site-to-site IP tunnel and really doesn’t need to be checked by the IDS.

    1. Set up firewall rules to block inbound and outbound traffic based on an address list:


/ip firewall add action=drop chain=input comment="Block bad actors" src-address-list=Blocked
/ip firewall add action=drop chain=forward comment="Drop any traffic going to bad actors" dst-address-list=Blocked

I have the input chain rule as the first rule after any default rules created by the Mikrotik. Similar with the forward chain rule.

    1. Enable the API interface:


/ip service set api address=192.168.3.0/24 enabled=yes

The API will be used to write the address list back to the Mikrotik. We make sure it is only accessible from the internal network.

IDS platform set-up, MySQL modifications

** If you don’t want to fuss with MySQL and barnyard2, skip to the section titled fast2mikrotik.php **

The alerts are moved from the Unified2 format into a MySQL database using barnyard2. There are plenty of examples on the web on how to do that. The schema that is being used for the database is the standard snort/snorby schema. The schema is included here for those who want to create it manually, rather than through the snort/snorby method.

-- phpMyAdmin SQL Dump

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `snorby`
--
CREATE DATABASE IF NOT EXISTS `snorby` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `snorby`;

-- --------------------------------------------------------

--
-- Table structure for table `agent_asset_names`
--

DROP TABLE IF EXISTS `agent_asset_names`;
CREATE TABLE `agent_asset_names` (
  `sensor_sid` int(10) UNSIGNED NOT NULL,
  `asset_name_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Stand-in structure for view `aggregated_events`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `aggregated_events`;
CREATE TABLE `aggregated_events` (
`ip_src` int(10) unsigned
,`ip_dst` int(10) unsigned
,`signature` int(10) unsigned
,`event_id` int(11)
,`number_of_events` bigint(21)
);

-- --------------------------------------------------------

--
-- Table structure for table `asset_names`
--

DROP TABLE IF EXISTS `asset_names`;
CREATE TABLE `asset_names` (
  `id` int(10) UNSIGNED NOT NULL,
  `ip_address` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `name` varchar(1024) NOT NULL,
  `global` tinyint(1) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `block_queue`
--

DROP TABLE IF EXISTS `block_queue`;
CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL,
  `que_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the block was added',
  `que_ip_adr` varchar(64) CHARACTER SET utf8 NOT NULL COMMENT 'The IP address to block',
  `que_timeout` varchar(12) CHARACTER SET utf8 NOT NULL COMMENT 'How long to block for',
  `que_sig_name` varchar(256) CHARACTER SET utf8 NOT NULL COMMENT 'The name of the signature that caused the block',
  `que_sig_gid` int(10) NOT NULL COMMENT 'The signature group ID',
  `que_sig_sid` int(10) NOT NULL COMMENT 'The signature ID',
  `que_event_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When the event was triggered',
  `que_processed` int(11) NOT NULL DEFAULT '0' COMMENT 'If this item has been processed (0=no, <>0=yes)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall';

-- --------------------------------------------------------

--
-- Table structure for table `caches`
--

DROP TABLE IF EXISTS `caches`;
CREATE TABLE `caches` (
  `id` int(10) UNSIGNED NOT NULL,
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `ran_at` datetime DEFAULT NULL,
  `event_count` int(11) DEFAULT '0',
  `tcp_count` int(11) DEFAULT '0',
  `udp_count` int(11) DEFAULT '0',
  `icmp_count` int(11) DEFAULT '0',
  `severity_metrics` mediumtext,
  `signature_metrics` mediumtext,
  `src_ips` mediumtext,
  `dst_ips` mediumtext,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `classifications`
--

DROP TABLE IF EXISTS `classifications`;
CREATE TABLE `classifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` text,
  `hotkey` int(11) DEFAULT NULL,
  `locked` tinyint(1) DEFAULT '0',
  `events_count` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `data`
--

DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
  `sid` int(10) UNSIGNED NOT NULL,
  `cid` int(10) UNSIGNED NOT NULL,
  `data_payload` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `delayed_jobs`
--

DROP TABLE IF EXISTS `delayed_jobs`;
CREATE TABLE `delayed_jobs` (
  `id` int(10) UNSIGNED NOT NULL,
  `priority` int(11) DEFAULT '0',
  `attempts` int(11) DEFAULT '0',
  `handler` text,
  `run_at` datetime DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `locked_by` text,
  `failed_at` datetime DEFAULT NULL,
  `last_error` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `detail`
--

DROP TABLE IF EXISTS `detail`;
CREATE TABLE `detail` (
  `detail_type` int(10) UNSIGNED NOT NULL,
  `detail_text` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `encoding`
--

DROP TABLE IF EXISTS `encoding`;
CREATE TABLE `encoding` (
  `encoding_type` int(10) UNSIGNED NOT NULL,
  `encoding_text` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `event`
--

DROP TABLE IF EXISTS `event`;
CREATE TABLE `event` (
  `sid` int(10) UNSIGNED NOT NULL,
  `cid` int(10) UNSIGNED NOT NULL,
  `signature` int(10) UNSIGNED DEFAULT NULL,
  `classification_id` int(10) UNSIGNED DEFAULT NULL,
  `users_count` int(10) UNSIGNED DEFAULT '0',
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `notes_count` int(10) UNSIGNED DEFAULT '0',
  `type` int(10) UNSIGNED DEFAULT '1',
  `number_of_events` int(10) UNSIGNED DEFAULT '0',
  `timestamp` datetime DEFAULT NULL,
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Stand-in structure for view `events_with_join`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `events_with_join`;
CREATE TABLE `events_with_join` (
`sid` int(10) unsigned
,`cid` int(10) unsigned
,`signature` int(10) unsigned
,`classification_id` int(10) unsigned
,`users_count` int(10) unsigned
,`user_id` int(10) unsigned
,`notes_count` int(10) unsigned
,`type` int(10) unsigned
,`number_of_events` int(10) unsigned
,`timestamp` datetime
,`id` int(11)
,`ip_src` int(10) unsigned
,`ip_dst` int(10) unsigned
,`sig_priority` int(10) unsigned
,`sig_name` text
);

-- --------------------------------------------------------

--
-- Table structure for table `favorites`
--

DROP TABLE IF EXISTS `favorites`;
CREATE TABLE `favorites` (
  `id` int(10) UNSIGNED NOT NULL,
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `icmphdr`
--

DROP TABLE IF EXISTS `icmphdr`;
CREATE TABLE `icmphdr` (
  `sid` int(10) UNSIGNED NOT NULL,
  `cid` int(10) UNSIGNED NOT NULL,
  `icmp_type` int(10) UNSIGNED DEFAULT NULL,
  `icmp_code` int(10) UNSIGNED DEFAULT NULL,
  `icmp_csum` int(10) UNSIGNED DEFAULT NULL,
  `icmp_id` int(10) UNSIGNED DEFAULT NULL,
  `icmp_seq` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `iphdr`
--

DROP TABLE IF EXISTS `iphdr`;
CREATE TABLE `iphdr` (
  `sid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `ip_src` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_dst` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_ver` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_hlen` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_tos` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_len` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_flags` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_off` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_ttl` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_proto` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_csum` int(10) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `lookups`
--

DROP TABLE IF EXISTS `lookups`;
CREATE TABLE `lookups` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(50) DEFAULT NULL,
  `value` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `notes`
--

DROP TABLE IF EXISTS `notes`;
CREATE TABLE `notes` (
  `id` int(10) UNSIGNED NOT NULL,
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `body` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `description` text,
  `sig_id` int(11) DEFAULT NULL,
  `ip_src` varchar(50) DEFAULT NULL,
  `ip_dst` varchar(50) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_ids` mediumtext,
  `sensor_ids` mediumtext,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `opt`
--

DROP TABLE IF EXISTS `opt`;
CREATE TABLE `opt` (
  `sid` int(10) UNSIGNED NOT NULL,
  `cid` int(10) UNSIGNED NOT NULL,
  `optid` int(10) UNSIGNED NOT NULL,
  `opt_proto` int(10) UNSIGNED DEFAULT NULL,
  `opt_code` int(10) UNSIGNED DEFAULT NULL,
  `opt_len` int(10) UNSIGNED DEFAULT NULL,
  `opt_data` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `reference`
--

DROP TABLE IF EXISTS `reference`;
CREATE TABLE `reference` (
  `ref_id` int(10) UNSIGNED NOT NULL,
  `ref_system_id` int(10) UNSIGNED DEFAULT NULL,
  `ref_tag` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `reference_system`
--

DROP TABLE IF EXISTS `reference_system`;
CREATE TABLE `reference_system` (
  `ref_system_id` int(10) UNSIGNED NOT NULL,
  `ref_system_name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `schema`
--

DROP TABLE IF EXISTS `schema`;
CREATE TABLE `schema` (
  `id` int(10) UNSIGNED NOT NULL,
  `vseq` int(10) UNSIGNED DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `version` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `search`
--

DROP TABLE IF EXISTS `search`;
CREATE TABLE `search` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `rule_count` int(11) DEFAULT '0',
  `public` tinyint(1) DEFAULT '0',
  `title` varchar(50) DEFAULT NULL,
  `search` mediumtext,
  `checksum` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `sensor`
--

DROP TABLE IF EXISTS `sensor`;
CREATE TABLE `sensor` (
  `sid` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) DEFAULT 'Click To Change Me',
  `hostname` text,
  `interface` text,
  `filter` text,
  `detail` int(10) UNSIGNED DEFAULT NULL,
  `encoding` int(10) UNSIGNED DEFAULT NULL,
  `last_cid` int(10) UNSIGNED DEFAULT NULL,
  `pending_delete` tinyint(1) DEFAULT '0',
  `updated_at` datetime DEFAULT NULL,
  `events_count` int(10) UNSIGNED DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `name` varchar(50) NOT NULL DEFAULT '',
  `value` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `severities`
--

DROP TABLE IF EXISTS `severities`;
CREATE TABLE `severities` (
  `id` int(10) UNSIGNED NOT NULL,
  `sig_id` int(10) UNSIGNED DEFAULT NULL,
  `events_count` int(10) UNSIGNED DEFAULT '0',
  `name` varchar(50) DEFAULT NULL,
  `text_color` varchar(50) DEFAULT '#ffffff',
  `bg_color` varchar(50) DEFAULT '#dddddd'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `signature`
--

DROP TABLE IF EXISTS `signature`;
CREATE TABLE `signature` (
  `sig_id` int(10) UNSIGNED NOT NULL,
  `sig_class_id` int(10) UNSIGNED DEFAULT NULL,
  `sig_name` text,
  `sig_priority` int(10) UNSIGNED DEFAULT NULL,
  `sig_rev` int(10) UNSIGNED DEFAULT NULL,
  `sig_sid` int(10) UNSIGNED DEFAULT NULL,
  `sig_gid` int(10) UNSIGNED DEFAULT NULL,
  `events_count` int(10) UNSIGNED DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `sig_class`
--

DROP TABLE IF EXISTS `sig_class`;
CREATE TABLE `sig_class` (
  `sig_class_id` int(10) UNSIGNED NOT NULL,
  `sig_class_name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `sig_reference`
--

DROP TABLE IF EXISTS `sig_reference`;
CREATE TABLE `sig_reference` (
  `sig_id` int(10) UNSIGNED NOT NULL,
  `ref_seq` int(10) UNSIGNED NOT NULL,
  `ref_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `tcphdr`
--

DROP TABLE IF EXISTS `tcphdr`;
CREATE TABLE `tcphdr` (
  `sid` int(10) UNSIGNED NOT NULL,
  `cid` int(10) UNSIGNED NOT NULL,
  `tcp_sport` int(10) UNSIGNED DEFAULT NULL,
  `tcp_dport` int(10) UNSIGNED DEFAULT NULL,
  `tcp_seq` int(10) UNSIGNED DEFAULT NULL,
  `tcp_ack` int(10) UNSIGNED DEFAULT NULL,
  `tcp_off` int(10) UNSIGNED DEFAULT NULL,
  `tcp_res` int(10) UNSIGNED DEFAULT NULL,
  `tcp_flags` int(10) UNSIGNED DEFAULT NULL,
  `tcp_win` int(10) UNSIGNED DEFAULT NULL,
  `tcp_csum` int(10) UNSIGNED DEFAULT NULL,
  `tcp_urp` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `udphdr`
--

DROP TABLE IF EXISTS `udphdr`;
CREATE TABLE `udphdr` (
  `sid` int(10) UNSIGNED NOT NULL,
  `cid` int(10) UNSIGNED NOT NULL,
  `udp_sport` int(10) UNSIGNED DEFAULT NULL,
  `udp_dport` int(10) UNSIGNED DEFAULT NULL,
  `udp_len` int(10) UNSIGNED DEFAULT NULL,
  `udp_csum` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `email` varchar(255) NOT NULL DEFAULT '',
  `encrypted_password` varchar(128) NOT NULL DEFAULT '',
  `remember_token` varchar(255) DEFAULT NULL,
  `remember_created_at` datetime DEFAULT NULL,
  `reset_password_token` varchar(255) DEFAULT NULL,
  `sign_in_count` int(11) DEFAULT '0',
  `current_sign_in_at` datetime DEFAULT NULL,
  `last_sign_in_at` datetime DEFAULT NULL,
  `current_sign_in_ip` varchar(255) DEFAULT NULL,
  `last_sign_in_ip` varchar(255) DEFAULT NULL,
  `favorites_count` int(11) DEFAULT '0',
  `accept_notes` int(11) DEFAULT '1',
  `notes_count` int(11) DEFAULT '0',
  `id` int(10) UNSIGNED NOT NULL,
  `per_page_count` int(11) DEFAULT '45',
  `name` varchar(50) DEFAULT NULL,
  `timezone` varchar(50) DEFAULT 'UTC',
  `admin` tinyint(1) DEFAULT '0',
  `enabled` tinyint(1) DEFAULT '1',
  `gravatar` tinyint(1) DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `online` tinyint(1) DEFAULT '0',
  `last_daily_report_at` datetime DEFAULT '2016-07-26 03:37:41',
  `last_weekly_report_at` int(11) DEFAULT '201630',
  `last_monthly_report_at` int(11) DEFAULT '201607',
  `last_email_report_at` datetime DEFAULT NULL,
  `email_reports` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure for view `aggregated_events`
--
DROP TABLE IF EXISTS `aggregated_events`;

CREATE ALGORITHM=UNDEFINED DEFINER=`snort`@`localhost` SQL SECURITY DEFINER VIEW `aggregated_events`  AS  select `iphdr`.`ip_src` AS `ip_src`,`iphdr`.`ip_dst` AS `ip_dst`,`event`.`signature` AS `signature`,max(`event`.`id`) AS `event_id`,count(0) AS `number_of_events` from (`event` join `iphdr` on(((`event`.`sid` = `iphdr`.`sid`) and (`event`.`cid` = `iphdr`.`cid`)))) where isnull(`event`.`classification_id`) group by `iphdr`.`ip_src`,`iphdr`.`ip_dst`,`event`.`signature` ;

-- --------------------------------------------------------

--
-- Structure for view `events_with_join`
--
DROP TABLE IF EXISTS `events_with_join`;

CREATE ALGORITHM=UNDEFINED DEFINER=`snort`@`localhost` SQL SECURITY DEFINER VIEW `events_with_join`  AS  select `event`.`sid` AS `sid`,`event`.`cid` AS `cid`,`event`.`signature` AS `signature`,`event`.`classification_id` AS `classification_id`,`event`.`users_count` AS `users_count`,`event`.`user_id` AS `user_id`,`event`.`notes_count` AS `notes_count`,`event`.`type` AS `type`,`event`.`number_of_events` AS `number_of_events`,`event`.`timestamp` AS `timestamp`,`event`.`id` AS `id`,`iphdr`.`ip_src` AS `ip_src`,`iphdr`.`ip_dst` AS `ip_dst`,`signature`.`sig_priority` AS `sig_priority`,`signature`.`sig_name` AS `sig_name` from ((`event` join `iphdr` on(((`event`.`sid` = `iphdr`.`sid`) and (`event`.`cid` = `iphdr`.`cid`)))) join `signature` on((`event`.`signature` = `signature`.`sig_id`))) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `agent_asset_names`
--
ALTER TABLE `agent_asset_names`
  ADD PRIMARY KEY (`sensor_sid`,`asset_name_id`),
  ADD KEY `index_agent_asset_names_sensor` (`sensor_sid`),
  ADD KEY `index_agent_asset_names_asset_name` (`asset_name_id`);

--
-- Indexes for table `asset_names`
--
ALTER TABLE `asset_names`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_asset_names_ip_address` (`ip_address`);

--
-- Indexes for table `block_queue`
--
ALTER TABLE `block_queue`
  ADD PRIMARY KEY (`que_id`),
  ADD KEY `que_added` (`que_added`);

--
-- Indexes for table `caches`
--
ALTER TABLE `caches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_caches_ran_at` (`ran_at`);

--
-- Indexes for table `classifications`
--
ALTER TABLE `classifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_classifications_id` (`id`),
  ADD KEY `index_classifications_hotkey` (`hotkey`),
  ADD KEY `index_classifications_locked` (`locked`),
  ADD KEY `index_classifications_events_count` (`events_count`);

--
-- Indexes for table `data`
--
ALTER TABLE `data`
  ADD PRIMARY KEY (`sid`,`cid`),
  ADD KEY `index_data_sid` (`sid`),
  ADD KEY `index_data_cid` (`cid`);

--
-- Indexes for table `delayed_jobs`
--
ALTER TABLE `delayed_jobs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_delayed_jobs_run_at_priority` (`priority`,`run_at`),
  ADD KEY `index_delayed_jobs_locked_at` (`locked_at`);

--
-- Indexes for table `detail`
--
ALTER TABLE `detail`
  ADD PRIMARY KEY (`detail_type`),
  ADD KEY `index_detail_detail_type` (`detail_type`);

--
-- Indexes for table `encoding`
--
ALTER TABLE `encoding`
  ADD PRIMARY KEY (`encoding_type`),
  ADD KEY `index_encoding_encoding_type` (`encoding_type`);

--
-- Indexes for table `event`
--
ALTER TABLE `event`
  ADD PRIMARY KEY (`sid`,`cid`),
  ADD KEY `index_event_sid` (`sid`),
  ADD KEY `index_event_cid` (`cid`),
  ADD KEY `index_event_signature` (`signature`),
  ADD KEY `index_event_classification_id` (`classification_id`),
  ADD KEY `index_event_users_count` (`users_count`),
  ADD KEY `index_event_user_id` (`user_id`),
  ADD KEY `index_event_notes_count` (`notes_count`),
  ADD KEY `index_timestamp_cid_sid` (`timestamp`,`cid`,`sid`),
  ADD KEY `index_event_id` (`id`);

--
-- Indexes for table `favorites`
--
ALTER TABLE `favorites`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_favorites_id` (`id`),
  ADD KEY `index_favorites_sid` (`sid`),
  ADD KEY `index_favorites_cid` (`cid`),
  ADD KEY `index_favorites_user_id` (`user_id`);

--
-- Indexes for table `icmphdr`
--
ALTER TABLE `icmphdr`
  ADD PRIMARY KEY (`sid`,`cid`),
  ADD KEY `index_icmphdr_sid` (`sid`),
  ADD KEY `index_icmphdr_cid` (`cid`);

--
-- Indexes for table `iphdr`
--
ALTER TABLE `iphdr`
  ADD PRIMARY KEY (`sid`,`cid`),
  ADD KEY `index_iphdr_sid` (`sid`),
  ADD KEY `index_iphdr_cid` (`cid`),
  ADD KEY `index_iphdr_ip_src` (`ip_src`),
  ADD KEY `index_iphdr_ip_dst` (`ip_dst`);

--
-- Indexes for table `lookups`
--
ALTER TABLE `lookups`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `notes`
--
ALTER TABLE `notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_notes_sid` (`sid`),
  ADD KEY `index_notes_cid` (`cid`),
  ADD KEY `index_notes_user_id` (`user_id`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `opt`
--
ALTER TABLE `opt`
  ADD PRIMARY KEY (`sid`,`cid`,`optid`),
  ADD KEY `index_opt_sid` (`sid`),
  ADD KEY `index_opt_cid` (`cid`),
  ADD KEY `index_opt_optid` (`optid`);

--
-- Indexes for table `reference`
--
ALTER TABLE `reference`
  ADD PRIMARY KEY (`ref_id`),
  ADD KEY `index_reference_ref_id` (`ref_id`);

--
-- Indexes for table `reference_system`
--
ALTER TABLE `reference_system`
  ADD PRIMARY KEY (`ref_system_id`),
  ADD KEY `index_reference_system_ref_system_id` (`ref_system_id`);

--
-- Indexes for table `schema`
--
ALTER TABLE `schema`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_schema_id` (`id`);

--
-- Indexes for table `search`
--
ALTER TABLE `search`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_search_user_id` (`user_id`),
  ADD KEY `index_search_rule_count` (`rule_count`),
  ADD KEY `index_search_public` (`public`);

--
-- Indexes for table `sensor`
--
ALTER TABLE `sensor`
  ADD PRIMARY KEY (`sid`),
  ADD KEY `index_sensor_sid` (`sid`),
  ADD KEY `index_sensor_detail` (`detail`),
  ADD KEY `index_sensor_encoding` (`encoding`),
  ADD KEY `index_sensor_last_cid` (`last_cid`),
  ADD KEY `index_sensor_events_count` (`events_count`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`name`),
  ADD KEY `index_settings_name` (`name`);

--
-- Indexes for table `severities`
--
ALTER TABLE `severities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_severities_id` (`id`),
  ADD KEY `index_severities_sig_id` (`sig_id`),
  ADD KEY `index_severities_events_count` (`events_count`),
  ADD KEY `index_severities_text_color` (`text_color`),
  ADD KEY `index_severities_bg_color` (`bg_color`);

--
-- Indexes for table `signature`
--
ALTER TABLE `signature`
  ADD PRIMARY KEY (`sig_id`),
  ADD KEY `index_signature_sig_id` (`sig_id`),
  ADD KEY `index_signature_sig_class_id` (`sig_class_id`),
  ADD KEY `index_signature_sig_priority` (`sig_priority`),
  ADD KEY `index_signature_events_count` (`events_count`);

--
-- Indexes for table `sig_class`
--
ALTER TABLE `sig_class`
  ADD PRIMARY KEY (`sig_class_id`),
  ADD KEY `index_sig_class_sig_class_id` (`sig_class_id`);

--
-- Indexes for table `sig_reference`
--
ALTER TABLE `sig_reference`
  ADD PRIMARY KEY (`sig_id`,`ref_seq`),
  ADD KEY `index_sig_reference_sig_id` (`sig_id`),
  ADD KEY `index_sig_reference_ref_seq` (`ref_seq`);

--
-- Indexes for table `tcphdr`
--
ALTER TABLE `tcphdr`
  ADD PRIMARY KEY (`sid`,`cid`),
  ADD KEY `index_tcphdr_sid` (`sid`),
  ADD KEY `index_tcphdr_cid` (`cid`),
  ADD KEY `index_tcphdr_tcp_sport` (`tcp_sport`),
  ADD KEY `index_tcphdr_tcp_dport` (`tcp_dport`);

--
-- Indexes for table `udphdr`
--
ALTER TABLE `udphdr`
  ADD PRIMARY KEY (`sid`,`cid`),
  ADD KEY `index_udphdr_sid` (`sid`),
  ADD KEY `index_udphdr_cid` (`cid`),
  ADD KEY `index_udphdr_udp_sport` (`udp_sport`),
  ADD KEY `index_udphdr_udp_dport` (`udp_dport`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_users_favorites_count` (`favorites_count`),
  ADD KEY `index_users_notes_count` (`notes_count`),
  ADD KEY `index_users_id` (`id`),
  ADD KEY `index_users_per_page_count` (`per_page_count`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `asset_names`
--
ALTER TABLE `asset_names`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `block_queue`
--
ALTER TABLE `block_queue`
  MODIFY `que_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=160;
--
-- AUTO_INCREMENT for table `caches`
--
ALTER TABLE `caches`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1593;
--
-- AUTO_INCREMENT for table `classifications`
--
ALTER TABLE `classifications`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `delayed_jobs`
--
ALTER TABLE `delayed_jobs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4434;
--
-- AUTO_INCREMENT for table `detail`
--
ALTER TABLE `detail`
  MODIFY `detail_type` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `encoding`
--
ALTER TABLE `encoding`
  MODIFY `encoding_type` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `event`
--
ALTER TABLE `event`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=79077;
--
-- AUTO_INCREMENT for table `favorites`
--
ALTER TABLE `favorites`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `lookups`
--
ALTER TABLE `lookups`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `notes`
--
ALTER TABLE `notes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `reference`
--
ALTER TABLE `reference`
  MODIFY `ref_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35231;
--
-- AUTO_INCREMENT for table `reference_system`
--
ALTER TABLE `reference_system`
  MODIFY `ref_system_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `schema`
--
ALTER TABLE `schema`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `search`
--
ALTER TABLE `search`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `sensor`
--
ALTER TABLE `sensor`
  MODIFY `sid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `severities`
--
ALTER TABLE `severities`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `signature`
--
ALTER TABLE `signature`
  MODIFY `sig_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=516;
--
-- AUTO_INCREMENT for table `sig_class`
--
ALTER TABLE `sig_class`
  MODIFY `sig_class_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=39;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;COMMIT;

New Tables

In my situation, I don’t want all alerts to trigger an IPS. I’ve watched my traffic over time and the vast majority of events come from a small set of alert signatures. So the first thing we’ll do is set up a table (sigs_to_block) in MySQL to indicate which alert signature categories we want to trigger an IPS.

--
-- Table structure for table `sigs_to_block`
--
CREATE TABLE `sigs_to_block` (
   `sig_name` text COLLATE utf8_unicode_ci NOT NULL,
   `src_or_dst` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'src',
   `timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '01:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Indexes for table `sigs_to_block`
--
ALTER TABLE `sigs_to_block`
  ADD UNIQUE KEY `sig_name_unique_index` (`sig_name`(64));

The “sig_name” column contains the portion of the signature that will be used to match alerts we want to block. The “src_or_dst” column contains whether we are blocking a source IP address or a destination IP address with this alert. And finally, the “timeout” column indicates how long we’ll block this IP address for after an event.

Here are the alert signatures that I have populated the “sigs_to_block” table with:

INSERT INTO `sigs_to_block` (`sig_name`, `src_or_dst`, `timeout`) VALUES
('ET COMPROMISED Known Compromised or Hostile Host Traffic', 'src', '01:00:00'),
('ET POLICY Suspicious inbound to', 'src', '01:00:00'),
('ET DROP Dshield Block Listed Source', 'src', '01:00:00'),
('ET SCAN', 'src', '01:00:00'),
('ET DROP Spamhaus DROP Listed Traffic Inbound', 'src', '01:00:00'),
('ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted', 'dst', '23:59:59'),
('ET CINS Active Threat Intelligence Poor Reputation IP', 'src', '01:00:00'),
('GPL SNMP', 'src', '01:00:00'),
('ET TOR Known Tor', 'src', '01:00:00'),
('GPL DNS named version attempt', 'src', '01:00:00'),
('ET VOIP Modified Sipvicious Asterisk PBX User-Agent', 'src', '01:00:00'),
('GPL RPC xdmcp info query', 'src', '01:00:00'),
('GPL RPC portmap listing', 'src', '01:00:00'),
('SURICATA SMTP no server welcome message', 'dst', '23:59:59'),
('GPL SMTP', 'dst', '23:59:59'),
('ET WEB_CLIENT SUSPICIOUS Possible automated connectivity check', 'dst', '01:00:00'),
('Snort Alert [1:2403326:2951]', 'src', '01:00:00'),
('Snort Alert [1:2500082:4086]', 'src', '01:00:00'),
('GPL POLICY PCAnywhere', 'src', '01:00:00'),
('ET POLICY External IP Lookup', 'dst', '23:59:59'),
('GPL WEB_SERVER', 'src', '23:59:59'),
('ET SHELLCODE', 'src', '23:59:59'),
('ET P2P Edonkey IP Query End', 'src', '01:00:00'),
('ET MALWARE Suspicious', 'dst', '01:00:00'),
('ET CURRENT_EVENTS', 'src', '01:00:00'),
('Unencrypted Request Method', 'src', '01:00:00'),
('SURICATA SMTP data command rejected', 'dst', '01:00:00'),
('ET WEB_SERVER', 'src', '01:00:00'),
('ET DOS', 'src', '01:00:00'),
(' ET POLICY User', 'dst', '01:00:00'),
('ET TROJAN Possible Win32/', 'dst', '01:00:00'),
('.tk domain', 'dst', '23:59:59'),
('ET POLICY archive.org', 'dst', '01:00:00'),
('ET CNC', 'dst', '23:59:59'),
('ET SNMP', 'src', '01:00:00'),
('ET POLICY Python', 'dst', '01:00:00'),
('ET POLICY curl User-Agent Outbound', 'dst', '01:00:00'),
('ET POLICY Windows 98 User-Agent Detected', 'dst', '01:00:00'),
('ET POLICY Internal Host Retrieving External IP', 'dst', '01:00:00'),
('ET POLICY Unsupported/Fake FireFox Version', 'dst', '01:00:00'),
('ET MOBILE_MALWARE', 'dst', '23:59:59'),
('ET POLICY Possible IP Check', 'src', '01:00:00'),
('ET P2P', 'dst', '01:00:00'),
('GPL SHELLCODE', 'src', '23:59:59'),
('GPL P2P', 'src', '01:00:00'),
('.pw domain', 'dst', '23:59:59'),
('Request to .su TLD', 'dst', '01:00:00'),
('Abnormal User-Agent', 'dst', '01:00:00'),
('SSLv3 outbound', 'src', '01:00:00'),
('Hex Obfuscation', 'src', '01:00:00'),
('SSH banner detected on TCP 443 likely proxy evasion', 'src', '01:00:00');

The next table that we’ll add is a queue table, which collects the relevant alerts (events) that will be used to create the IPS triggers sent to the Mikrotik.

--
-- Table structure for table `block_queue`
--
CREATE TABLE `block_queue` (
   `que_id` int(11) NOT NULL,
   `que_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the block was added',
   `que_ip_adr` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The IP address to block',
   `que_timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL COMMENT 'How long to block for',
   `que_sig_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the signature that caused the block',
   `que_sig_gid` int(10) NOT NULL COMMENT 'The signature group ID',
   `que_sig_sid` int(10) NOT NULL COMMENT 'The signature ID',
   `que_event_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When the event was triggered',
   `que_processed` int(11) NOT NULL DEFAULT '0' COMMENT 'If this item has been processed (0=no, <>0=yes)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall';

--
-- Indexes for table `block_queue`
--
ALTER TABLE `block_queue`
  ADD PRIMARY KEY (`que_id`),
  ADD KEY `que_added` (`que_added`);
--
-- AUTO_INCREMENT for table `block_queue`
--
ALTER TABLE `block_queue`
  MODIFY `que_id` int(11) NOT NULL AUTO_INCREMENT;

Database Trigger

The last portion that ties this all together and makes it work, is a MySQL trigger that populates the block_queue table when a relevant alert occurs. This is an after insert trigger on the iphdr table.

CREATE TRIGGER `after_iphdr_insert` AFTER INSERT ON `iphdr`
  FOR EACH ROW BEGIN
  DECLARE this_event INT(11) default 0;
  DECLARE this_event_signature INT(10) default 0;
  DECLARE this_event_timestamp TIMESTAMP;
  DECLARE this_sig INT(10) default 0;
  DECLARE this_sig_name VARCHAR(256) default "";
  DECLARE this_sig_gid INT(10) default 0;
  DECLARE timeout VARCHAR(12) default "";
  DECLARE interested INT default 0;
  DECLARE direction VARCHAR(3) default "";
  DECLARE ip_src VARCHAR(64) default "";
  DECLARE ip_dst VARCHAR(64) default "";
  SELECT event.id, event.signature, event.timestamp
  INTO this_event, this_event_signature, this_event_timestamp
  FROM event
  WHERE event.sid = NEW.sid and event.cid = NEW.cid;  
  SELECT signature.sig_sid, signature.sig_gid, signature.sig_name 
  INTO this_sig, this_sig_gid, this_sig_name
  FROM signature
  WHERE signature.sig_id = this_event_signature;
  SELECT count(*), sigs_to_block.src_or_dst, sigs_to_block.timeout
  INTO interested, direction, timeout
  FROM sigs_to_block
  WHERE this_sig_name LIKE CONCAT(sigs_to_block.sig_name, '%');
  IF (interested > 0) THEN
   IF (direction = "src") THEN
      INSERT INTO block_queue
	SET que_ip_adr = inet_ntoa(NEW.ip_src),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    ELSE
      INSERT INTO block_queue
	SET que_ip_adr = inet_ntoa(NEW.ip_dst),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    END IF;
  END IF;
END

suricata_block.php

The last component is a PHP script. It has 2 functions:

  • If the router was booted less than 5 minutes ago, rebuild the list of blocked addresses that are still active
  • Watch for new records in the “block_queue” table and add new entries to the blocked address list. When a new record is added, it uses the Mikrotik PHP API to create a new address in the “Blocked” address list.

Be sure to modify the configuration variables at the beginning of the script. Notice that you can have this script send you emails when an address is blocked. The emails will look like this:
email.png

<?php
require('routeros_api.class.php');

/* Set your specific configuration below */
$user_name = "db_user";
$password = "db_password";
$database = "snorby";
$server = "localhost";
$mikrotik_addr = "x.x.x.x";
$mikrotik_user = "admin";
$mikrotik_pwd = "admin_password";
$local_ip_prefix = "192.168.";
/* Set email_alert to true if you'd like to get email messages when a block is sent to the Mikrotik */
$email_alert = false;
$email_to = "myself@abc.com";
$email_from = "myids@abc.com";

header('Content-Type: text/plain');

$API = new RouterosAPI();

function UptimeInSeconds($uptime) {
  $mark1=strpos($uptime, "d");
  $days=substr($uptime, 0, $mark1);
  if ($mark1) $uptime=substr($uptime, $mark1 + 1);
  $mark1=strpos($uptime, "h");
  $hours=substr($uptime, 0, $mark1);
  if ($mark1) $uptime=substr($uptime, $mark1 + 1);
  $mark1=strpos($uptime, "m");
  $minutes=substr($uptime, 0, $mark1);
  if ($mark1) $uptime=substr($uptime, $mark1 + 1);
  $mark1=strpos($uptime, "s");
  $seconds=substr($uptime, 0, $mark1);
  if ($mark1) $uptime=substr($uptime, $mark1 + 1);
  $total=($days * 86400) + ($hours * 3600) + ($minutes * 60) + $seconds;
  return $total;
}

function AddToFirewall($thisrow) {

  global $local_ip_prefix, $API, $mikrotik_addr, $mikrotik_user, $mikrotik_pwd, $email_to, $email_from, $email_alert;

  if (strpos($thisrow['que_ip_adr'], $local_ip_prefix) !== true) {
    /* Does not match local address... */
    /* See if the address is already in the firewall list, if so delete it so we can readd it with a new timeout */
    try {
        $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
    } catch (Exception $e) {
        die('Unable to connect to RouterOS. Error:' . $e);
    }
    $ARRAY = $API->comm("/ip/firewall/address-list/print", array(
       ".proplist"=> ".id",
       "?address" => $thisrow['que_ip_adr'],));
    foreach ($ARRAY as $a) {
      foreach ($a as $name => $value) {
        $API->write("/ip/firewall/address-list/remove",false);
        $API->write("=.id=$value",true);
        $API->read();
      }
    }
    if (array_key_exists('que_remaining', $thisrow))
      { $timeremaining = $thisrow['que_remaining']; }
    else
      { $timeremaining = $thisrow['que_timeout']; }
    $API->comm("/ip/firewall/address-list/add", array(
      "list" => "Blocked",
      "address" => $thisrow['que_ip_adr'],
      "timeout" => $timeremaining,
      "comment" => "From suricata, " . $thisrow['que_sig_name'] . " => " . $thisrow['que_sig_gid'] . ":" . $thisrow['que_sig_sid'] .
         " => event timestamp: " . $thisrow['que_event_timestamp'],));
    $API->disconnect();
    if ($email_alert) {
      $to      = $email_to;
      $subject = 'Suricata on ' . gethostname() . ': blocked IP address ' . $thisrow['que_ip_adr'];
      $message = 'The IP address ' . $thisrow['que_ip_adr'] . " has been blocked due to the following rule match:\r\n";
      $message = $message . "\r\n";
      $message = $message . "The signature ID is " . $thisrow['que_sig_gid'] . " named: " . $thisrow['que_sig_name'] . "\r\n";
      $message = $message . "    event timestamp: " . $thisrow['que_event_timestamp'] . " blocked for: " . $timeremaining . "\r\n\r\n";
      $headers = 'From: ' . $email_from . "\r\n" .
        'Reply-To: ' . $email_from . "\r\n" .
        'X-Mailer: PHP/' . phpversion();
      mail($to, $subject, $message, $headers);
    }
  } else {
    /* Send email indicating bad block attempt*/
    $to      = $email_to;
    $subject = 'Suricata on ' . gethostname() . ': attempted block on local address';
    $message = 'A record in the block_queue indicated a block on a local IP Address (' . $row['que_ip_adr'] . ")\r\n";
    $message = $message . "\r\n";
    $message = $message . "The signature ID is " . $row['que_sig_id'] . " named: " . $row['que_sig_name'] . "\r\n";
    $message = $message . "    with a que_id of " . $row['que_id'] . "\r\n\r\n";
    $message = $message . "Check the src_or_dst field in events_to_block for the signature to make sure it is correct (src/dst).\r\n\r\n";
    $message = $message . "The record was not processed but marked as completed.\r\n";
    $headers = 'From: ' . $email_from . "\r\n" .
      'Reply-To: ' . $email_from . "\r\n" .
      'X-Mailer: PHP/' . phpversion();
    mail($to, $subject, $message, $headers);
  }
  return true;
}

/* $API->debug = true; */

/* Connect to database, if unsuccessful keep trying for 100 seconds */
    $i = 0;
    while ( $i < 100 ) {
      $db = new mysqli($server, $user_name, $password, $database);
      if ($db->connect_errno > 0) {
        print('Unable to connect to database [' . $db->connect_error . ']');
        sleep(10);
        $i = $i + 10;
      }
      else {
        $i = 100;
      }
    }

/* Check to see how long the mikrotik has been up.  If less than 5 minutes then rebuild firewall list */
    try {
        $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
    } catch (Exception $e) {
        die('Unable to connect to RouterOS. Error:' . $e);
    }
    $ARRAY = $API->comm("/system/resource/print", false);
    $upsecs = UptimeInSeconds($ARRAY[0]['uptime']);
    $API->disconnect();
    if ($upsecs < 300) {
      /* Do not send alerts when rebuilding the firewall list */
      $save_alert = $email_alert;
      $email_alert = false;
      /* This SELECT statement will ignore any entries that had 2 minutes or less remaining */
      $SQL = "SELECT *,DATE_FORMAT(TIMEDIFF(ADDTIME(que_added,que_timeout), CURRENT_TIMESTAMP), '%H:%i:%s') as que_remaining " .
          "FROM block_queue where ADDTIME(que_added, que_timeout) > TIMESTAMPADD(MINUTE, 2, CURRENT_TIMESTAMP) order by que_remaining;";
      if (!$result = $db->query($SQL)) {
          die('There was an error running the query [' . $db->error . ']');
      }
      while ($row = $result->fetch_assoc()) {
        $x = AddToFirewall($row);
      }
      mysqli_free_result($result);
      $email_alert = $save_alert;
    }

/* Main program loop */
    while ( 1 == 1 ) {
      $SQL = "SELECT * FROM block_queue WHERE que_processed = 0;";
      if(!$result = $db->query($SQL)) {
        die('There was an error running the query [' . $db->error . ']');
      }
      while($row = $result->fetch_assoc()) {
        $x = AddToFirewall($row);
        $SQL2 = "UPDATE block_queue set que_processed = 1 WHERE que_id = " . $row['que_id'] . ";";
        if (!$result2 = $db->query($SQL2)) {
          die('There was an error running the query [' . $db->error . ']');
        }
        mysqli_free_result($result2);
      }
      mysqli_free_result($result);
      sleep(5); /* Sleep 5 seconds then do again */
      mysqli_ping($db);
    }
  $db->close();
?>

You can run this code with the command php –f /usr/local/bin/suricata_block.php. I’ve created an /etc/init.d script for it so that it can be started upon system boot and managed with the service command.

The following php script is useful to have run by a crontab entry every 10 minutes. It turns on packet sniffing on the Mikrotik, which is helpful if the Mikrotik gets rebooted as the default state for the packet sniffer tool is stopped.

<?php
require('routeros_api.class.php');

header('Content-Type: text/plain');

$API = new RouterosAPI();

/* $API->debug = true; */

    try {
        $API->connect('192.168.3.1', 'username', 'password');
    } catch (Exception $e) {
        die('Unable to connect to RouterOS. Error:' . $e);
    }
    $ARRAY = $API->comm("/tool/sniffer/start");
    $API->disconnect();
?>

The Result

Here is a screen shot from Winbox that shows the address list with the addresses generated as a result of the alerts from Suricata.
Block List.jpg
fast2mikrotik.php

If you want to go to a fast and simple route to create firewall rules, I’ve created the following PHP script, fast2mikrotik.php that will use the fast.log created by suricata. This PHP script uses the inotify library so you’ll have to add this to your PHP implementation. To do this:

$ apt-get install php-pear php-dev
$ pecl install inotify

Then modify php.ini to include the configuration command “extension=inotify.so”. In my case, php.ini was located in /etc/php/7.0/cli.

Be sure to modify the configuration variables at the beginning of the fast2mikrotik.php script. Notice that you can have this script send you emails when an address is blocked.

This script will block all events flagged by suricata and for the length of time specified in the $block_time variable.

You can run this code with the command php –f /usr/local/bin/fast2mikrotik.php. I’ve created an /etc/init.d script for it so that it can be started upon system boot and managed with the service command.

<?php
require('routeros_api.class.php');

/* Set your specific configuration below */
$fastlog = "/var/log/suricata/fast.log";
$mikrotik_addr = "__someip__";
$mikrotik_user = "admin";
$mikrotik_pwd = "__somesecret__";
$local_ip_prefix = "192.168.";
$block_time = "01:00:00";
/* Set email_alert to true if you'd like to get email messages when a block is sent to the Mikrotik */
$email_alert = false;
$email_to = "__someemail__yourself@xyz.com";
$email_from = "__someemail__root@xyz.com";

header('Content-Type: text/plain');

$API = new RouterosAPI();

/**
* Tail a file (UNIX only!)
* Watch a file for changes using inotify and return the changed data
*
* @param string $file - filename of the file to be watched
* @param integer $pos - actual position in the file
* @return string
*/
function tail($file,&$pos) {
    $buf='';
    // get the size of the file
    if(!$pos) $pos = filesize($file);
    // Open an inotify instance
    $fd = inotify_init();
    // Watch $file for changes.
    $watch_descriptor = inotify_add_watch($fd, $file, IN_ALL_EVENTS);
    // Loop forever (breaks are below)
    while (true) {
        // Read events (inotify_read is blocking!)
        $events = inotify_read($fd);
        // Loop though the events which occured
        foreach ($events as $event=>$evdetails) {
            // React on the event type
            switch (true) {
                // File was modified
                case ($evdetails['mask'] & IN_MODIFY):
                    // Stop watching $file for changes
                    inotify_rm_watch($fd, $watch_descriptor);
                    // Close the inotify instance
                    fclose($fd);
                    // open the file
                    $fp = fopen($file,'r');
                    if (!$fp) return false;
                    // seek to the last EOF position
                    fseek($fp,$pos);
                    // read until EOF
                    while (!feof($fp)) {
                        $buf .= fread($fp,8192);
                    }
                    // save the new EOF to $pos
                    $pos = ftell($fp); // (remember: $pos is called by reference)
                    // close the file pointer
                    fclose($fp);
                    // return the new data and leave the function
                    return $buf;
                    // be a nice guy and program good code ;-)
                    break;

                    // File was moved or deleted
                case ($evdetails['mask'] & IN_MOVE):
                case ($evdetails['mask'] & IN_MOVE_SELF):
                case ($evdetails['mask'] & IN_DELETE):
                case ($evdetails['mask'] & IN_DELETE_SELF):
                    // Stop watching $file for changes
                    inotify_rm_watch($fd, $watch_descriptor);
                    // Close the inotify instance
                    fclose($fd);
                    // Return a failure
                    return false;
                    break;
            }
        }
    }
}

function AddToFirewall($thisalert, $srcdst) {

  global $local_ip_prefix, $API, $mikrotik_addr, $mikrotik_user, $mikrotik_pwd, $block_time, $email_to, $email_from, $email_alert;

  /* Determine the target external address */
  if ((strpos($srcdst[0], $local_ip_prefix) === false) and
      (strpos($srcdst[0], "127.0.0.1") === false)) {
     $target = $srcdst[0];
  } else {
     $target = $srcdst[1];
  }   
  try {
      $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
  } catch (Exception $e) {
      die('Unable to connect to RouterOS. Error:' . $e);
  }
  $ARRAY = $API->comm("/ip/firewall/address-list/print", array(
     ".proplist"=> ".id",
     "?address" => $target,));
  foreach ($ARRAY as $a) {
    foreach ($a as $name => $value) {
      $API->write("/ip/firewall/address-list/remove",false);
      $API->write("=.id=$value",true);
      $API->read();
    }
  }
  $API->comm("/ip/firewall/address-list/add", array(
    "list" => "Blocked",
    "address" => $target,
    "timeout" => $block_time,
    "comment" => "From suricata, " . $thisalert[1] .
       " => event timestamp: " . $thisalert[0],));
  $API->disconnect();
  if ($email_alert) {
    $to      = $email_to;
    $subject = 'Suricata on ' . gethostname() . ': blocked IP address ' . $target;
    $message = 'The IP address ' . $target . " has been blocked due to the following rule match:\r\n";
    $message = $message . "\r\n";
    $message = $message . "The signature ID is " . $thisalert[1] . "\r\n";
    $message = $message . "    event timestamp: " . $thisalert[0] . " blocked for: " . $block_time . "\r\n\r\n";
    $headers = 'From: ' . $email_from . "\r\n" .
      'Reply-To: ' . $email_from . "\r\n" .
      'X-Mailer: PHP/' . phpversion();
    mail($to, $subject, $message, $headers);
  }
  return true;
}

$lastpos = 0;
while (true) {
  $alertstr = tail($fastlog,$lastpos);
  foreach (preg_split("/((\r?\n)|(\r\n?))/", $alertstr) as $line){
    if (strlen($line) > 0) {
      $thisalert = explode("[**]", $line);
      $thisalert[0] = trim($thisalert[0]);
      $thisalert[1] = trim($thisalert[1]);
      $thisalert[2] = trim($thisalert[2]);
      $tmpstr = explode("}", $thisalert[2]);
      $srcdst = explode("->", $tmpstr[1]);
      $tmpstr = explode(":", $srcdst[0]);
      $srcdst[0] = trim($tmpstr[0]);
      $tmpstr = explode(":", $srcdst[1]);
      $srcdst[1] = trim($tmpstr[0]);
      AddToFirewall($thisalert, $srcdst);
    }
  }   
}
?>

OSSEC IPS

Once you have Suricata sending messages to the Mikrotik with the components above, adding in OSSEC IPS is relatively easy. We will take advantage of the block_queue MySQL table and the suricata_block process. You’ll have to set up the host running suricata as the OSSEC Manager (Server).

In order to have OSSEC send IPS requests to the Mikrotik, we need to turn on active_responses in OSSEC. In the /var/ossec/etc/ossec.conf file (your path may vary slightly) add the following lines in the <ossec_config> section:

<command>
    <name>block-mikrotik</name>
    <executable>mikrotik-fw.sh</executable>
    <timeout_allowed>no</timeout_allowed>
    <expect />
</command>

<active-response>
    <command>block-mikrotik</command>
    <location>server</location>
    <!-- Set these rules to the rules in local_rules.xml that you want to have fire an active reponse -->
    <rules_id>100070,100071,100072,100073,100074</rules_id>
</active-response>

Make sure to check if there is another <active_repsonse> section in the ossec.conf file. If so, comment it out.

The directive defines a command block-mikrotik that points to a shell script that is located in /var/ossec/active-response/bin. Here is the mikrotik-fw.sh bash script:

#!/bin/bash
# mikrotik-fw.sh - get address from alert and write record into snorby.block_queue to drop packets from/to this address
# Author: Tom Fisk

ACTION=$1
USER=$2
IP=$3
ALERTID=$4
RULEID=$5

LOCAL=`dirname $0`;
cd $LOCAL
cd ../
PWD=`pwd`

# Logging the call
echo "`date` $0 $1 $2 $3 $4 $5 $6 $7 $8" >> ${PWD}/../logs/active-responses.log

# Getting alert time
ALERTTIME=`echo "$ALERTID" | cut -d  "." -f 1`

# Getting end of alert
ALERTLAST=`echo "$ALERTID" | cut -d  "." -f 2`

# Get the line from the log file and remove characters that need to be escaped in MySQL
LOGLINE=`sed -n "/$ALERTTIME/,/^$/{/^$/!p}" "${PWD}/../logs/alerts/alerts.log" | tail -n1`
LOGLINE=`echo ${LOGLINE//[-\"_]/} | cut -c1-232`

# Get the IP address from the last line
IP=`grep -oP '\b(?:(?:25[0-5]|2[0-4][0-9]|[0-1]?[0-9]{1,2})[.](?:25[0-5]|2[0-4][0-9]|[0-1]?[0-9]{1,2})[.](?:25[0-5]|2[0-4][0-9]|[0-1]?[0-9]{1,2})[.](?:25[0-
5]|2[0-4][0-9]|[0-1]?[0-9]{1,2}))\b' <<< "$LOGLINE" | head -1`

# if IP isn't blank & doesn't match 192.168.* <Replace 192.168.* below with your local address prefix>
if [[ ! -z "$IP" && ! $IP =~ ^192.168.* ]]; then
  tmpfile=$(mktemp)
  echo "insert into snorby.block_queue (que_ip_adr, que_timeout, que_sig_name, que_sig_gid, que_sig_sid, que_event_timestamp)" >> $tmpfile
  curdate=`date +'%F %T'`
  echo "  values ("'"'"$IP"'"'", "'"'"23:59:59"'"'", "'"'"OSSEC HIDS >> $LOGLINE"'"'" , 1, 1002, "'"'"$curdate"'"'");" >> $tmpfile
  /usr/local/mysql/bin/mysql -u<your mysql username> -p<your mysql password> snorby < $tmpfile
  rm $tmpfile
fi

Make sure this script is executable (chmod a+x).

Finally, you’ll want to override any rules that you want to fire an active response in /var/ossec/rules/local_rules.xml. Here are a set of rules that I defined for some specific events OSSEC fires on:

  <rule id="100070" level="12">
    <if_sid>1002</if_sid>
    <match>AH00135: Invalid method in request quit</match>
    <description>Block IP's trying to attack apache</description>
  </rule>

  <rule id="100071" level="12">
    <if_sid>1002</if_sid>
    <match>not found or unable to stat</match>
    <description>Block IP's trying to attack apache</description>
  </rule>

  <rule id="100072" level="12">
    <if_sid>1002</if_sid>
    <match>rejecting client initiated renegotiation</match>
    <description>Block IP's trying to attack apache</description>
  </rule>

  <rule id="100073" level="12">
    <if_sid>1002</if_sid>
    <match>request failed: malformed request line</match>
    <description>Block IP's trying to attack apache</description>
  </rule>

  <rule id="100074" level="12">
    <if_sid>3332</if_sid>
    <match>SASL LOGIN authentication failed</match>
    <description>Block IP's trying to log into SMTP</description>
  </rule>

That is all there is to do in order to get your OSSEC alerts firing into you Mikrotik.

a virus scanner on the router board
MT Router and Suricata as a IDS
No WAN packets with packet sniffer
Historical IP address analysis for Intrusion Prevention
Tor browser
2

Great post!

Thanks Tom

3

wonderful post.
Thanks.
Should be made a sticky

4

Hello

Thanks for your sharing. I’m try to implement this one but when i try to create trigger I have this issue:

Error in query (1064): Syntax error near '' at line 3




Error in query (1064): Syntax error near 'DECLARE this_event_signature INT(10) default 0' at line 1

I try to fix it, but i can’t.

Can you help me with this issue?

Regards.
M.

5

Hello


Well, after working a few days, I used your post like base, to make this project

https://github.com/elmaxid/ips-mikrotik-suricata

Tell me if you have some suggestions

Regards

M.

6

If you are using phpMyAdmin to define the trigger, you can’t do it in an SQL window. Go to the “Triggers” tab on the iphdr table and add the new trigger.
addtrigger.png
Otherwise just put the code in a file and execute it from the command line:

mysql -u username -p database_name < trigger_code.sql



7

Looks good. There are a couple of reasons I don’t clean out the block_queue table:

  1. I do some analytics to see who my repeat offenders are. The top repeat offenders get added into the address list on the mikrotik with no expiration time.
  2. If the mikrotik gets rebooted, then the blocked address list is lost. It would be possible to detect if the mikrotik was rebooted (check the system/resources uptime) and then replay the blocks that are still unexpired to rebuild the blocked address list. I haven’t done this, but would be relatively easy to do with some time arithmetic.


8

Hello.
Well, I update the manual on GIT. The new release support Telegram and Mail report.

I commented on it, you need Snorby installed to work ( the script) , because i use the schema DB.

You suggestions are great and welcome. Thank you.

M.

9

Thank you for your recommendation. i don’t know mysql, so you rescue me. I will update my script to add this trigger and remove a “daemon” which make this job.

10

Hi, I installed correctly this but I get bad address on Mikrotik, could anyone help me please?

Here a picture:




Hi, I am using your package but with your script MK can’t receive any rule. With original script from Tom I receive rules but badformed. Could you help me?

===Spanish===
He usado tu programa, aparentemente se ha instalado bien pero MK no recibe ninguna regla para banear. Con el script original de Tom si que añade reglas pero con un formato mal (la IP no es tal y es otro valor), ¿qué estoy haciendo mal para echar a andar tu script al completo?

Thanks/Gracias.

11

What version of MySql are you using? inet_ntoa was introduced in version 5.5.3.

12

Thanks you for reply.

I am using this:

mysql -V

mysql Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (x86_64) using readline 6.3

For other side, I used your script php but I added the tables using the package from maximan. Are you tables inserted different way to thim and for that reason Mysql is taking another values?

Thanks.

13

I haven’t examined his code in detail so I couldn’t say what changes he may have made.

14

Well, its seems same but maybe I should restore database to original (before to install that script) and add your tables.
If I paste your mysql’s code on a file .sql and export, is it right? or how should I add that content?

NOTE: Its same the tables. Any idea why MK get another value and not IP?

Thanks.

15

Yes, export code to file, then run it from command line into MySQL. See my previous response to max.

16

Thanks, I pasted all code (with DROP TABLE IF exists table_name

When I export sql I receive this on triggers:

mysql -usnorby -p snorby < table.sql

Enter password:
ERROR 1064 (42000) at line 101: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Line 101 is trigger's start. I dropped trigger:
mysql> show triggers;
Empty set (0.00 sec)

How could I add it again?

Here my code new table

--
-- Table structure for table `sigs_to_block`
--
DROP TABLE IF EXISTS sigs_to_block;
CREATE TABLE `sigs_to_block` (
   `sig_name` text COLLATE utf8_unicode_ci NOT NULL,
   `src_or_dst` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'src',
   `timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '01:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Indexes for table `sigs_to_block`
--
ALTER TABLE `sigs_to_block`
  ADD UNIQUE KEY `sig_name_unique_index` (`sig_name`(64));


INSERT INTO `sigs_to_block` (`sig_name`, `src_or_dst`, `timeout`) VALUES
('ET COMPROMISED Known Compromised or Hostile Host Traffic', 'src', '01:00:00'),
('ET POLICY Suspicious inbound to', 'src', '01:00:00'),
('ET DROP Dshield Block Listed Source', 'src', '01:00:00'),
('ET SCAN', 'src', '01:00:00'),
('ET DROP Spamhaus DROP Listed Traffic Inbound', 'src', '01:00:00'),
('ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted', 'dst', '23:59:59'),
('ET CINS Active Threat Intelligence Poor Reputation IP', 'src', '01:00:00'),
('GPL SNMP', 'src', '01:00:00'),
('ET TOR Known Tor', 'src', '01:00:00'),
('GPL DNS named version attempt', 'src', '01:00:00'),
('ET VOIP Modified Sipvicious Asterisk PBX User-Agent', 'src', '01:00:00'),
('GPL RPC xdmcp info query', 'src', '01:00:00'),
('GPL RPC portmap listing', 'src', '01:00:00'),
('SURICATA SMTP no server welcome message', 'dst', '23:59:59'),
('GPL SMTP', 'dst', '23:59:59'),
('ET WEB_CLIENT SUSPICIOUS Possible automated connectivity check', 'dst', '01:00:00'),
('Snort Alert [1:2403326:2951]', 'src', '01:00:00'),
('Snort Alert [1:2500082:4086]', 'src', '01:00:00'),
('GPL POLICY PCAnywhere', 'src', '01:00:00'),
('ET POLICY External IP Lookup', 'dst', '23:59:59'),
('GPL WEB_SERVER', 'src', '23:59:59'),
('ET SHELLCODE', 'src', '23:59:59'),
('ET P2P Edonkey IP Query End', 'src', '01:00:00'),
('ET MALWARE Suspicious', 'dst', '01:00:00'),
('ET CURRENT_EVENTS', 'src', '01:00:00'),
('Unencrypted Request Method', 'src', '01:00:00'),
('SURICATA SMTP data command rejected', 'dst', '01:00:00'),
('ET WEB_SERVER', 'src', '01:00:00'),
('ET DOS', 'src', '01:00:00'),
(' ET POLICY User', 'dst', '01:00:00'),
('ET TROJAN Possible Win32/', 'dst', '01:00:00'),
('.tk domain', 'dst', '23:59:59'),
('ET POLICY archive.org', 'dst', '01:00:00'),
('ET CNC', 'dst', '23:59:59'),
('ET SNMP', 'src', '01:00:00'),
('ET POLICY Python', 'dst', '01:00:00'),
('ET POLICY curl User-Agent Outbound', 'dst', '01:00:00'),
('ET POLICY Windows 98 User-Agent Detected', 'dst', '01:00:00'),
('ET POLICY Internal Host Retrieving External IP', 'dst', '01:00:00'),
('ET POLICY Unsupported/Fake FireFox Version', 'dst', '01:00:00'),
('ET MOBILE_MALWARE', 'dst', '23:59:59'),
('ET POLICY Possible IP Check', 'src', '01:00:00'),
('ET P2P', 'dst', '01:00:00'),
('GPL SHELLCODE', 'src', '23:59:59'),
('GPL P2P', 'src', '01:00:00'),
('.pw domain', 'dst', '23:59:59'),
('Request to .su TLD', 'dst', '01:00:00'),
('Abnormal User-Agent', 'dst', '01:00:00'),
('SSLv3 outbound', 'src', '01:00:00'),
('Hex Obfuscation', 'src', '01:00:00'),
('SSH banner detected on TCP 443 likely proxy evasion', 'src', '01:00:00');


--
-- Table structure for table `block_queue`
--
DROP TABLE IF EXISTS block_queue;
CREATE TABLE `block_queue` (
   `que_id` int(11) NOT NULL,
   `que_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the block was added',
   `que_ip_adr` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The IP address to block',
   `que_timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL COMMENT 'How long to block for',
   `que_sig_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the signature that caused the block',
   `que_sig_gid` int(10) NOT NULL COMMENT 'The signature group ID',
   `que_sig_sid` int(10) NOT NULL COMMENT 'The signature ID',
   `que_event_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When the event was triggered',
   `que_processed` int(11) NOT NULL DEFAULT '0' COMMENT 'If this item has been processed (0=no, <>0=yes)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall';

--
-- Indexes for table `block_queue`
--
ALTER TABLE `block_queue`
  ADD PRIMARY KEY (`que_id`),
  ADD KEY `que_added` (`que_added`);
--
-- AUTO_INCREMENT for table `block_queue`
--
ALTER TABLE `block_queue`
  MODIFY `que_id` int(11) NOT NULL AUTO_INCREMENT;


DROP TRIGGER IF EXISTS after_iphdr_insert;
CREATE TRIGGER `after_iphdr_insert` AFTER INSERT ON `iphdr` FOR EACH ROW
  BEGIN
  DECLARE this_event INT(11) default 0;
  DECLARE this_event_signature INT(10) default 0;
  DECLARE this_event_timestamp TIMESTAMP;
  DECLARE this_sig INT(10) default 0;
  DECLARE this_sig_name VARCHAR(256) default "";
  DECLARE this_sig_gid INT(10) default 0;
  DECLARE timeout VARCHAR(12) default "";
  DECLARE interested INT default 0;
  DECLARE direction VARCHAR(3) default "";
  DECLARE ip_src VARCHAR(64) default "";
  DECLARE ip_dst VARCHAR(64) default "";
  SELECT event.id, event.signature, event.timestamp
  INTO this_event, this_event_signature, this_event_timestamp
  FROM event
  WHERE event.sid = NEW.sid and event.cid = NEW.cid;  
  SELECT signature.sig_sid, signature.sig_gid, signature.sig_name 
  INTO this_sig, this_sig_gid, this_sig_name
  FROM signature
  WHERE signature.sig_id = this_event_signature;
  SELECT count(*), sigs_to_block.src_or_dst, sigs_to_block.timeout
  INTO interested, direction, timeout
  FROM sigs_to_block
  WHERE this_sig_name LIKE CONCAT(sigs_to_block.sig_name, '%');
  IF (interested > 0) THEN
   IF (direction = "src") THEN
      INSERT INTO block_queue
   SET que_ip_adr = inet_ntoa(NEW.ip_src),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    ELSE
      INSERT INTO block_queue
   SET que_ip_adr = inet_ntoa(NEW.ip_dst),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    END IF;
  END IF;
END

Thanks

17

Hmmm. A couple of ideas. Try to put just the trigger code in the SQL file and run it again. If that doesn’t work so you have phpmyadmin or a similar tool that will let you define the trigger?

18

A add trigger deleting old trigger, I think that its works ,but I discovered a new thing:

If I do from myqsl console set inet_ntoa(NEW.ip_src) → I get real IP

But MK is adding the number without decipher.

Any idea?

Thanks

19

Nice !!!

Thanks

20

So if you do this in MySQL:

use snorby;
Select * from block_queue;

What do you see in the que_ip_adr column? Should be real IP address. If it is still encoded IP address then trigger is not executing correctly as trigger should insert record with real IP address.

If you don’t see real IP address then do this in MySQL:

show triggers;

And check to make sure that trigger code is correct. If not, you might still have the old trigger executing.