• 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

• Added in OSSEC IPS functionality (after suricata section)

• Added email notification function in suricata_block.php

• Included snort/snorby database schema

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

1. 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

1. 2. 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

1. 3. Enable the API interface:

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

-- 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;

--
-- 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));

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`
--
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;

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

• 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.

<?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();
?>

<?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();
?>

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

<?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);
    }
  }   
}
?>

<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>

#!/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

  <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>

mysql -u username -p database_name < trigger_code.sql

Hello

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

Code: Select all

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

Code: Select all

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.

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.

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.

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.

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.

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:

Code: Select all

mysql -u username -p database_name < trigger_code.sql

Hello

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

Code: Select all

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

Code: Select all

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.

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.

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

Here a picture:

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

Here a picture:

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

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

Here a picture:

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

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.

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

Here a picture:

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

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.

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

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

Here a picture:

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

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.

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

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?

Thanks.

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

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.

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

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?

Thanks.

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

--
-- 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 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.

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

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?

Thanks.

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

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

Code: Select all

--
-- 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

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

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?

Thanks.

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

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

Code: Select all

--
-- 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

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?

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

use snorby;
Select * from block_queue;

show triggers;

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

So if you do this in MySQL:

Code: Select all

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:

Code: Select all

show triggers;

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

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

So if you do this in MySQL:

Code: Select all

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:

Code: Select all

show triggers;

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

Hi, finally its working fine. I dropped old tables and add it again and its showing real IP on Mikrotik.

A last question, I see a lot of rules on emerging-*, how could I test one? I see some rules with an address.
Example:alert http $HOME_NET any -> $EXTERNAL_NET any (msg:"ET MALWARE Win32/Hadsruda!bit Adware/PUA Installation Activity"; flow:to_server,established; content:"GET"; http_method; content:"?alpha="; http_uri; content:"User-Agent|3a 20|NSIS_Inetc"; http_header; fast_pattern; pcre:"/\?alpha=(?:[A-Za-z0-9/+]{4})*(?:[A-Za-z0-9/+]{2}==|[A-Za-z0-9/+]{3}=|[A-Za-z0-9/+]{4})/U"; reference:md5,6b58b3eb9bbb0f7297a2e36e615506d3; classtype:trojan-activity; sid:2022850; rev:2;)

I want to access to check how MK ban that traffic. I tested with ransomware's IP and its ok, MK ban automatically, but I can't test it with a URL.

Thanks you for your job and support.

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

So if you do this in MySQL:

Code: Select all

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:

Code: Select all

show triggers;

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

Hi, finally its working fine. I dropped old tables and add it again and its showing real IP on Mikrotik.

A last question, I see a lot of rules on emerging-*, how could I test one? I see some rules with an address.
Example:alert http $HOME_NET any -> $EXTERNAL_NET any (msg:"ET MALWARE Win32/Hadsruda!bit Adware/PUA Installation Activity"; flow:to_server,established; content:"GET"; http_method; content:"?alpha="; http_uri; content:"User-Agent|3a 20|NSIS_Inetc"; http_header; fast_pattern; pcre:"/\?alpha=(?:[A-Za-z0-9/+]{4})*(?:[A-Za-z0-9/+]{2}==|[A-Za-z0-9/+]{3}=|[A-Za-z0-9/+]{4})/U"; reference:md5,6b58b3eb9bbb0f7297a2e36e615506d3; classtype:trojan-activity; sid:2022850; rev:2;)

I want to access to check how MK ban that traffic. I tested with ransomware's IP and its ok, MK ban automatically, but I can't test it with a URL.

Thanks you for your job and support.

I don't know that there is a way to test all of the signatures, at least not easily. If you go to section 1.5 of this document you can see how they test some of the signatures:

https://web.nsrc.org/workshops/2015/pac ... g-test.htm

I would suspect that there is a way to generate the signatures on the interface, but that is beyond my level at this time.

I will look to test it.

My Mikrotik has banned my own mail IP, are there way to add a whitelist in suricata or script or Mikrotik?
Could I create a rule in first place to allow traffic to my mail address?

Thanks again.

vars:
  # more specifc is better for alert accuracy and performance
  address-groups:
    HOME_NET: "[192.168.0.0/16,10.0.0.0/8]"
    #HOME_NET: "[192.168.0.0/16]"
    #HOME_NET: "[10.0.0.0/8]"
    #HOME_NET: "[172.16.0.0/12]"
    #HOME_NET: "any"

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

I will look to test it.

My Mikrotik has banned my own mail IP, are there way to add a whitelist in suricata or script or Mikrotik?
Could I create a rule in first place to allow traffic to my mail address?

Thanks again.

A couple of different ways to do this in suricata.

First of all in /etc/suricata/suricata.yaml right at the top of the file is a place to define you home network:

Code: Select all

vars:
  # more specifc is better for alert accuracy and performance
  address-groups:
    HOME_NET: "[192.168.0.0/16,10.0.0.0/8]"
    #HOME_NET: "[192.168.0.0/16]"
    #HOME_NET: "[10.0.0.0/8]"
    #HOME_NET: "[172.16.0.0/12]"
    #HOME_NET: "any"

If that doesn't seem to do the trick, then in /etc/suricata/threshold.config you can suppress the firing of specific signatures:

Code: Select all

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Just see what signature id caused your email server to be block and add it into the threshold.config file.

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: "[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]"

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [**] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [**] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 -> 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: "[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]"

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [**] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [**] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 -> 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Yes, your definitions look right.

Suricata will not prevent all bad things from happening. It will detect network based threats that match it's signature database. If you have an external facing website or any other service, it will see that traffic as valid and not prohibit access.

Suricata is just one layer of security that you can implement to protect your assets from network based threats. End-point protection, ransomware protection, and host intrusion detection (e.g. OSSEC) adds more layers of security in order to try to alleviate threats. But just like physical security, if someone wants to work hard enough to get into your house, there is really not a way to stop them. The more layers of security you have, the more likely it is that the attacker will move on and look for softer targets.

# ps wuaxf|grep suri
root      7789 45.3 24.0 1392900 755588 ?      Sl   07:17  54:48 /usr/bin/suricata -c /etc/suricata/suricata-debian.yaml -r -
root      7862  0.3  2.4 177752 77560 ?        Ss   07:18   0:28 barnyard2 -c /etc/suricata/barnyard2.conf -d /var/log/suricata -f unified2.alert -w /var/log/suricata/suricata.waldo -D
root     11335  0.0  0.0   8824   752 pts/0    S+   09:18   0:00      \_ grep --color=auto suri
root     11325  0.2  0.3 191204 10532 pts/0    S    09:18   0:00 php -f /opt/mikrotik-ips-suricata/suricata_block.php

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: "[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]"

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [**] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [**] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 -> 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Yes, your definitions look right.

Suricata will not prevent all bad things from happening. It will detect network based threats that match it's signature database. If you have an external facing website or any other service, it will see that traffic as valid and not prohibit access.

Suricata is just one layer of security that you can implement to protect your assets from network based threats. End-point protection, ransomware protection, and host intrusion detection (e.g. OSSEC) adds more layers of security in order to try to alleviate threats. But just like physical security, if someone wants to work hard enough to get into your house, there is really not a way to stop them. The more layers of security you have, the more likely it is that the attacker will move on and look for softer targets.

A lot of thanks for your fast reply. I am checking this script and I am finding with a trouble.

Script is running good:

Code: Select all

# ps wuaxf|grep suri
root      7789 45.3 24.0 1392900 755588 ?      Sl   07:17  54:48 /usr/bin/suricata -c /etc/suricata/suricata-debian.yaml -r -
root      7862  0.3  2.4 177752 77560 ?        Ss   07:18   0:28 barnyard2 -c /etc/suricata/barnyard2.conf -d /var/log/suricata -f unified2.alert -w /var/log/suricata/suricata.waldo -D
root     11335  0.0  0.0   8824   752 pts/0    S+   09:18   0:00      \_ grep --color=auto suri
root     11325  0.2  0.3 191204 10532 pts/0    S    09:18   0:00 php -f /opt/mikrotik-ips-suricata/suricata_block.php

And if I try to access a ransomware website (for example) automatically Mikrotik receive a new line from server with that IP, its okay and running fine.

Now, I leave from that shell, and I again enter a new ransomware's IP (which it should be banned too because php process is running) but not, when I access this new IP, script doesn't appear running and IP's aren't banned.

I enabled logs php:
error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT
log_errors = On
error_log = /var/log/php_errors.log
But log is empty.

Any idea please?

Thanks.

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: "[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]"

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [**] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [**] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 -> 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Yes, your definitions look right.

Suricata will not prevent all bad things from happening. It will detect network based threats that match it's signature database. If you have an external facing website or any other service, it will see that traffic as valid and not prohibit access.

Suricata is just one layer of security that you can implement to protect your assets from network based threats. End-point protection, ransomware protection, and host intrusion detection (e.g. OSSEC) adds more layers of security in order to try to alleviate threats. But just like physical security, if someone wants to work hard enough to get into your house, there is really not a way to stop them. The more layers of security you have, the more likely it is that the attacker will move on and look for softer targets.

A lot of thanks for your fast reply. I am checking this script and I am finding with a trouble.

Script is running good:

Code: Select all

# ps wuaxf|grep suri
root      7789 45.3 24.0 1392900 755588 ?      Sl   07:17  54:48 /usr/bin/suricata -c /etc/suricata/suricata-debian.yaml -r -
root      7862  0.3  2.4 177752 77560 ?        Ss   07:18   0:28 barnyard2 -c /etc/suricata/barnyard2.conf -d /var/log/suricata -f unified2.alert -w /var/log/suricata/suricata.waldo -D
root     11335  0.0  0.0   8824   752 pts/0    S+   09:18   0:00      \_ grep --color=auto suri
root     11325  0.2  0.3 191204 10532 pts/0    S    09:18   0:00 php -f /opt/mikrotik-ips-suricata/suricata_block.php

And if I try to access a ransomware website (for example) automatically Mikrotik receive a new line from server with that IP, its okay and running fine.

Now, I leave from that shell, and I again enter a new ransomware's IP (which it should be banned too because php process is running) but not, when I access this new IP, script doesn't appear running and IP's aren't banned.

I enabled logs php:
error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT
log_errors = On
error_log = /var/log/php_errors.log
But log is empty.

Any idea please?

Thanks.

Is there anything in fast.log? Perhaps suricata didn't flag any signatures.

04/10/2017-09:47:00.315253  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:51052 -> 91.226.92.140:80
04/10/2017-09:47:00.315303  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:51052 -> 91.226.92.140:80
04/10/2017-09:48:02.390455  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:52704 -> 109.195.175.176:80
04/10/2017-09:48:02.390541  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:52704 -> 109.195.175.176:80
04/10/2017-09:53:01.815003  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:33050 -> 46.172.212.32:80
04/10/2017-09:53:01.815198  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:33050 -> 46.172.212.32:80

Yes, when I run script again Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-run it again because script is stopped when I leave shell.

Here my tests in fast.log

Code: Select all

04/10/2017-09:47:00.315253  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:51052 -> 91.226.92.140:80
04/10/2017-09:47:00.315303  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:51052 -> 91.226.92.140:80
04/10/2017-09:48:02.390455  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:52704 -> 109.195.175.176:80
04/10/2017-09:48:02.390541  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:52704 -> 109.195.175.176:80
04/10/2017-09:53:01.815003  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:33050 -> 46.172.212.32:80
04/10/2017-09:53:01.815198  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:33050 -> 46.172.212.32:80

Ransomware Tracker Reported CnC Server

Yes, when I run script again Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-run it again because script is stopped when I leave shell.

Here my tests in fast.log

Code: Select all

04/10/2017-09:47:00.315253  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:51052 -> 91.226.92.140:80
04/10/2017-09:47:00.315303  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:51052 -> 91.226.92.140:80
04/10/2017-09:48:02.390455  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:52704 -> 109.195.175.176:80
04/10/2017-09:48:02.390541  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:52704 -> 109.195.175.176:80
04/10/2017-09:53:01.815003  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:33050 -> 46.172.212.32:80
04/10/2017-09:53:01.815198  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:33050 -> 46.172.212.32:80

Have you added this signature string

Code: Select all

Ransomware Tracker Reported CnC Server

in the sigs_to_block table? By default, only signatures in the sigs_to_block table are actually blocked. I use Anvil to check which signatures I'm seeing and if I see new signatures that I want to block, I add them to that table.

Yes, I edited my own message to be more clear:

Yes, suricata is getting rules because I can see it on Snorby.
When I start script again, Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-start it again because script stops when I leave shell and access a new bad IP. (note: While none IP has been detected by suricata, script continue running but if I access any ransomware's IP (or any other), script stop run and doesn't send notifications to Mikrotik).

Maybe could I debug php in any log to check because script stops?

Thanks.

Yes, I edited my own message to be more clear:

Yes, suricata is getting rules because I can see it on Snorby.
When I start script again, Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-start it again because script stops when I leave shell and access a new bad IP. (note: While none IP has been detected by suricata, script continue running but if I access any ransomware's IP (or any other), script stop run and doesn't send notifications to Mikrotik).

Maybe could I debug php in any log to check because script stops?

Thanks.

This is my php script, or script from max?

# cat /opt/mikrotik-ips-suricata/suricata_block.php 
<?php
require('routeros_api.class.php');

/* Set your specific configuration below */
$user_name = "snorby";
$password = "XXXXXX";
$database = "snorby";
$server = "localhost";
$mikrotik_addr = "192.168.X.X";
$mikrotik_user = "admin";
$mikrotik_pwd = "XXXXX";
$local_ip_prefix = "192.168.";
$email_to = "MYEMAIL";
$email_from = "MYEMAIL";

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) {
  echo $thisrow;
  global $local_ip_prefix, $API, $mikrotik_addr, $mikrotik_user, $mikrotik_pwd, $email_to, $email_from;

  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();
  } 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;
}

/* 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) {
      /* 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);
      }
      $rslt = mysqli_free_result($result);
    }

/* 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 . ']');
        }
        $rslt = mysqli_free_result($result2);
      }
      $rslt = mysqli_free_result($result);
      sleep(5); /* Sleep 5 seconds then do again */
      mysqli_ping($db);
    }
  $db->close();
?>

It is your script. Its weird because now script sent 2 rules to Mikrotik but in third test, script has stopped. Some is killing that script which I can't see what neither why.

Here output file php (which its always showing but doesn't matter because with that error if I don't leave shell, Mikrotik receive alerts) ->
Notice: Array to string conversion in /opt/mikrotik-ips-suricata/suricata_block.php on line 41
Array
Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /opt/mikrotik-ips-suricata/suricata_block.php on line 139

Here script:

Code: Select all

# cat /opt/mikrotik-ips-suricata/suricata_block.php 
<?php
require('routeros_api.class.php');

/* Set your specific configuration below */
$user_name = "snorby";
$password = "XXXXXX";
$database = "snorby";
$server = "localhost";
$mikrotik_addr = "192.168.X.X";
$mikrotik_user = "admin";
$mikrotik_pwd = "XXXXX";
$local_ip_prefix = "192.168.";
$email_to = "MYEMAIL";
$email_from = "MYEMAIL";

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) {
  echo $thisrow;
  global $local_ip_prefix, $API, $mikrotik_addr, $mikrotik_user, $mikrotik_pwd, $email_to, $email_from;

  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();
  } 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;
}

/* 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) {
      /* 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);
      }
      $rslt = mysqli_free_result($result);
    }

/* 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 . ']');
        }
        $rslt = mysqli_free_result($result2);
      }
      $rslt = mysqli_free_result($result);
      sleep(5); /* Sleep 5 seconds then do again */
      mysqli_ping($db);
    }
  $db->close();
?>

Sorry for so many post converting the post (a great job and an excellent tool) in my troubleshooting. Thanks.

mysql> show table create block_queue;

Hmmmm...can you post the definition of your block_queue table?

Code: Select all

mysql> show table create block_queue;

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `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)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Hmmmm...can you post the definition of your block_queue table?

