Ir para conteúdo
meebo
Entre para seguir isso  
Guest Paulo

Integração Suricata IDS / IPS com Mikrotik agora com OSSEC

Recommended Posts

Guest Paulo

Atualização 7-Dezembro-2017 Para quem não quer mexer com o MySQL, adicionei o fast2mikrotik.php que lerá os eventos do suricata do fast.log e criará as regras do firewall. 

Atualização de 26 de novembro de 2017 Consulte meu post Análise de endereço IP histórico para prevenção de invasões sobre como o histórico de eventos pode ser usado para identificar grupos de ofensores freqüentes e criar regras de firewall para evitar que esses criminosos freqüentes acessem sua rede. 

Atualizado em 10 de março de 2017
A lista de assinaturas que eu uso, agora são 51 assinaturas.
O suricata_block.php foi atualizado para reconstruir a lista de endereços bloqueados após a reinicialização do Mikrotik (por exemplo, atualização de firmware). Ao atualizar o firmware no roteador, pare o processo suricata_block, execute a atualização e inicie o processo suricata_block. A lista de endereços bloqueados será repovoada
Atualizado em 9 de abril de 2017
Adicionado na funcionalidade OSSEC IPS (após a seção suricata)
Atualizado em 27 de abril de 2017
Adicionado função de notificação por email em suricata_block.php
Atualizado em 6 de junho de 2017
Esquema de banco de dados snort / snorby incluído
Atualizado em 19 de junho de 2017
bug fixo no mikrotik-fw.sh para resposta ativa ossec
Já vi alguns posts neste fórum pedindo integração de um IDS / IPS na plataforma Mikrotik. Embora isso seja conveniente, acho que os argumentos apresentados são muito convincentes. Como Robert Penz apontou em seu blog ( http://robert.penz.name/849/howto-setup ... ta-as-ids / ) é muito fácil configurar um roteador Mikrotik para transmitir os pacotes para um Plataforma IDS (suricata / snort). A parte que está faltando neste cenário é a parte IPS da solução. Para implementar um IPS, os alertas que o IDS sinaliza precisam modificar as regras do firewall para bloquear o tráfego ofensivo. Neste artigo, vou fazer o layout do que fiz para introduzir uma solução IPS no mix.

Minha instalação do IDS para minha extensa rede doméstica é bastante direta. É uma implementação do Suricata da usina, usando o conjunto de regras Emergência de Ameaças, que, quando disparadas, são levadas do formato Unified2 para um banco de dados MySQL usando o barnyard2. Eu uso o Aanval como meu console para monitorar alertas. Minha plataforma Suricata é um Solid Run CuBox I4 (braço quádruplo, 4gb de memória, Debian Jessie) e um disco rígido de 1TB (um exagero, mas eram peças de reposição) conectado via eSata. Eu tenho isso diretamente conectado a uma das portas no meu RB2011UiAS-IN. 

Configuração do Mikrotik

Aqui estão os passos que você pode seguir no lado do Mikrotik da equação IPS.
1. Transmitir pacotes para a plataforma IDS (CuBox no meu caso):
/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 Eu filtro um endereço remoto específico (neste exemplo de 1.2.3.4), pois esse tráfego é um túnel IP site a site e realmente não precisa ser verificado pelo IDS.
2. Configure regras de firewall para bloquear o tráfego de entrada e saída com base em uma lista de endereços:
/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 Eu tenho a regra da cadeia de entrada como a primeira regra depois de qualquer regra padrão criada pelo Mikrotik. Semelhante à regra da cadeia para frente.
3. Ative a interface da API:
/ip service set api address=192.168.3.0/24 enabled=yes A API será usada para gravar a lista de endereços de volta ao Mikrotik. Temos certeza de que só é acessível a partir da rede interna. 

Configuração da plataforma do IDS, modificações do MySQL

** Se você não quer mexer com o MySQL e o barnyard2, pule para a seção intitulada fast2mikrotik.php ** 

Os alertas são movidos do formato Unified2 para um banco de dados MySQL usando o barnyard2. Há muitos exemplos na Web sobre como fazer isso. O esquema que está sendo usado para o banco de dados é o esquema padrão snort / snorby. O esquema está incluído aqui para aqueles que querem criá-lo manualmente, em vez de usar o método snort / snorby. -- 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;Novas tabelas

Na minha situação, não quero que todos os alertas acionem um IPS. Eu observei meu tráfego ao longo do tempo e a grande maioria dos eventos vem de um pequeno conjunto de assinaturas de alerta. Então, a primeira coisa que faremos é configurar uma tabela (sigs_to_block) no MySQL para indicar quais categorias de assinatura de alerta queremos para acionar um IPS.
Código: selecione todos
--
-- 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));
A coluna "sig_name" contém a parte da assinatura que será usada para corresponder aos alertas que queremos bloquear. A coluna "src_or_dst" contém se estamos bloqueando um endereço IP de origem ou um endereço IP de destino com este alerta. E, finalmente, a coluna "tempo limite" indica por quanto tempo bloquearemos esse endereço IP após um evento. 

