powered by simpleCommunicator - 21.11.28     © 2024 Programmizd 02
Map
Форумы / Вопросы по IT / Mikrotik - сбор логов в БД под Windows
6 сообщений из 6, страница 1 из 1
Mikrotik - сбор логов в БД под Windows
    #1963414
f..h
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Подумал, что неплохо был собирать логи в БД, но не нашел внятного простого решения под Виндовс. Наверное, плохо искал. По сути, задача тривиальная - получить UDP пакет и отправить его содержимое в БД. Решил, что проще написать, чем искать что-то готовое.

Сделано на Borland Turbo Delphi Explorer (тот самый, который распространялся бесплатно), BDS 2006. Если кто хочет, может взять исходник и перекомпилировать в чем-то современном.

Протестировано на Windows Server 2003 x64, Windows 7 SP1 x64.

Инструкция для пользователей.
Распаковываем содержимое архива куда-нибудь, устанавливаем службу
mtklggr.exe -install
При необходимости в управлении службами меняем пользователя для запуска службы, следим, чтобы у этого пользователя были права на запись в каталог с экзешником (туда логи с ошибками будут сохраняться).
Создаем ODBC источник к БД, в которой собираемся хранить логи Микротика. Важно! Источник в 64-битной системе надо делать 32-битный, поскольку прога 32-битная. Для этого надо запускать оснастку, находящуюся по следующему пути,
C:\WINDOWS\SysWOW64\odbcad32.exe
а не ту, которая в панели управления.

Правим инишник:
[mtk] BindingIP=192.168.0.2 - интерфейс, на котором служба будет слушать Микротик BindingPort=514 - порт, который будет слушать. 514 может быть занят системой, тогда забиваем свой.
Эти два пункта надо забить в настройках логгинга Микротика
[sql] CSPattern=Provider=MSDASQL;Password=%s;Persist Security Info=True;User ID=%s;Data Source=%s
Предполагаю, что шаблон строки подключения к БД править не надо. Если все-таки придется, соблюдаем последовательность подстановок: пароль, логин, источник.
DataSource=mtk_sql10_32 UserID=mtku Password=mtku
Тут все понятно.
Pattern=exec put_string @item = '%s'
Пишем наш запрос на вставку данных. Хотим - insert, хотим - через процедуру, так грамотней.
[sys] HaltOnErrors=1
Если ошибки на стадии запуска службы, нефиг ей оставаться запущенной. Если на стадии обработки данных, она засрет диск логами. Ошибки читаем в файлике *.log в каталоге проги.

Экзешник
mtklog - pub - exe.zip
Сорцы
mtklog - pub - src.zip
...
Рейтинг: 0 / 0
Период между сообщениями больше года.
Mikrotik - сбор логов в БД под Windows
    #2939908
f..h
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Возникла потребность более гибко оперировать IP адресами в базе данных, пришлось дописать проект.

Новое:
  • Адреса из лога теперь хранятся в отдельных полях в формате varbinay и индексируются. Это позволяет делать шустрые запросы по диапазону адресов.
  • У хранимки, которая пишет лог, параметры тоже varbinary, пришлось отказаться от паттерна sql запроса. Как сервис будет работать с не MS SQL базой, не знаю. Кто пожелает разобраться, может скачать исходники и разобраться самостоятельно. В любом случае, скачать и установить MS SQL Express недолго.
  • В логе есть индексируемое поле fword, о нем ниже.
Сервис:

Все по-старому, как в первом посте. Распаковываем в каталог, правим ini, устанавливаем в систему как сервис. Так же и с ODBC соединением.

БД:

Создаем руками базу данных mtk2, скачиваем скрипт, запускаем, он делает все что надо. Если пользователь mtku на сервере есть, скрипт придется подправить.

Исходники:

Для быстрого парсинга строк был использован проект CachedTexts. Дефекатор считает его глючным говном Многие считают его сомнительным, но в данном случае после доработки напильником он отрабатывает что должен. В архиве немного подправленные исходники CachedTexts, пришлось закомментировать объявление типа TBytes, дописать в uses IDGlobals, закомментить еще пару строк, с которыми модуль не хотел компилироваться.
Не забываем, что это BDS 2006. При конвертации в что-то современное учитываем, что строку лога от Микротика мы получаем однобайтную, так что всюду AnsiString и TByteString.AssingUTF8, а не просто Assign.
А, прошлый раз забыл сказать, что LogFileUnit я нашел где-то в инете, автора не знаю, но спасибо ему за работу.

