Загрузка логов журнала регистрации 1С из MS SQL в ElasticSearch

Опубликовал Амир Фарукшин (farukshin) в раздел Администрирование - Журнал регистрации

Еще один инструмент хранения и визуализации логов журнала регистрации 1С

В данной статье покажу еще один инструмент хранения и визуализации записей журнала регистрации 1С.

Входная задача: Небольшая конфигурация на 1С Предприятие 8.2. Максимальная суточная активность - 150 чел. Информационная база генерирует ~ 0,5 млн записей логов в рабочие дни. Логи необходимо хранить в сторонней БД MS SQL. Также необходим гибкий инструмент хранения и визуализации логов, как текущего так и  прошлого периода (3-5 лет). Использовать стек ELK (Elastic + Logstash + Kibana).

Решение: Будет реализована следующая инфраструктура:

Последовательность действий:

1. Запись журнала регистрации 1С в базу MS SQL;

2. Отправка логов 1С из MS SQL в ElastisSearch;

3. Визуализация логов 1С в Kibana.

Реализация.

Запись журнала регистрации 1С в базу MS SQL

Механизм реализовал Алексей Бочков, описание и исходники в статье //infostart.ru/public/182820/

Для того, чтобы Logstash понимал, какие записи уже были отправлены в Elastic, а какие появились с момента последнего запуска - необходимо в таблице Events создать инкрементное поле id_log типа bigint (+ 8 байт на запись).  Для этого в MS SQL Server Management Studio достаточно выполнить запрос:

USE [log1c]
ALTER TABLE [log1c].[dbo].[Events] ADD id_log bigint identity

где log1c - имя базы данных с логами.

В итоге, имеем базу данных MS SQL с актуальными логами информационной базы 1С, которые периодически  считываются из файла ЖР. 

Отправка логов 1С из MS SQL в ElastisSearch

На том, как устанавливать стек ELK (Elastic + Logstash + Kibana) останавливаться не буду, т.к. в Интернет есть много подробной информации. Например, установка на Windows, установка на Linux.

ПО на сервера установлено, переходим к настройке. Для загрузки из базы MS SQL в Logstash будем использовать jdbc-input-plugin

На сервере Logstash внесем изменения в конфигурационный файл 

input {
  jdbc {
    jdbc_driver_library => "C:\logstash-2.3.4\jdbc2\sqljdbc_4.2\rus\sqljdbc41.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://10.4.2.147\MSSQLSERVER:1433;DatabaseName=log1c"
    jdbc_user => "logstash"
    jdbc_password => "Qwerty123"
    jdbc_fetch_size => "100000"
    #clean_run => true
    last_run_metadata_path => "C:\logstash-2.3.4\last_run\last_run"
    statement_filepath => "C:\logstash-2.3.4\bin\SQLQuery.sql"
    schedule => "* * * * *"
    tracking_column => id_log
    use_column_value => true
  }
}

filter {
  date{
    target => "datetime"
    match => ["datetime", "YYYY-MM-dd HH:mm:ss.SSS", "MMM dd YYYY HH:mm:ss.SSS", "ISO8601"]    
  }
}

output { 
  elasticsearch {
    hosts => "10.4.2.90:9200"
    index => "log1c-%{index_elastic}"
  }
}

Разберем настройки подробнее. 

jdbc_driver_library - путь к библиотеке jdbc драйвера, скачиваем с официального сайта

jdbc_driver_class  - класс jdbc драйвера. Для MS SQL это "com.microsoft.sqlserver.jdbc.SQLServerDriver"

jdbc_connection_string => "jdbc:sqlserver://10.4.2.147\MSSQLSERVER:1433;DatabaseName=log1c" - 10.4.2.147 - IP адрес сервера MSSQL, MSSQLSERVER - Instance Name заданный при установке, 1433 - порт, log1c - БД с логами.

jdbc_user, jdbc_password - логин/пароли для доступа к БД

jdbc_fetch_size - размер выборки из БД

tracking_column - имя колонки, значение которой будет отслеживаться (то самое инкрементное поле id_log)

last_run_metadata_path - путь к файлу, где храниться значение отслеживаемой колонки