Aqui estão as assinaturas de alerta que preenchi a tabela “sigs_to_block” com:
Código: selecione todos
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');
A próxima tabela que vamos adicionar é uma tabela de fila, que coleta os alertas relevantes (eventos) que serão usados para criar os gatilhos IPS enviados para o Mikrotik.
Código: selecione todos
--
-- 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;
Trigger do banco de dados

A última parte que liga tudo isso e faz com que funcione, é um disparador do MySQL que preenche a tabela block_queue quando ocorre um alerta relevante. Este é um gatilho após a inserção na tabela de iphdr.
Código: selecione todos
CREATE TRIGGER `after_iphdr_insert` AFTER INSERT ON `iphdr`
  FOR EACH ROW BEGIN
  DECLARE this_event INT(11) default 0;
  DECLARE this_event_signature INT(10) default 0;
  DECLARE this_event_timestamp TIMESTAMP;
  DECLARE this_sig INT(10) default 0;
  DECLARE this_sig_name VARCHAR(256) default "";
  DECLARE this_sig_gid INT(10) default 0;
  DECLARE timeout VARCHAR(12) default "";
  DECLARE interested INT default 0;
  DECLARE direction VARCHAR(3) default "";
  DECLARE ip_src VARCHAR(64) default "";
  DECLARE ip_dst VARCHAR(64) default "";
  SELECT event.id, event.signature, event.timestamp
  INTO this_event, this_event_signature, this_event_timestamp
  FROM event
  WHERE event.sid = NEW.sid and event.cid = NEW.cid;  
  SELECT signature.sig_sid, signature.sig_gid, signature.sig_name 
  INTO this_sig, this_sig_gid, this_sig_name
  FROM signature
  WHERE signature.sig_id = this_event_signature;
  SELECT count(*), sigs_to_block.src_or_dst, sigs_to_block.timeout
  INTO interested, direction, timeout
  FROM sigs_to_block
  WHERE this_sig_name LIKE CONCAT(sigs_to_block.sig_name, '%');
  IF (interested > 0) THEN
   IF (direction = "src") THEN
      INSERT INTO block_queue
    SET que_ip_adr = inet_ntoa(NEW.ip_src),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    ELSE
      INSERT INTO block_queue
    SET que_ip_adr = inet_ntoa(NEW.ip_dst),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    END IF;
  END IF;
END
suricata_block.php

O último componente é um script PHP. Tem 2 funções:
Se o roteador foi inicializado há menos de 5 minutos, recrie a lista de endereços bloqueados que ainda estão ativos
Observe novos registros na tabela "block_queue" e adicione novas entradas à lista de endereços bloqueados. Quando um novo registro é adicionado, ele usa a API PHP do Mikrotik para criar um novo endereço na lista de endereços “Bloqueados”.
Não se esqueça de modificar as variáveis de configuração no início do script. Observe que você pode ter esse script enviando e-mails quando um endereço é bloqueado. Os emails serão assim:
email.png<?php
require('routeros_api.class.php');

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

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

$API = new RouterosAPI();

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