Как это работает:

fword. Это первое слово после слова MikroTik. Может быть одним из этого списка:
<pptp-username> ether8 ether9 filter first input: nat pppoe-out1: proxy: respond username TCP the user
Это то, что нам не нужно. Но если мы в Микротике в правиле файрвола указываем префикс, то этот префикс попадет в это поле и по нему можно будет делать отбор.

Читать и указывать айпишники в запросах придется через функции dbo.IP2Bin и dbo.Bin2IP. Кроме этих функций я набросал несколько процедурок, которые облегчат жизнь на первое время. Если понадобится что-то эдакое, посмотрите, как они написаны и сделаете свое. У меня сейчас открыт вот такой файлик, если мне что-то надо посмотреть, правлю параметры руками, выделаю нужную строчку и жму F5. Дата используется с условием "позже".
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
exec GetLogBySrcAndDate '192.168.0.5', '2021-04-13 08:01:40.830'

exec GetLogByWordAndDate 'check_97', '2021-04-13 16:56:03.903'

exec GetSrcAddressesByWordAndDate 'akamai', '2021-04-13 17:01:40.830'

exec GetDstAddressesByWordAndDate 'akamai', '2021-04-12 16:01:40.830'

exec GetDstAddressesBySrcAndDate '192.168.0.7', '2021-04-13 15:01:40.830'

exec GetSrcAddressesByDstRangeAndDate '104.64.0.0', '104.127.255.255', '2021-04-01 13:18:53.433'

exec GetSrcAddressesByDstAndDate '104.73.92.241', '2021-04-01 13:18:53.433'

exec GetLogByDate '2021-04-14 10:58:09.617'

exec GetBinSrcAddressesByDstRangeAndDate '104.64.0.0', '104.127.255.255', '2021-04-01 13:18:53.433'

select distinct fword from [log]
----------------------------------------------------------------------------------
select distinct ip2 into #t1 from [log] where ip2 between dbo.IP2Bin('104.64.0.0') and dbo.IP2Bin('104.127.255.255')
select dbo.Bin2IP(ip2) from #t1 order by ip2
drop table #t1
----------------------------------------------------------------------------------
Адресов в записи может быть от 0 до 5. Чтобы узнать их количество, можно использовать поле ipnum. Номеров портов после адреса иногда может не быть. Как правило, если речь идет о форварде, то первый айпишник - сорс, второй - дестинэйшн.

Дату МикроТика в сервисе я не парсю, пользуюсь датой MS SQL сервера.
mtklog2 - pub - base.zip
mtklog2 - pub - exe.zip
...
Если бы делать было бы так же легко, как знать, что надо делать...
Рейтинг: 0 / 0
Mikrotik - сбор логов в БД под Windows
    #2939911
f..h
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Сорцы к Деде на ноутбук не влезают. Но мы впихнем!
mtklog2 - pub - src.zip.001
mtklog2 - pub - src.zip.002
mtklog2 - pub - src.zip.003
...
Если бы делать было бы так же легко, как знать, что надо делать...
Рейтинг: 0 / 0
Mikrotik - сбор логов в БД под Windows
    #2939950
f..h
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Благодарю Дефекатора, Попыхтета и Сандаля за помощь.
...
Если бы делать было бы так же легко, как знать, что надо делать...
Рейтинг: 0 / 0
Mikrotik - сбор логов в БД под Windows
    #2940332
WildMage
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
f..h  14.04.2021, 14:18
Возникла потребность более гибко оперировать IP адресами в базе данных, пришлось дописать проект.

Новое:
  • Адреса из лога теперь хранятся в отдельных полях в формате varbinay и индексируются. Это позволяет делать шустрые запросы по диапазону адресов.
  • У хранимки, которая пишет лог, параметры тоже varbinary, пришлось отказаться от паттерна sql запроса. Как сервис будет работать с не MS SQL базой, не знаю. Кто пожелает разобраться, может скачать исходники и разобраться самостоятельно. В любом случае, скачать и установить MS SQL Express недолго.
  • В логе есть индексируемое поле fword, о нем ниже.
Сервис:

Все по-старому, как в первом посте. Распаковываем в каталог, правим ini, устанавливаем в систему как сервис. Так же и с ODBC соединением.

БД:

Создаем руками базу данных mtk2, скачиваем скрипт, запускаем, он делает все что надо. Если пользователь mtku на сервере есть, скрипт придется подправить.