use_column_value => true - включаем использование отслеживаемого столбца (на запрос будет добавляться условие Where, в параметр sql_last_value будет вставлено значение из файла last_run_metadata_path)

statement_filepath => "C:\logstash-2.3.4\bin\SQLQuery.sql" - путь к файлу с запросом SQL

schedule => "* * * * *" - расписание, периодического выполнения запроса к БД, в формате Cron.

Текст запроса в файле "C:\logstash-2.3.4\bin\SQLQuery.sql"

SELECT 
	  e.*
	  ,i.[Name] AS "InfobaseName"
	  ,a.[Name] AS "AppName_str"
	  ,c.[Name] AS "ComputerName_str"
	  ,et.[Name] AS "EventName"
	  ,mp.[Name] AS "MainPorts"
	  ,sp.[Name] AS "SecondPortsName"
	  ,md.[Name] AS "MetadataName"
	  ,md.[Guid] AS "MetadataGUID"
	  ,s.[Name] AS "ServerName"
	  ,u.[Name] AS "UserName_str"
	  ,u.[Guid] AS "UserGUID"
	  ,FORMAT(e.[datetime], 'yyyy-MM', 'en-US') as 'index_elastic'
FROM [log1c].[dbo].[Events] e
  left join [log1c].[dbo].[Infobases] i		ON (e.[InfobaseCode] = i.[Code])
  left join [log1c].[dbo].[Applications] a	ON (e.[InfobaseCode] = a.[InfobaseCode]		AND e.[AppName] = a.[Code])
  left join [log1c].[dbo].[Computers] c		ON (e.[InfobaseCode] = c.[InfobaseCode]		AND e.[ComputerName] = c.[Code])
  left join [log1c].[dbo].[EventsType] et	ON (e.[InfobaseCode] = et.[InfobaseCode]	AND e.[EventID] = et.[Code])
  left join [log1c].[dbo].[MainPorts] mp	ON (e.[InfobaseCode] = mp.[InfobaseCode]	AND e.[MainPortID] = mp.[Code])
  left join [log1c].[dbo].[SecondPorts] sp	ON (e.[InfobaseCode] = sp.[InfobaseCode]	AND e.[SecondPortID] = sp.[Code])
  left join [log1c].[dbo].[Metadata] md		ON (e.[InfobaseCode] = md.[InfobaseCode]	AND e.[MetadataID] = md.[Code])
  left join [log1c].[dbo].[Servers] s		ON	(e.[InfobaseCode] = s.[InfobaseCode]	AND e.[ServerID] = s.[Code])
  left join [log1c].[dbo].[Users] u		ON (e.[InfobaseCode] = u.[InfobaseCode]		AND e.[UserName] = u.[Code])
WHERE 
  e.id_log > :sql_last_value

Итого, при первом запуске Logstash в Elastic выгрузятся все записи из БД log1c (sql_last_value=0), при последующем запуске будут считываться только вновь добавленные записи (последнее считанное значение id_log будет храниться в файле last_run_metadata_path и передаваться в качестве переменной через sql_last_value)

Визуализация логов 1С в Kibana

Если ElasticSearch и Kibana установлены на одном сервере, то дефолтных настроек будет достаточно. В нашем случае - на разных серверах, поэтому необходимо внести правки в конфигурационные файлы.
Для ElasticSearch в файл elasticsearch.yml

network.host: ["127.0.0.1", "10.4.2.90"]
network.port: 9200
discovery.zen.ping.unicast.host: [IP_адреса]

Для Kibana - файл  kibana.yml

server.port: 5601
server.host: "10.4.2.91"
elasticsearch.url: "10.4.2.90:9200"

Самая приятная часть - анализ и визуализация логов. Вводим в браузере "10.4.2.91:5601", на вкладке Settings создаем новый шаблон индекса:

На вкладке Discover появятся все логи журнала регистрации 1С:

Запрос на сервер Elastic для отбора всех ошибок ЖР:

{
  "query": {
    "match": {
      "eventtype": {
        "query": "E",
        "type": "phrase"
      }
    }
  }
}

Все поиски и визуализации можно вывести на dashboard, задать период анализа логов (например за последний час) и частоту обновления из Elastic.