function AddToFirewall($thisrow) {

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

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

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

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

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

/* Main program loop */
    while ( 1 == 1 ) {
      $SQL = "SELECT * FROM block_queue WHERE que_processed = 0;";
      if(!$result = $db->query($SQL)) {
        die('There was an error running the query [' . $db->error . ']');
      }
      while($row = $result->fetch_assoc()) {
        $x = AddToFirewall($row);
        $SQL2 = "UPDATE block_queue set que_processed = 1 WHERE que_id = " . $row['que_id'] . ";";
        if (!$result2 = $db->query($SQL2)) {
          die('There was an error running the query [' . $db->error . ']');
        }
        mysqli_free_result($result2);
      }
      mysqli_free_result($result);
      sleep(5); /* Sleep 5 seconds then do again */
      mysqli_ping($db);
    }
  $db->close();
?> Você pode executar este código com o comando php –f /usr/local/bin/suricata_block.php. Eu criei um script /etc/init.d para que ele possa ser iniciado na inicialização do sistema e gerenciado com o comando service. 

O script php a seguir é útil para ser executado por uma entrada crontab a cada 10 minutos. Ele ativa o sniffing de pacotes no Mikrotik, o que é útil se o Mikrotik for reinicializado quando o estado padrão da ferramenta sniffer de pacotes for interrompido.
Código: selecione todos
<?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();
?>
O resultado

Aqui está uma captura de tela do Winbox que mostra a lista de endereços com os endereços gerados como resultado dos alertas do Suricata.
Bloco List.jpgfast2mikrotik.php

Se você quiser ir a uma rota rápida e simples para criar regras de firewall, eu criei o seguinte script PHP, fast2mikrotik.php que usará o fast.log criado pela suricata. Este script PHP usa a biblioteca inotify, então você terá que adicionar isso à sua implementação PHP. Para fazer isso:
Código: selecione todos
$ apt-get install php-pear php-dev
$ pecl install inotify
Em seguida, modifique o arquivo php.ini para incluir o comando de configuração "extension = inotify.so". No meu caso, o php.ini estava localizado em /etc/php/7.0/cli. 

Não se esqueça de modificar as variáveis de configuração no início do script fast2mikrotik.php. Observe que você pode ter esse script enviando e-mails quando um endereço é bloqueado. 

Este script irá bloquear todos os eventos sinalizados pelo suricata e pelo período de tempo especificado na variável $ block_time. 

Você pode executar este código com o comando php –f /usr/local/bin/fast2mikrotik.php. Eu criei um script /etc/init.d para que ele possa ser iniciado na inicialização do sistema e gerenciado com o comando service.
Código: selecione todos
<?php
require('routeros_api.class.php');

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

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

$API = new RouterosAPI();

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

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

function AddToFirewall($thisalert, $srcdst) {

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

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

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

Uma vez que você tenha o Suricata enviando mensagens para o Mikrotik com os componentes acima, adicionar o OSSEC IPS é relativamente fácil. Vamos aproveitar a tabela MySQL block_queue e o processo suricata_block. Você terá que configurar o host executando o suricata como o OSSEC Manager (Server). 

Para que o OSSEC envie requisições IPS para o Mikrotik, precisamos ativar o active_responses no OSSEC. No arquivo /var/ossec/etc/ossec.conf (seu caminho pode variar um pouco) adicione as seguintes linhas na seção <ossec_config>:
Código: selecione todos
<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>
Certifique-se de verificar se existe outra seção <active_repsonse> no arquivo ossec.conf. Se sim, comente. 

A diretiva <command> define um comando bloco-mikrotik que aponta para um shell script que está localizado em / var / ossec / active-response / bin. Aqui está o script bash do mikrotik-fw.sh: #!/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 Certifique-se de que este script seja executável (chmod a + x). 

Por fim, você desejará substituir quaisquer regras para as quais deseja enviar uma resposta ativa em /var/ossec/rules/local_rules.xml. Aqui está um conjunto de regras que defini para alguns eventos específicos que o OSSEC dispara:
Código: selecione todos
  <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>
Isso é tudo o que há para fazer para que seus alertas OSSEC sejam acionados em seu Mikrotik.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Seu conteúdo precisará ser aprovado por um moderador

Visitante
Você está comentando como visitante. Se você tem uma conta, por favor, entre.
Responder

×   Você colou conteúdo com formatação.   Remover formatação

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Entre para seguir isso  

×