Исходники:

Для быстрого парсинга строк был использован проект CachedTexts. Дефекатор считает его глючным говном Многие считают его сомнительным, но в данном случае после доработки напильником он отрабатывает что должен. В архиве немного подправленные исходники CachedTexts, пришлось закомментировать объявление типа TBytes, дописать в uses IDGlobals, закомментить еще пару строк, с которыми модуль не хотел компилироваться.
Не забываем, что это BDS 2006. При конвертации в что-то современное учитываем, что строку лога от Микротика мы получаем однобайтную, так что всюду AnsiString и TByteString.AssingUTF8, а не просто Assign.
А, прошлый раз забыл сказать, что LogFileUnit я нашел где-то в инете, автора не знаю, но спасибо ему за работу.

Как это работает:

fword. Это первое слово после слова MikroTik. Может быть одним из этого списка:
<pptp-username> ether8 ether9 filter first input: nat pppoe-out1: proxy: respond username TCP the user
Это то, что нам не нужно. Но если мы в Микротике в правиле файрвола указываем префикс, то этот префикс попадет в это поле и по нему можно будет делать отбор.

Читать и указывать айпишники в запросах придется через функции dbo.IP2Bin и dbo.Bin2IP. Кроме этих функций я набросал несколько процедурок, которые облегчат жизнь на первое время. Если понадобится что-то эдакое, посмотрите, как они написаны и сделаете свое. У меня сейчас открыт вот такой файлик, если мне что-то надо посмотреть, правлю параметры руками, выделаю нужную строчку и жму F5. Дата используется с условием "позже".
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
exec GetLogBySrcAndDate '192.168.0.5', '2021-04-13 08:01:40.830'

exec GetLogByWordAndDate 'check_97', '2021-04-13 16:56:03.903'

exec GetSrcAddressesByWordAndDate 'akamai', '2021-04-13 17:01:40.830'

exec GetDstAddressesByWordAndDate 'akamai', '2021-04-12 16:01:40.830'

exec GetDstAddressesBySrcAndDate '192.168.0.7', '2021-04-13 15:01:40.830'

exec GetSrcAddressesByDstRangeAndDate '104.64.0.0', '104.127.255.255', '2021-04-01 13:18:53.433'

exec GetSrcAddressesByDstAndDate '104.73.92.241', '2021-04-01 13:18:53.433'

exec GetLogByDate '2021-04-14 10:58:09.617'

exec GetBinSrcAddressesByDstRangeAndDate '104.64.0.0', '104.127.255.255', '2021-04-01 13:18:53.433'

select distinct fword from [log]
----------------------------------------------------------------------------------
select distinct ip2 into #t1 from [log] where ip2 between dbo.IP2Bin('104.64.0.0') and dbo.IP2Bin('104.127.255.255')
select dbo.Bin2IP(ip2) from #t1 order by ip2
drop table #t1
----------------------------------------------------------------------------------
Адресов в записи может быть от 0 до 5. Чтобы узнать их количество, можно использовать поле ipnum. Номеров портов после адреса иногда может не быть. Как правило, если речь идет о форварде, то первый айпишник - сорс, второй - дестинэйшн.

Дату МикроТика в сервисе я не парсю, пользуюсь датой MS SQL сервера.
mtklog2 - pub - base.zip
mtklog2 - pub - exe.zip
Слушай, а если ты адреса хранишь 4 части в разных полях, то нахрена варбинари? Почему бы просто Инты не захерачить?
...
Рейтинг: 0 / 0
Mikrotik - сбор логов в БД под Windows
    #2941176
f..h
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
WildMage  14.04.2021, 16:46
f..h  14.04.2021, 14:18
Возникла потребность более гибко оперировать IP адресами в базе данных, пришлось дописать проект.

Новое:
  • Адреса из лога теперь хранятся в отдельных полях в формате varbinay и индексируются. Это позволяет делать шустрые запросы по диапазону адресов.
  • У хранимки, которая пишет лог, параметры тоже varbinary, пришлось отказаться от паттерна sql запроса. Как сервис будет работать с не MS SQL базой, не знаю. Кто пожелает разобраться, может скачать исходники и разобраться самостоятельно. В любом случае, скачать и установить MS SQL Express недолго.
  • В логе есть индексируемое поле fword, о нем ниже.
Сервис:

Все по-старому, как в первом посте. Распаковываем в каталог, правим ini, устанавливаем в систему как сервис. Так же и с ODBC соединением.

БД:

Создаем руками базу данных mtk2, скачиваем скрипт, запускаем, он делает все что надо. Если пользователь mtku на сервере есть, скрипт придется подправить.

Исходники:

Для быстрого парсинга строк был использован проект CachedTexts. Дефекатор считает его глючным говном Многие считают его сомнительным, но в данном случае после доработки напильником он отрабатывает что должен. В архиве немного подправленные исходники CachedTexts, пришлось закомментировать объявление типа TBytes, дописать в uses IDGlobals, закомментить еще пару строк, с которыми модуль не хотел компилироваться.
Не забываем, что это BDS 2006. При конвертации в что-то современное учитываем, что строку лога от Микротика мы получаем однобайтную, так что всюду AnsiString и TByteString.AssingUTF8, а не просто Assign.
А, прошлый раз забыл сказать, что LogFileUnit я нашел где-то в инете, автора не знаю, но спасибо ему за работу.

Как это работает:

fword. Это первое слово после слова MikroTik. Может быть одним из этого списка:
<pptp-username> ether8 ether9 filter first input: nat pppoe-out1: proxy: respond username TCP the user
Это то, что нам не нужно. Но если мы в Микротике в правиле файрвола указываем префикс, то этот префикс попадет в это поле и по нему можно будет делать отбор.

Читать и указывать айпишники в запросах придется через функции dbo.IP2Bin и dbo.Bin2IP. Кроме этих функций я набросал несколько процедурок, которые облегчат жизнь на первое время. Если понадобится что-то эдакое, посмотрите, как они написаны и сделаете свое. У меня сейчас открыт вот такой файлик, если мне что-то надо посмотреть, правлю параметры руками, выделаю нужную строчку и жму F5. Дата используется с условием "позже".
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
exec GetLogBySrcAndDate '192.168.0.5', '2021-04-13 08:01:40.830'

exec GetLogByWordAndDate 'check_97', '2021-04-13 16:56:03.903'

exec GetSrcAddressesByWordAndDate 'akamai', '2021-04-13 17:01:40.830'

exec GetDstAddressesByWordAndDate 'akamai', '2021-04-12 16:01:40.830'

exec GetDstAddressesBySrcAndDate '192.168.0.7', '2021-04-13 15:01:40.830'

exec GetSrcAddressesByDstRangeAndDate '104.64.0.0', '104.127.255.255', '2021-04-01 13:18:53.433'

exec GetSrcAddressesByDstAndDate '104.73.92.241', '2021-04-01 13:18:53.433'

exec GetLogByDate '2021-04-14 10:58:09.617'

exec GetBinSrcAddressesByDstRangeAndDate '104.64.0.0', '104.127.255.255', '2021-04-01 13:18:53.433'

select distinct fword from [log]
----------------------------------------------------------------------------------
select distinct ip2 into #t1 from [log] where ip2 between dbo.IP2Bin('104.64.0.0') and dbo.IP2Bin('104.127.255.255')
select dbo.Bin2IP(ip2) from #t1 order by ip2
drop table #t1
----------------------------------------------------------------------------------
Адресов в записи может быть от 0 до 5. Чтобы узнать их количество, можно использовать поле ipnum. Номеров портов после адреса иногда может не быть. Как правило, если речь идет о форварде, то первый айпишник - сорс, второй - дестинэйшн.

Дату МикроТика в сервисе я не парсю, пользуюсь датой MS SQL сервера.
mtklog2 - pub - base.zip
mtklog2 - pub - exe.zip
Слушай, а если ты адреса хранишь 4 части в разных полях, то нахрена варбинари? Почему бы просто Инты не захерачить?
Да не, один адрес в одном поле. Обсуждалось в топике у Пыхтета. Я сперва тоже думал, что придется с Интами мудрить, но Сандаль меня вовремя остановил.
...
Если бы делать было бы так же легко, как знать, что надо делать...
Рейтинг: 0 / 0
6 сообщений из 6, страница 1 из 1
Форумы / Вопросы по IT / Mikrotik - сбор логов в БД под Windows
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (1): Анонимы (1)
Читали форум (3): Анонимы (1), Yandex Bot 7 мин., Bing Bot 8 мин.
Пользователи онлайн (5): Анонимы (3), Bing Bot 2 мин., Yandex Bot 7 мин.
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]