Возможности резервного копирования и восстановления в ElasticSearch реализованы очень интересно. Управление через HTTP запросы в формате JSON.
Сначала, необходимо создать репозиторий.

curl -XPUT 'http://l0.4.2.90:9200/_snapshot/backup1' -d '{
"type": "fs",
"settings": {
"location": "/elastic-backup",
"compress": true
}
}'

где type - тип хранилища, куда будут складываться бэкапы. Для дефолтной установки доступна только файловая система. С дополнительными плагинами можно реализовать хранение на Azure Cloud, HDFS и AWS S3.
location - путь хранения
compress - сжимать ли бэкапы, по умолчанию true.

Репозиторий создан, можно бэкариповать - создадим snapshot1 в репозитории backup1:

curl -XPUT "10.4.2.90:9200/_snapshot/backup1/snapshot1?wait_for_completion=true"

Для восстановления данных:

curl -XPOST "10.4.2.90:9200/_snapshot/backup1/snapshot1/_restore"

Посмотреть текущие индексы:

curl -XGET "10.4.2.90:9200/_cat/indices/?pretty"

Удалить индекс "log1c-2014-02" - все логи за февраль 2014 г.:

curl -XDELETE "10.4.2.90:9200/log1c-2014-02/?pretty"

Более полный список команд по работе с ElasticSearch - тема для отдельной статьи.

Замечания:
1) В данной статье не рассматриваются вопросы безопасности/разграничения доступа к ElasticSearch/Kibana, настройка сертификатов, кластеризация и масштабирование ElasticSearch, т.к. это не входило в изначальную задачу.
2) Выбранная архитектура миграции логов 1С -> MS SQL -> ELK обусловлена только лишь поставленной задачей. В Production логи не будут храниться в БД и ElasticSearch, дублирую друг друга.
3) Размер БД в 500 ГБ уже требует большого количества ресурсов и операций чтения с диска при выполнении запроса от Logstash к MS SQL. Можно было бы на MS SQL создать архивную БД, в которую выгружать прочитанные Logstash'ом данные. Тогда таблица, в которую пишутся данные из 1С и читаются в Logstash, будет иметь небольшой объем (буфер).

Итоги по задаче. В ходе выполнения задачи, была достигнута главная цель статьи - демонстрация механизма получения и анализа данных в ElasticSearch из БД MS SQL.

Спасибо.

P.S. Возможно, вам будет интересна предыдущая статья Мониторинг количества сеансов 1С на базе PRTG

См. также

Комментарии
1. Максим Кузнецов (Makushimo) 149 06.09.16 09:49 Сейчас в теме
Тема интересная, но акцент сделан на тех. составляющую.
А вот с представлением темы подкачал.
поймал себя на мысли, а зачем читать так много букав неонятно о чем.

Что это? Зачем это?

Допустим, я не в теме ни разу, но [возможно] это будет полезным для меня или моего работодателя. А в статье нет общего введения. Демонстрации того, что это решение дает. без технических подробностей, Чтобы войти в контекст.

То есть автор отправит меня в гугел, мол иди изучай, ведь это ТЕБЕ надо... А я вот тут технические моменты выложу. Кому надо тот поймет.

И на втором третьем абзаце сливаю эту статью фтопку.

Автор, зачем ты это выкладываешь. похвастаться?

Люди в теме и так это знают, а те кто не в теме идут лесом в гугел.
kalyaka; DenisF8; Новиков; +3 Ответить 1
2. Ivan Khorkov (vano-ekt) 854 06.09.16 17:37 Сейчас в теме
3. Алексей Лустин (lustin) 817 07.09.16 17:31 Сейчас в теме
4. pahalovo (pahalovo) 08.09.16 02:33 Сейчас в теме
(3) Есть ли beats для sqlite логов?
5. Евгений Мартыненков (JohnyDeath) 290 09.12.16 06:49 Сейчас в теме
(0) Без промежуточной MSSQL базы не делали? На основании, например, Beats, который упомянут в (3)
6. Амир Фарукшин (farukshin) 55 09.12.16 20:51 Сейчас в теме
(5) Евгений, без MSSQL базы не делал, только в планах.
Оставьте свое сообщение