Code: Select all

mysql> show table create block_queue;

Here:

Code: Select all

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `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)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

Code: Select all

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Note: Now for 1h I leave/enter shell and script is adding lines on Mikrotik. However, I dont know how stable it will be this... I will keep monitoring all day, if you know how could I debug it please let me know.
I enabled logs mysql too, with this output constantly:

Code: Select all

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Thanks.

Hmmmm...can you post the definition of your block_queue table?

Code: Select all

mysql> show table create block_queue;

Here:

Code: Select all

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `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)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

Code: Select all

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Note: Now for 1h I leave/enter shell and script is adding lines on Mikrotik. However, I dont know how stable it will be this... I will keep monitoring all day, if you know how could I debug it please let me know.
I enabled logs mysql too, with this output constantly:

Code: Select all

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Thanks.

OK, I'm thinking that perhaps there were bad records in the block_queue table from the initial trigger implementation using max's code. If those records expired then it shouldn't occur again. Keep me updated.

Thanks!

Hmmmm...can you post the definition of your block_queue table?

Code: Select all

mysql> show table create block_queue;

Here:

Code: Select all

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `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)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

Code: Select all

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Note: Now for 1h I leave/enter shell and script is adding lines on Mikrotik. However, I dont know how stable it will be this... I will keep monitoring all day, if you know how could I debug it please let me know.
I enabled logs mysql too, with this output constantly:

Code: Select all

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Thanks.

OK, I'm thinking that perhaps there were bad records in the block_queue table from the initial trigger implementation using max's code. If those records expired then it shouldn't occur again. Keep me updated.

Thanks!

Hi again!

In last 12h server had been running without problems. I dont know if it was for code, for triggers or something but its running correctly. I will be monitoring it today all day to check if script go down or not.
While... I will test your new update, OSSEC, it sounds really good. Could I install server on suricata's server and check agents, dont? I will receive all logs on suricata's server and I will check it on his own web GUI or Mikrotik if any IP have been banned.

Thanks for all your support Tom.

OSSEC installed and running fine (excelent & easy manual).
Two question, I installed too web UI for OSSEC and its running fine too, but I would like to have logs from anothers servers to check integrity, logs, etc.

Most important question. Should I install agent on servers or since everything is addressed to Mikrotik I do not need to have the agents sending logs to the server?
Could I install agents on servers to have logs on web UI?

Thanks as always.

OSSEC installed and running fine (excelent & easy manual).
Two question, I installed too web UI for OSSEC and its running fine too, but I would like to have logs from anothers servers to check integrity, logs, etc.

Most important question. Should I install agent on servers or since everything is addressed to Mikrotik I do not need to have the agents sending logs to the server?
Could I install agents on servers to have logs on web UI?

Thanks as always.

From what I read the Web UI is no longer being maintained. Just an FYI. I never installed it.

Yes, you can install agents on other hosts, send logs to server, and trigger active events from those hosts on server. I have multiple agents sending their logs to the server and I can trigger an active response to the Mikrotik from any agent activity.

 ActionMailer::Base.delivery_method = :smtp
 ActionMailer::Base.smtp_settings = {
   :address              => "mail.mydomain.com",
   :port                 => Port_Sent_Email,
   :domain               => "mydomain.com",
   :user_name            => "myusername",
   :password             => "mypassword",
   :authentication       => "plain",
   :enable_starttls_auto => true
 }

OSSEC installed and running fine (excelent & easy manual).
Two question, I installed too web UI for OSSEC and its running fine too, but I would like to have logs from anothers servers to check integrity, logs, etc.

Most important question. Should I install agent on servers or since everything is addressed to Mikrotik I do not need to have the agents sending logs to the server?
Could I install agents on servers to have logs on web UI?

Thanks as always.

From what I read the Web UI is no longer being maintained. Just an FYI. I never installed it.

Yes, you can install agents on other hosts, send logs to server, and trigger active events from those hosts on server. I have multiple agents sending their logs to the server and I can trigger an active response to the Mikrotik from any agent activity.

Ok, it sounds good, I will do it same to you.
And what do you think about ntop? any trouble in have in same server both services? I have Snorby on port 3000 but I could change ntop another port.

How could I enable notifications from Suricata/Snorby in email? I enabled Snorby emails in /opt/snorby/config/initializers/mail_config.rb

Code: Select all

 ActionMailer::Base.delivery_method = :smtp
 ActionMailer::Base.smtp_settings = {
   :address              => "mail.mydomain.com",
   :port                 => Port_Sent_Email,
   :domain               => "mydomain.com",
   :user_name            => "myusername",
   :password             => "mypassword",
   :authentication       => "plain",
   :enable_starttls_auto => true
 }

But I don't see any logs neither any sent to my email.

Thanks.

Hi, yesterday I had a false positive with this:
fast.log.2:04/25/2017-14:05:58.149205 [**] [1:2002994:7] ET SCAN Rapid IMAP Connections - Possible Brute Force Attack [**] [Classification: Misc activity] [Priority: 3] {TCP}

alert tcp $EXTERNAL_NET any -> $HOME_NET 143 (msg:"ET SCAN Rapid IMAP Connections - Possible Brute Force Attack"; flow:to_server; flags: S,12; threshold: type both, track by_src, count 30, seconds 60; reference:url,doc.emergingthreats.net/2002994; classtype:misc-activity; sid:2002994; rev:7;)

I'm seeing that rule which if it detects 30 count in 60 sec it will drop the traffic, its okay and mikotik dropped all traffic, but my question is. Are there way to upload that limit or add my public IP as whitelist?

Maybe adding in threshold.config this to avoid ban IP?
suppress gen_id 1, sig_id 2002994, track by_src, ip my_ip_public

Any idea to upload limit? I won't like remove that rule from that IP really but if there isn't another solution I will have to do it.

Thanks.

modifysid 2002994 "count 30, seconds 60;" "count 60; seconds 120;"

Hi, yesterday I had a false positive with this:
fast.log.2:04/25/2017-14:05:58.149205 [**] [1:2002994:7] ET SCAN Rapid IMAP Connections - Possible Brute Force Attack [**] [Classification: Misc activity] [Priority: 3] {TCP}

alert tcp $EXTERNAL_NET any -> $HOME_NET 143 (msg:"ET SCAN Rapid IMAP Connections - Possible Brute Force Attack"; flow:to_server; flags: S,12; threshold: type both, track by_src, count 30, seconds 60; reference:url,doc.emergingthreats.net/2002994; classtype:misc-activity; sid:2002994; rev:7;)

I'm seeing that rule which if it detects 30 count in 60 sec it will drop the traffic, its okay and mikotik dropped all traffic, but my question is. Are there way to upload that limit or add my public IP as whitelist?

Maybe adding in threshold.config this to avoid ban IP?
suppress gen_id 1, sig_id 2002994, track by_src, ip my_ip_public

Any idea to upload limit? I won't like remove that rule from that IP really but if there isn't another solution I will have to do it.

Thanks.

In oinkmaster.conf you can modify rules are they are loaded. In this case, you can change the count and seconds parameter on this rule using the following directive:

Code: Select all

modifysid 2002994 "count 30, seconds 60;" "count 60; seconds 120;"

Look for the modifysid directive in your oinkmaster.conf. For more info check out https://redmine.openinfosecfoundation.o ... Oinkmaster

Hi, yesterday I had a false positive with this:
fast.log.2:04/25/2017-14:05:58.149205 [**] [1:2002994:7] ET SCAN Rapid IMAP Connections - Possible Brute Force Attack [**] [Classification: Misc activity] [Priority: 3] {TCP}

alert tcp $EXTERNAL_NET any -> $HOME_NET 143 (msg:"ET SCAN Rapid IMAP Connections - Possible Brute Force Attack"; flow:to_server; flags: S,12; threshold: type both, track by_src, count 30, seconds 60; reference:url,doc.emergingthreats.net/2002994; classtype:misc-activity; sid:2002994; rev:7;)

I'm seeing that rule which if it detects 30 count in 60 sec it will drop the traffic, its okay and mikotik dropped all traffic, but my question is. Are there way to upload that limit or add my public IP as whitelist?

Maybe adding in threshold.config this to avoid ban IP?
suppress gen_id 1, sig_id 2002994, track by_src, ip my_ip_public

Any idea to upload limit? I won't like remove that rule from that IP really but if there isn't another solution I will have to do it.

Thanks.

In oinkmaster.conf you can modify rules are they are loaded. In this case, you can change the count and seconds parameter on this rule using the following directive:

Code: Select all

modifysid 2002994 "count 30, seconds 60;" "count 60; seconds 120;"

Look for the modifysid directive in your oinkmaster.conf. For more info check out https://redmine.openinfosecfoundation.o ... Oinkmaster

Thanks you very much as always. I added it to my file.

A question, I had in threshold.config another rules (suppress gen_id 1, sig_id 2020565 for example). I added it to oinkmaster.conf too. Suricata checks both files?

Hi, yesterday I had a false positive with this:
fast.log.2:04/25/2017-14:05:58.149205 [**] [1:2002994:7] ET SCAN Rapid IMAP Connections - Possible Brute Force Attack [**] [Classification: Misc activity] [Priority: 3] {TCP}

alert tcp $EXTERNAL_NET any -> $HOME_NET 143 (msg:"ET SCAN Rapid IMAP Connections - Possible Brute Force Attack"; flow:to_server; flags: S,12; threshold: type both, track by_src, count 30, seconds 60; reference:url,doc.emergingthreats.net/2002994; classtype:misc-activity; sid:2002994; rev:7;)

I'm seeing that rule which if it detects 30 count in 60 sec it will drop the traffic, its okay and mikotik dropped all traffic, but my question is. Are there way to upload that limit or add my public IP as whitelist?

Maybe adding in threshold.config this to avoid ban IP?
suppress gen_id 1, sig_id 2002994, track by_src, ip my_ip_public

Any idea to upload limit? I won't like remove that rule from that IP really but if there isn't another solution I will have to do it.

Thanks.

In oinkmaster.conf you can modify rules are they are loaded. In this case, you can change the count and seconds parameter on this rule using the following directive:

Code: Select all

modifysid 2002994 "count 30, seconds 60;" "count 60; seconds 120;"

Look for the modifysid directive in your oinkmaster.conf. For more info check out https://redmine.openinfosecfoundation.o ... Oinkmaster

Thanks you very much as always. I added it to my file.

A question, I had in threshold.config another rules (suppress gen_id 1, sig_id 2020565 for example). I added it to oinkmaster.conf too. Suricata checks both files?

Oinkmaster checks for new rules from the public sources and loads them. That is where the modifysid directive will take place.

Suricata uses the rules to determine how it matches patterns against the network traffic and suppresses those rules you've put in threshold.config.

Hi, I'm trying to enable notifications via email to my account but I was reading and reading and I haven't luck. I use postfix and I'm trying to send it to a server with TLS (I created file sasl in postfix with details login)
How could I enable it?

Thanks.

Hi, I'm trying to enable notifications via email to my account but I was reading and reading and I haven't luck. I use postfix and I'm trying to send it to a server with TLS (I created file sasl in postfix with details login)
How could I enable it?

Thanks.

Are you referring to maximan's implementation?

Hi, I'm trying to enable notifications via email to my account but I was reading and reading and I haven't luck. I use postfix and I'm trying to send it to a server with TLS (I created file sasl in postfix with details login)
How could I enable it?

Thanks.

Are you referring to maximan's implementation?

No no, I'm using your main implementation. I have Mikrotik enabled with logs and I receive some emails when router drops something but I would like to get emails too, is it possible?

Thanks.

Hi, I'm trying to enable notifications via email to my account but I was reading and reading and I haven't luck. I use postfix and I'm trying to send it to a server with TLS (I created file sasl in postfix with details login)
How could I enable it?

Thanks.

Are you referring to maximan's implementation?

No no, I'm using your main implementation. I have Mikrotik enabled with logs and I receive some emails when router drops something but I would like to get emails too, is it possible?

Thanks.

There is not an email function within Suricata or my implementation. Would you like an email every time a block is sent to the Mikrotik?

Hi, I'm trying to enable notifications via email to my account but I was reading and reading and I haven't luck. I use postfix and I'm trying to send it to a server with TLS (I created file sasl in postfix with details login)
How could I enable it?

Thanks.

Are you referring to maximan's implementation?

No no, I'm using your main implementation. I have Mikrotik enabled with logs and I receive some emails when router drops something but I would like to get emails too, is it possible?

Thanks.

There is not an email function within Suricata or my implementation. Would you like an email every time a block is sent to the Mikrotik?

I added that option, in rule I marked "Log" and i receive emails when Mikrotik blocks but I would like an email from Suricata if blocks anything. The email from Mikrotik isn't very useful because the info isn't complete (Not cause, not rule ID, etc)
Here a example (body is empty):

firewall,info [DROP_BLOCK] input: in:A_Router_Mail/Nas_XXXX_eth2 out:(none), src-mac f8:8e:85:e2:49:18, proto TCP (SYN), 91.197.234.22:43276->192.168.3.2:3380, len 44

thanks.

Are you referring to maximan's implementation?

No no, I'm using your main implementation. I have Mikrotik enabled with logs and I receive some emails when router drops something but I would like to get emails too, is it possible?

Thanks.

There is not an email function within Suricata or my implementation. Would you like an email every time a block is sent to the Mikrotik?

I added that option, in rule I marked "Log" and i receive emails when Mikrotik blocks but I would like an email from Suricata if blocks anything. The email from Mikrotik isn't very useful because the info isn't complete (Not cause, not rule ID, etc)
Here a example (body is empty):

firewall,info [DROP_BLOCK] input: in:A_Router_Mail/Nas_XXXX_eth2 out:(none), src-mac f8:8e:85:e2:49:18, proto TCP (SYN), 91.197.234.22:43276->192.168.3.2:3380, len 44

thanks.

Check the update to suricata_block.php that I just made.

No no, I'm using your main implementation. I have Mikrotik enabled with logs and I receive some emails when router drops something but I would like to get emails too, is it possible?

Thanks.

There is not an email function within Suricata or my implementation. Would you like an email every time a block is sent to the Mikrotik?

I added that option, in rule I marked "Log" and i receive emails when Mikrotik blocks but I would like an email from Suricata if blocks anything. The email from Mikrotik isn't very useful because the info isn't complete (Not cause, not rule ID, etc)
Here a example (body is empty):

firewall,info [DROP_BLOCK] input: in:A_Router_Mail/Nas_XXXX_eth2 out:(none), src-mac f8:8e:85:e2:49:18, proto TCP (SYN), 91.197.234.22:43276->192.168.3.2:3380, len 44

thanks.

Check the update to suricata_block.php that I just made.

Thanks! Its an email very useful
I have to change some values on my mail server because I'm using TLS 587 but I will let you know when its done and working fine.

Thanks again for your job.

There is not an email function within Suricata or my implementation. Would you like an email every time a block is sent to the Mikrotik?

I added that option, in rule I marked "Log" and i receive emails when Mikrotik blocks but I would like an email from Suricata if blocks anything. The email from Mikrotik isn't very useful because the info isn't complete (Not cause, not rule ID, etc)
Here a example (body is empty):

firewall,info [DROP_BLOCK] input: in:A_Router_Mail/Nas_XXXX_eth2 out:(none), src-mac f8:8e:85:e2:49:18, proto TCP (SYN), 91.197.234.22:43276->192.168.3.2:3380, len 44

thanks.

Check the update to suricata_block.php that I just made.

Thanks! Its an email very useful
I have to change some values on my mail server because I'm using TLS 587 but I will let you know when its done and working fine.

Thanks again for your job.

I have a little problem, when the event notification turns on? If I use from shell mail -s "test" xxxx@hotmail.com I get it, but not with script suricata_block.php. Should I enable anything more?

I can't see any log from that script. How could I debug it? Thanks again.

I added that option, in rule I marked "Log" and i receive emails when Mikrotik blocks but I would like an email from Suricata if blocks anything. The email from Mikrotik isn't very useful because the info isn't complete (Not cause, not rule ID, etc)
Here a example (body is empty):

firewall,info [DROP_BLOCK] input: in:A_Router_Mail/Nas_XXXX_eth2 out:(none), src-mac f8:8e:85:e2:49:18, proto TCP (SYN), 91.197.234.22:43276->192.168.3.2:3380, len 44

thanks.

Check the update to suricata_block.php that I just made.

Thanks! Its an email very useful
I have to change some values on my mail server because I'm using TLS 587 but I will let you know when its done and working fine.

Thanks again for your job.

I have a little problem, when the event notification turns on? If I use from shell mail -s "test" xxxx@hotmail.com I get it, but not with script suricata_block.php. Should I enable anything more?

I can't see any log from that script. How could I debug it? Thanks again.

Check these settings in php.ini:

http://php.net/manual/en/mail.configuration.php

<?php
$to = "mydomain@mydomain.com";
$subject = "My subject";
$txt = "Hello world!";
$headers = "From: mydomain@mydomain.com" . "\r\n" .
"CC: mydomain@mydomain.com";

mail($to,$subject,$txt,$headers);
?>

Check the update to suricata_block.php that I just made.

Thanks! Its an email very useful
I have to change some values on my mail server because I'm using TLS 587 but I will let you know when its done and working fine.

Thanks again for your job.

I have a little problem, when the event notification turns on? If I use from shell mail -s "test" xxxx@hotmail.com I get it, but not with script suricata_block.php. Should I enable anything more?

I can't see any log from that script. How could I debug it? Thanks again.

Check these settings in php.ini:

http://php.net/manual/en/mail.configuration.php

I got a test successfully but I don't receive emails from script suricata-block. How could I debug it more? I have alerts on Snorby which I think that I should have an email from script.

Here my test.php which I receive alerts:

Code: Select all

<?php
$to = "mydomain@mydomain.com";
$subject = "My subject";
$txt = "Hello world!";
$headers = "From: mydomain@mydomain.com" . "\r\n" .
"CC: mydomain@mydomain.com";

mail($to,$subject,$txt,$headers);
?>

Thanks.

Thanks! Its an email very useful
I have to change some values on my mail server because I'm using TLS 587 but I will let you know when its done and working fine.

Thanks again for your job.

I have a little problem, when the event notification turns on? If I use from shell mail -s "test" xxxx@hotmail.com I get it, but not with script suricata_block.php. Should I enable anything more?

I can't see any log from that script. How could I debug it? Thanks again.

Check these settings in php.ini:

http://php.net/manual/en/mail.configuration.php

I got a test successfully but I don't receive emails from script suricata-block. How could I debug it more? I have alerts on Snorby which I think that I should have an email from script.

Here my test.php which I receive alerts:

Code: Select all

<?php
$to = "mydomain@mydomain.com";
$subject = "My subject";
$txt = "Hello world!";
$headers = "From: mydomain@mydomain.com" . "\r\n" .
"CC: mydomain@mydomain.com";

mail($to,$subject,$txt,$headers);
?>

Thanks.

You did update suricata_block.php with the new one? That is the same code as in suricata_block.

Hi again Tomfisk, I have a little question about OSSEC. My mikrotik never receive any entry by OSSEC. I think that OSSEC isn't adding any info to SQL because I have logs in the script and all is empty. How could I debug it?
I have logs with info, as
/etc/ossec/logs/active-responses.log
Here a example:

Code: Select all

Thu May  4 10:51:19 CEST 2017 /etc/ossec/active-response/bin/mikrotik-fw.sh add - - 1493887879.417847 100074 (mail.xxx.dom) 192.168.X.X->/var/log/syslog -

I think that I am getting blank result and for that reason mikrotik doesn't add any rule, here output exec scrip:

Code: Select all

#sh -x mikrotik-fw.sh 
+ ACTION=
+ USER=
+ IP=
+ ALERTID=
+ RULEID=
+ dirname mikrotik-fw.sh
+ LOCAL=.
+ cd .
+ cd ../
+ pwd
+ PWD=/etc/ossec/active-response
+ date
+ echo Fri May  5 13:15:39 CEST 2017 mikrotik-fw.sh        
+ echo 
+ cut -d . -f 1
+ ALERTTIME=
+ echo 
+ cut -d . -f 2
+ ALERTLAST=
+ sed -n //,/^$/{/^$/!p} /etc/ossec/active-response/../logs/alerts/alerts.log
+ tail -n1
sed: -e expression #1, char 0: no previous regular expression
+ LOGLINE=
mikrotik-fw.sh: 2: mikrotik-fw.sh: Syntax error: redirection unexpected

Any idea? thanks as always!

Hi again Tomfisk, I have a little question about OSSEC. My mikrotik never receive any entry by OSSEC. I think that OSSEC isn't adding any info to SQL because I have logs in the script and all is empty. How could I debug it?
I have logs with info, as
/etc/ossec/logs/active-responses.log
Here a example:

Code: Select all

Thu May  4 10:51:19 CEST 2017 /etc/ossec/active-response/bin/mikrotik-fw.sh add - - 1493887879.417847 100074 (mail.xxx.dom) 192.168.X.X->/var/log/syslog -

I think that I am getting blank result and for that reason mikrotik doesn't add any rule, here output exec scrip:

Code: Select all

#sh -x mikrotik-fw.sh 
+ ACTION=
+ USER=
+ IP=
+ ALERTID=
+ RULEID=
+ dirname mikrotik-fw.sh
+ LOCAL=.
+ cd .
+ cd ../
+ pwd
+ PWD=/etc/ossec/active-response
+ date
+ echo Fri May  5 13:15:39 CEST 2017 mikrotik-fw.sh        
+ echo 
+ cut -d . -f 1
+ ALERTTIME=
+ echo 
+ cut -d . -f 2
+ ALERTLAST=
+ sed -n //,/^$/{/^$/!p} /etc/ossec/active-response/../logs/alerts/alerts.log
+ tail -n1
sed: -e expression #1, char 0: no previous regular expression
+ LOGLINE=
mikrotik-fw.sh: 2: mikrotik-fw.sh: Syntax error: redirection unexpected

Any idea? thanks as always!

Can you show the line from the alerts.log that it is trying to match against? The script is trying to find an IP address in line that triggered the active response.

Hi again Tomfisk, I have a little question about OSSEC. My mikrotik never receive any entry by OSSEC. I think that OSSEC isn't adding any info to SQL because I have logs in the script and all is empty. How could I debug it?
I have logs with info, as
/etc/ossec/logs/active-responses.log
Here a example:

Code: Select all

Thu May  4 10:51:19 CEST 2017 /etc/ossec/active-response/bin/mikrotik-fw.sh add - - 1493887879.417847 100074 (mail.xxx.dom) 192.168.X.X->/var/log/syslog -

I think that I am getting blank result and for that reason mikrotik doesn't add any rule, here output exec scrip:

Code: Select all

#sh -x mikrotik-fw.sh 
+ ACTION=
+ USER=
+ IP=
+ ALERTID=
+ RULEID=
+ dirname mikrotik-fw.sh
+ LOCAL=.
+ cd .
+ cd ../
+ pwd
+ PWD=/etc/ossec/active-response
+ date
+ echo Fri May  5 13:15:39 CEST 2017 mikrotik-fw.sh        
+ echo 
+ cut -d . -f 1
+ ALERTTIME=
+ echo 
+ cut -d . -f 2
+ ALERTLAST=
+ sed -n //,/^$/{/^$/!p} /etc/ossec/active-response/../logs/alerts/alerts.log
+ tail -n1
sed: -e expression #1, char 0: no previous regular expression
+ LOGLINE=
mikrotik-fw.sh: 2: mikrotik-fw.sh: Syntax error: redirection unexpected

Any idea? thanks as always!

Can you show the line from the alerts.log that it is trying to match against? The script is trying to find an IP address in line that triggered the active response.

Sorry for late reply. Here a example alerts.log:

** Alert 1494324377.869344: mail - syslog,sshd,
2017 May 09 12:06:17 (mail.mydomain.com) 192.168.X.X->/var/log/auth.log
Rule: 5739 (level 4) -> 'SSHD configuration error (AuthorizedKeysCommand)'
May 9 12:00:30 mail sshd[10831]: error: Could not stat AuthorizedKeysCommand "/usr/local/bin/ssh-ldap-pubkey-wrapper": No such file or directory

Thanks!

Hi again Tomfisk, I have a little question about OSSEC. My mikrotik never receive any entry by OSSEC. I think that OSSEC isn't adding any info to SQL because I have logs in the script and all is empty. How could I debug it?
I have logs with info, as
/etc/ossec/logs/active-responses.log
Here a example:

Code: Select all

Thu May  4 10:51:19 CEST 2017 /etc/ossec/active-response/bin/mikrotik-fw.sh add - - 1493887879.417847 100074 (mail.xxx.dom) 192.168.X.X->/var/log/syslog -

I think that I am getting blank result and for that reason mikrotik doesn't add any rule, here output exec scrip:

Code: Select all

#sh -x mikrotik-fw.sh 
+ ACTION=
+ USER=
+ IP=
+ ALERTID=
+ RULEID=
+ dirname mikrotik-fw.sh
+ LOCAL=.
+ cd .
+ cd ../
+ pwd
+ PWD=/etc/ossec/active-response
+ date
+ echo Fri May  5 13:15:39 CEST 2017 mikrotik-fw.sh        
+ echo 
+ cut -d . -f 1
+ ALERTTIME=
+ echo 
+ cut -d . -f 2
+ ALERTLAST=
+ sed -n //,/^$/{/^$/!p} /etc/ossec/active-response/../logs/alerts/alerts.log
+ tail -n1
sed: -e expression #1, char 0: no previous regular expression
+ LOGLINE=
mikrotik-fw.sh: 2: mikrotik-fw.sh: Syntax error: redirection unexpected

Any idea? thanks as always!

Can you show the line from the alerts.log that it is trying to match against? The script is trying to find an IP address in line that triggered the active response.

Sorry for late reply. Here a example alerts.log:

** Alert 1494324377.869344: mail - syslog,sshd,
2017 May 09 12:06:17 (mail.mydomain.com) 192.168.X.X->/var/log/auth.log
Rule: 5739 (level 4) -> 'SSHD configuration error (AuthorizedKeysCommand)'
May 9 12:00:30 mail sshd[10831]: error: Could not stat AuthorizedKeysCommand "/usr/local/bin/ssh-ldap-pubkey-wrapper": No such file or directory

Thanks!

If that is the actual contents of the log, "192.168.X.X" then the reason it is failing is because the script can't find a full IP address.

Hi again Tomfisk, I have a little question about OSSEC. My mikrotik never receive any entry by OSSEC. I think that OSSEC isn't adding any info to SQL because I have logs in the script and all is empty. How could I debug it?
I have logs with info, as
/etc/ossec/logs/active-responses.log
Here a example:

Code: Select all

Thu May  4 10:51:19 CEST 2017 /etc/ossec/active-response/bin/mikrotik-fw.sh add - - 1493887879.417847 100074 (mail.xxx.dom) 192.168.X.X->/var/log/syslog -

I think that I am getting blank result and for that reason mikrotik doesn't add any rule, here output exec scrip:

Code: Select all

#sh -x mikrotik-fw.sh 
+ ACTION=
+ USER=
+ IP=
+ ALERTID=
+ RULEID=
+ dirname mikrotik-fw.sh
+ LOCAL=.
+ cd .
+ cd ../
+ pwd
+ PWD=/etc/ossec/active-response
+ date
+ echo Fri May  5 13:15:39 CEST 2017 mikrotik-fw.sh        
+ echo 
+ cut -d . -f 1
+ ALERTTIME=
+ echo 
+ cut -d . -f 2
+ ALERTLAST=
+ sed -n //,/^$/{/^$/!p} /etc/ossec/active-response/../logs/alerts/alerts.log
+ tail -n1
sed: -e expression #1, char 0: no previous regular expression
+ LOGLINE=
mikrotik-fw.sh: 2: mikrotik-fw.sh: Syntax error: redirection unexpected

Any idea? thanks as always!

Can you show the line from the alerts.log that it is trying to match against? The script is trying to find an IP address in line that triggered the active response.

Sorry for late reply. Here a example alerts.log:

** Alert 1494324377.869344: mail - syslog,sshd,
2017 May 09 12:06:17 (mail.mydomain.com) 192.168.X.X->/var/log/auth.log
Rule: 5739 (level 4) -> 'SSHD configuration error (AuthorizedKeysCommand)'
May 9 12:00:30 mail sshd[10831]: error: Could not stat AuthorizedKeysCommand "/usr/local/bin/ssh-ldap-pubkey-wrapper": No such file or directory

Thanks!

If that is the actual contents of the log, "192.168.X.X" then the reason it is failing is because the script can't find a full IP address.

Uhmm so I understand that its works fine, when an event ocurrs script will get full IP I understand, don't?

Thanks!

Can you show the line from the alerts.log that it is trying to match against? The script is trying to find an IP address in line that triggered the active response.

Sorry for late reply. Here a example alerts.log:

** Alert 1494324377.869344: mail - syslog,sshd,
2017 May 09 12:06:17 (mail.mydomain.com) 192.168.X.X->/var/log/auth.log
Rule: 5739 (level 4) -> 'SSHD configuration error (AuthorizedKeysCommand)'
May 9 12:00:30 mail sshd[10831]: error: Could not stat AuthorizedKeysCommand "/usr/local/bin/ssh-ldap-pubkey-wrapper": No such file or directory

Thanks!

If that is the actual contents of the log, "192.168.X.X" then the reason it is failing is because the script can't find a full IP address.

Uhmm so I understand that its works fine, when an event ocurrs script will get full IP I understand, don't?

Thanks!

Yes, if the event from the log file contains the full IP address, it works.

Wow, this is fantastic . A great way to do it without having to use an inline IPS.
Just one question: can we do it without snorby? Could you maybe provide a full sql schema instead of just your additions for barnyard to write to?

I usually use suricata with evebox (json api) so I was just trying to think of a way to use this solution without having to install ruby and such.

Edit: would this schema work when imported into a database named snorby?
https://github.com/firnsy/barnyard2/blo ... eate_mysql

Thanks

Hi,

Tried to implement this solution. I use Ubuntu 16.04 with mysql 5.7.18. When I put a trigger in place I get this while loading barnyard2:

Code: Select all

ERROR: database mysql_error: Unknown column 'event.id' in 'field list'
SQL=[INSERT INTO iphdr (sid, cid, ip_src, ip_dst, ip_ver, ip_hlen, ip_tos, ip_len, ip_id, ip_flags, ip_off,ip_ttl, ip_proto, ip_csum) VALUES (1,2211,3232235876,3109214298,4,5,0,44,12662,0,0,64,17,46481);]
Fatal Error, Quitting..

Without a trigger my events are exporting fine. It seems my db cannot handle nested querry. Any ideas what is wrong?

show columns from event;

Code: Select all

+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| sid | int(10) unsigned | NO | PRI | NULL | |
| cid | int(10) unsigned | NO | PRI | NULL | |
| signature | int(10) unsigned | NO | MUL | NULL | |
| timestamp | datetime | NO | MUL | NULL | |
+-----------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

It was created using schema in barnyard2 repository.

Hey guys, i am stuck on the whole Database Trigger part. Whenever i try to add the trigger, I get a syntax error. I tried to add it to the triggers using phpMyAdmin, but still i got the same error.

I am really eager to try this as it seems fairly interesting,

Thanks!
error.JPG

Hey guys, i am stuck on the whole Database Trigger part. Whenever i try to add the trigger, I get a syntax error. I tried to add it to the triggers using phpMyAdmin, but still i got the same error.

I am really eager to try this as it seems fairly interesting,

Thanks!
error.JPG

Does my reply from Mon Feb 27, 2017 10:44 am help?

DELIMITER ;;
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;;
DELIMITER ;

Ok, new record are added to firewall's access list. One issue with it, is that they are added as unsigned long. Somehow they are not converted to addresses.

Hey guys, i am stuck on the whole Database Trigger part. Whenever i try to add the trigger, I get a syntax error. I tried to add it to the triggers using phpMyAdmin, but still i got the same error.

I am really eager to try this as it seems fairly interesting,

Thanks!
error.JPG

Does my reply from Mon Feb 27, 2017 10:44 am help?

Try with changing delimiter:

Code: Select all

DELIMITER ;;
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;;
DELIMITER ;

ERROR 1054 (42S22) at line 2: Unknown column 'sid' in 'NEW'

Ok, new record are added to firewall's access list. One issue with it, is that they are added as unsigned long. Somehow they are not converted to addresses.

Check the records in the block_queue table. Should be populated with IP v4 addresses. They are converted from numeric to IP v4 address by the trigger with inet_ntoa function.

Hey tom, i think there is an issue with the routeros_api.class.php file i got. Is there a dedicated link to the one you used? I got mine from https://github.com/BenMenking/routeros- ... .class.php and when i run it, i get errors such as:

Code: Select all

PHP Warning:  fwrite() expects parameter 1 to be resource, boolean given in /usr/local/bin/routeros_api.class.php on line 332
PHP Warning:  fwrite() expects parameter 1 to be resource, boolean given in /usr/local/bin/routeros_api.class.php on line 339
PHP Warning:  fread() expects parameter 1 to be resource, boolean given in /usr/local/bin/routeros_api.class.php on line 255
PHP Warning:  socket_get_status() expects parameter 1 to be resource, boolean given in /usr/local/bin/routeros_api.class.php on line 302
PHP Notice:  Undefined offset: 0 in /usr/local/bin/suricata_block.php on line 125

Thanks!

/*****************************
 *
 * RouterOS PHP API class v1.6
 * Author: Denis Basta
 * Contributors:
 *    Nick Barnes
 *    Ben Menking (ben [at] infotechsc [dot] com)
 *    Jeremy Jefferson (http://jeremyj.com)
 *    Cristian Deluxe (djcristiandeluxe [at] gmail [dot] com)
 *    Mikhail Moskalev (mmv.rus [at] gmail [dot] com)
 *
 * http://www.mikrotik.com
 * http://wiki.mikrotik.com/wiki/API_PHP_class
 *
 ******************************/

This looks like one of the things i've been searching for however i would like to know how much CPU is needed for this. Would using a quad core ARM A17 with gigabit ethernet would be sufficient for gigabit speeds? I would like to keep things as low power as possible and my routerboard has a usb port which can power it.

This looks like one of the things i've been searching for however i would like to know how much CPU is needed for this. Would using a quad core ARM A17 with gigabit ethernet would be sufficient for gigabit speeds? I would like to keep things as low power as possible and my routerboard has a usb port which can power it.

When it comes to running suricata it really depends on your traffic mix. If you have lots of users on your network then you'll have more unique sessions to keep track of. In general, the more memory you have available, the better off you will be. I'm running on a quad core ARM A9 with gigabit ethernet (4gb memory) with loads peaking around 200mbs and with typically less than 100 sessions. The A17 is supposed to be 60% higher performance. So I'd give it a qualified "yes" if you have the memory to support your sessions. There is an entire chapter on suricata performance in their documentation which might be worth a read: http://suricata.readthedocs.io/en/lates ... index.html

Hello, I'm try to implement this one, but I don't know how to test it, works or not. Please help my!

Good day
i've made this,
added a new column for queue_block table

Code: Select all

ALTER TABLE block_queue ADD que_ip_adrlan VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL;

modify trigger

Code: Select all

                         
....                         
                           INSERT INTO block_queue
                         SET que_ip_adr =NEW.ip_src,
                               `que_ip_adrlan = 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;
                          ELSE
                            INSERT INTO block_queue
                         SET que_ip_adr =NEW.ip_dst,
                               `que_ip_adrlan = 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;
                          END IF;
                        END IF;
                      END;;
                      DELIMITER ;

Now we can use the other ip address, for example

Code: Select all

$API->comm("/ip/firewall/address-list/add", array(
      "list" => "Blocked",
      "address" => $thisrow['que_ip_adr'],
      "timeout" => $timeremaining,
      "comment" => "From suricata, " . $thisrow[ 'que_ip_adrlan' ] .    $thisrow['que_sig_name'] . " => " . $thisrow['que_sig_gid'] . ":" . $thisrow['que_sig_sid'] .
         " => event timestamp: " . $thisrow['que_event_timestamp'],));

Tom i have a doubt, how did u choose the 20 signatures to block?? i see around 1000 in signature table , or where can i get info about how to choose them, thank you

regards

pkill -USR2 -u snort -f /usr/bin/suricata

A little thing. I monitor my servers with Nagios and my IDS server is increasing always process without kill old process, are there way to kill old process automatically?

I normally each week, stop daemon and re-start all again.

Thanks!

In my nightly process to update the rules, I issue the following command to suricata:

Code: Select all

pkill -USR2 -u snort -f /usr/bin/suricata

This might help with the problem.

A little thing. I monitor my servers with Nagios and my IDS server is increasing always process without kill old process, are there way to kill old process automatically?

I normally each week, stop daemon and re-start all again.

Thanks!

#!/bin/bash                                             
/usr/local/bin/oinkmaster.pl -C /etc/suricata/oinkmaster.conf -o /etc/suricata/rules                            
chown snort:snort /etc/suricata/rules/*                 
pkill -USR2 -u snort -f /usr/bin/suricata               
/etc/init.d/aanval restart                              
/etc/init.d/barnyard2 stop                              
sleep 5                                                 
/etc/init.d/barnyard2 start

In my nightly process to update the rules, I issue the following command to suricata:

Code: Select all

pkill -USR2 -u snort -f /usr/bin/suricata

This might help with the problem.

A little thing. I monitor my servers with Nagios and my IDS server is increasing always process without kill old process, are there way to kill old process automatically?

I normally each week, stop daemon and re-start all again.

Thanks!

Yes, It can help, do you start after process again with any particular script or with normal script?

Thanks.

I do it in my oinkupdate.sh script.

Code: Select all

#!/bin/bash                                             
/usr/local/bin/oinkmaster.pl -C /etc/suricata/oinkmaster.conf -o /etc/suricata/rules                            
chown snort:snort /etc/suricata/rules/*                 
pkill -USR2 -u snort -f /usr/bin/suricata               
/etc/init.d/aanval restart                              
/etc/init.d/barnyard2 stop                              
sleep 5                                                 
/etc/init.d/barnyard2 start

In my nightly process to update the rules, I issue the following command to suricata:

Code: Select all

pkill -USR2 -u snort -f /usr/bin/suricata

This might help with the problem.

A little thing. I monitor my servers with Nagios and my IDS server is increasing always process without kill old process, are there way to kill old process automatically?

I normally each week, stop daemon and re-start all again.

Thanks!

Yes, It can help, do you start after process again with any particular script or with normal script?

Thanks.

1. sudo nano /etc/mysql/my.cnf
2. Add this to the end of the file
   Code: Select all

   [mysqld]  
   sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3. sudo service mysql restart to restart MySQL

Very much I ask - help!
Everything was done step by step.
After creating the table, sigs_to_block and TRIGGER barnyard2 stopped writing to the database.
Ends with an error:

Code: Select all

Aug 30 11:43:14 sv-ips-01 barnyard2: FATAL ERROR: database mysql_error: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'snorby.sigs_to_block.src_or_dst'; this is incompatible with sql_mode=only_full_group_by#012#011SQL=[INSERT INTO iphdr (sid, cid, ip_src, ip_dst, ip_ver, ip_hlen, ip_tos, ip_len, ip_id, ip_flags, ip_off,ip_ttl, ip_proto, ip_csum) VALUES (1,22527,1539234099,3274620322,4,5,0,45,0,0,0,0,6,1289);]

ОК.
I turned on the desired mode in mySQL:

Code: Select all

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0,00 sec)

It did not help!
How to fix it?
What's my mistake?
There are no answers on the Internet ...
Help me please!

Code: Select all

OS: Ubuntu 16.04.3 AMD64
Barnyard2 Version 2.1.14 (Build 337)

As I understand, this is not working properly trigger ..
I created the trigger like this:

Code: Select all

mysql -u root -p snorby < trigger_code.sql

trigger_code.sql:

Code: Select all

DELIMITER ;;
                      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 =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 =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;;
DELIMITER ;

You must have MySQL version 5.7.5 or greater. I believe you will need to disable the ONLY_FULL_GROUP_BY sql_mode with the following:

1. sudo nano /etc/mysql/my.cnf
2. Add this to the end of the file
   Code: Select all

   [mysqld]  
   sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3. sudo service mysql restart to restart MySQL

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql -u root -p snorby < trigger_code.sql

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

/etc/mysql/mysql.conf.d/mysqld.cnf

mysql> use snorby;
mysql> SHOW TRIGGERS

| after_iphdr_insert | INSERT | iphdr | BEGIN
....
END | AFTER  | 2017-08-30 15:04:14.71 | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | utf8_unicode_ci

You must have MySQL version 5.7.5 or greater. I believe you will need to disable the ONLY_FULL_GROUP_BY sql_mode with the following:

1. sudo nano /etc/mysql/my.cnf
2. Add this to the end of the file
   Code: Select all

   [mysqld]  
   sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3. sudo service mysql restart to restart MySQL

OK.
I did everything as you wrote.

Code: Select all

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

I create a trigger again:

Code: Select all

mysql -u root -p snorby < trigger_code.sql

And run barnyard2...
.......
HM
......
And he does not fall!
It turns out, the trigger should be added only after the mode on:

Code: Select all

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Note.
In Ubuntu in the settings file to the path:

Code: Select all

/etc/mysql/mysql.conf.d/mysqld.cnf

Let's see the created Trigger:

Code: Select all

mysql> use snorby;
mysql> SHOW TRIGGERS

| after_iphdr_insert | INSERT | iphdr | BEGIN
....
END | AFTER  | 2017-08-30 15:04:14.71 | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | utf8_unicode_ci

The trigger should be created only after the settings MySQL...

Thank you!

suricata_block.php adds the following addresses to MikroTik:
But it's not right!

How to fix?

DELIMITER ;;
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;;
DELIMITER ;

It's me again ...
I run suricata_block.pxp from the command line:

Code: Select all

php -f /usr/bin/suricata_block.php

He gives me this in the console:

Code: Select all

PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157

Is this normal? Or am I wrong again somewhere?

whereis sendmail

It's me again ...
I run suricata_block.pxp from the command line:

Code: Select all

php -f /usr/bin/suricata_block.php

He gives me this in the console:

Code: Select all

PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157
sh: /usr/sbin/sendmail: No such file or directory
PHP Warning:  mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /usr/bin/suricata_block.php on line 157

Is this normal? Or am I wrong again somewhere?

Hi,

How could I add an IP as whitelist?

Thanks.

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Hi,

How could I add an IP as whitelist?

Thanks.

I put a line in threshold.config indicating the specific rule and IP address to suppress.

Code: Select all

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Thanks for the tutorial.

I had a question, is it possible to combine this scripts with SELKS ? or any guide so I can integrated SELKS with Mikrotik ?

It is possible to customize and/or build a SELKS distribution and there are guides here https://github.com/StamusNetworks/SELKS ... zing-SELKS and here https://github.com/StamusNetworks/SELKS ... ding-SELKS. Including this functionality into the SELKS distribution would be possible, but it would require analysis of what packages would need to be included in a SELKS distribution and specific instructions on how to configure. I don't know if the suricata implementation in SELKS includes for example, MySQL and barnyard2, or if it is configured as an inline IPS using with linux firewall rule processing.

Thanks for the tutorial.

I had a question, is it possible to combine this scripts with SELKS ? or any guide so I can integrated SELKS with Mikrotik ?

It is possible to customize and/or build a SELKS distribution and there are guides here https://github.com/StamusNetworks/SELKS ... zing-SELKS and here https://github.com/StamusNetworks/SELKS ... ding-SELKS. Including this functionality into the SELKS distribution would be possible, but it would require analysis of what packages would need to be included in a SELKS distribution and specific instructions on how to configure. I don't know if the suricata implementation in SELKS includes for example, MySQL and barnyard2, or if it is configured as an inline IPS using with linux firewall rule processing.

Thanks for the tutorial.

I had a question, is it possible to combine this scripts with SELKS ? or any guide so I can integrated SELKS with Mikrotik ?

I had install SELK4.0nodesktop , and try to combine with your scripts with installed library dependencies . How I know if the scripts did worked ? Which mikrotik interface should be sniffed ?

Thank you

Someone who can help me, I need sent Mikrotik from the Suricata, without MySQl some easy php like https://wiki.mikrotik.com/wiki/Mikrotik_IPS_IDS

Hi,

How could I add an IP as whitelist?

Thanks.

I put a line in threshold.config indicating the specific rule and IP address to suppress.

Code: Select all

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Hi,

How could I add an IP as whitelist?

Thanks.

I put a line in threshold.config indicating the specific rule and IP address to suppress.

Code: Select all

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Hi, re-open this reply. How could I do a whitelist for all entry for one IP?

I had this: suppress gen_id 1, sig_id 20003XX, track by_src, ip 213.XX.XX.XX

But my IP was banned again with this:
1:20003XX:13] ET P2P BitTorrent peer sync [**] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.X.X:55618 -> 213.XX.XX.XX:873

Why? Should I use in file 20003XX:13? How maybe my IP can be banned for more causes, I would ask if its possible add my IP as whitelist.

Thanks!!

I think you can justset gen_id and sig_id to 0 and it should apply to all events for that IP address.

Hi,

How could I add an IP as whitelist?

Thanks.

I put a line in threshold.config indicating the specific rule and IP address to suppress.

Code: Select all

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Hi, re-open this reply. How could I do a whitelist for all entry for one IP?

I had this: suppress gen_id 1, sig_id 20003XX, track by_src, ip 213.XX.XX.XX

But my IP was banned again with this:
1:20003XX:13] ET P2P BitTorrent peer sync [**] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.X.X:55618 -> 213.XX.XX.XX:873

Why? Should I use in file 20003XX:13? How maybe my IP can be banned for more causes, I would ask if its possible add my IP as whitelist.

Thanks!!

suppress gen_id 0, sig_id 0, track_by_src, ip 213.98.XX.XX

I think you can justset gen_id and sig_id to 0 and it should apply to all events for that IP address.

Hi,

How could I add an IP as whitelist?

Thanks.

I put a line in threshold.config indicating the specific rule and IP address to suppress.

Code: Select all

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Hi, re-open this reply. How could I do a whitelist for all entry for one IP?

I had this: suppress gen_id 1, sig_id 20003XX, track by_src, ip 213.XX.XX.XX

But my IP was banned again with this:
1:20003XX:13] ET P2P BitTorrent peer sync [**] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.X.X:55618 -> 213.XX.XX.XX:873

Why? Should I use in file 20003XX:13? How maybe my IP can be banned for more causes, I would ask if its possible add my IP as whitelist.

Thanks!!

Awesome your fast reply...
this ?
suppress gen_id 0, track_by_src, ip 213.98.XX.XX

Thanks.

This:

Code: Select all

suppress gen_id 0, sig_id 0, track_by_src, ip 213.98.XX.XX

This:

Code: Select all

suppress gen_id 0, sig_id 0, track_by_src, ip 213.98.XX.XX

Done! thanks. A bit question. I was using gen_id 1 always, why now 0?

You'd have to ask the developers that question...just what I found in the documentation

This:

Code: Select all

suppress gen_id 0, sig_id 0, track_by_src, ip 213.98.XX.XX

Done! thanks. A bit question. I was using gen_id 1 always, why now 0?

01/31/2018-09:18:26.904899  [**] [1:2009247:3] ET SHELLCODE Rothenburg Shellcode [**] [Classification: Executable code was detected] [Priority: 1] {TCP} 192.168.XX.XX:58471 -> 213.98.XX.XX:873

set file-limit=3000KiB filter-interface=ether1 filter-ip-address=!192.168.xx.xx/32 filter-stream=\
    yes streaming-enabled=yes streaming-server=192.168.xx.xx

You'd have to ask the developers that question...just what I found in the documentation

This:

Code: Select all

suppress gen_id 0, sig_id 0, track_by_src, ip 213.98.XX.XX

Done! thanks. A bit question. I was using gen_id 1 always, why now 0?

With that rule router yet banned my IP again. What am i doing wrong?
I added with both id
suppress gen_id 1, sig_id 0, track_by_src, ip 192.168.XX.XX
suppress gen_id 0, sig_id 0, track_by_src, ip 192.168.XX.XX

Line in fast.log

Code: Select all

01/31/2018-09:18:26.904899  [**] [1:2009247:3] ET SHELLCODE Rothenburg Shellcode [**] [Classification: Executable code was detected] [Priority: 1] {TCP} 192.168.XX.XX:58471 -> 213.98.XX.XX:873

BTW: I use suricata 1.4.7, but in his website there are version 2.0.2 and 4.0.3. Anyone test it?

Thanks.

Interesting. That should work, but let's tackle the problem in a different and more efficient manner. In the setup on the Mikrotik sniffer, let's just drop all of the packets from the IP you want to ignore set that it doesn't get sent to suricata in the first place. Set up a filter to exclude the address from the packet sniffer like this:

Code: Select all

set file-limit=3000KiB filter-interface=ether1 filter-ip-address=!192.168.xx.xx/32 filter-stream=\
    yes streaming-enabled=yes streaming-server=192.168.xx.xx

So packets from that address never get into suricata in the first place.

Interesting. That should work, but let's tackle the problem in a different and more efficient manner. In the setup on the Mikrotik sniffer, let's just drop all of the packets from the IP you want to ignore set that it doesn't get sent to suricata in the first place. Set up a filter to exclude the address from the packet sniffer like this:

Code: Select all

set file-limit=3000KiB filter-interface=ether1 filter-ip-address=!192.168.xx.xx/32 filter-stream=\
    yes streaming-enabled=yes streaming-server=192.168.xx.xx

So packets from that address never get into suricata in the first place.

I can't add that IP. If I try to save the changes, MK doesn't accept it and its reseted to 0.0.0.0 again
I attach picture, maybe I am doing something bad.

The "!" goes in the little box before the IP address. Just click on it and it should change to "!".

  echo "Target will be: " . $target . "\r\n";
  return true;
  try {
      $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
  } catch (Exception $e) {
      die('Unable to connect to RouterOS. Error:' . $e);
  }

I have installed a new VPS with new suricata 4.0.3, its installed correctly and I can start it good:

Code: Select all

root@suricatanew:/# trafr -s | suricata -c /etc/suricata/suricata.yaml -r -
31/1/2018 -- 07:01:57 - <Notice> - This is Suricata version 4.0.3 RELEASE
31/1/2018 -- 07:02:02 - <Notice> - all 5 packet processing threads, 4 management threads initialized, engine started.

I see traffic on stats.log

Code: Select all

Date: 1/31/2018 -- 07:05:53 (uptime: 0d, 00h 00m 16s)
------------------------------------------------------------------------------------
Counter                                    | TM Name                   | Value
------------------------------------------------------------------------------------
decoder.pkts                               | Total                     | 275968
decoder.bytes                              | Total                     | 277270818

I tested new method (with php). I have the script running but MK doesn't add any IP. I have configured details correctly to login API (same I used old suricata).
In MK I only changed Sniffer to old server to new server (I get packets).

When I execute php I only see same logs, example:

Code: Select all

root@suricatanew:/etc/init.d# php -f fast2mikrotik.php &
[1] 543
root@suricatanew:/etc/init.d# Target will be: 94.31.29.64
Target will be: 94.31.29.64
Target will be: 94.31.29.64
Target will be: 94.31.29.64

Any idea? Thanks! Maybe I am forgotten any simple thing.

Oops! Looks like I left some debug code in fast2mikrotik.php
Delete the "echo" and "return" lines...should work then

I am really tempted on setting Suricata with Mtik integration.
I want to run suricata on a QNAP as a VM and I already bought an Intel NIC for this purpose, however I noticed that the current QNAP versions do not support promiscuous mode for VMs, as such if I try to mirror the WAN interface I end up only getting multicast and broadcast packets.
This sniffer stream appears to be a perfect solution, however it does stop FastPath and FastTrack right?
I'm afraid RB3011 will be struggling with Gigabit WAN.
Apologies for the novice questions =)

Definitely Suricata has any bug with threshold.

Code: Select all

suppress gen_id 1, sig_id 2020565, track by_src, ip 8.8.8.8

And I receive an alert:

Code: Select all

The IP address 8.8.8.8 has been blocked due to the following rule match:

The signature ID is [1:2020565:1] ET POLICY Dropbox DNS Lookup - Possible Offsite File Backup in Use
    event timestamp: 02/02/2018-12:18:30.409788 blocked for: 01:00:00

Unfortunately I have to remove that rules from directory rules, if not all time IPs as Google o my own IP are banned,

I don't understand why... but I have threshold uncomment in suricata.yaml... anyone occurs same?

Thanks.