Как поместить логи sql в журнал application

Системы управления базами данных

Получение событий с Microsoft SQL Server возможно реализовать двумя способами:

• через события Windows events;
• через ODBC коллектор.

Настройка получения событий через windows events.

Включение аудита MS SQL Server:

1. Запустите Microsoft SQL Server Management Studio.
2. В окне подключения к базе данных укажите название экземпляра и введите учетные данные (см. рисунок 1). Рисунок 1 — Подключение к базе данных
3. В панели Object explorer перейдите во вкладку Security → Audits. По правому щелчку мыши выберите опцию New Audit. (см. рисунок 2). Рисунок 2 — Создание аудита
4. В открывшейся вкладке Create Audit укажите название аудита в поле Audit name. В качестве Audit destination выберите Application Log, нажмите ОК (см. рисунок 3). Рисунок 3 — Настройка аудита
5. В панели Object explorer перейдите во вкладку Security → Server Audit Specification. По правому щелчку мыши выберите опцию New Server Audit Specification. (см. рисунок 4). Рисунок 4 — Создание спецификации аудита
6. В открывшейся вкладке Create Server Audit Specification укажите название спецификации аудита в поле Name. В поле Audit выберите ранее созданный аудит из выпадающего списка. В поле Actions выберите типы событий для отслеживания, нажмите ОК (см. рисунок 5). Рисунок 5 — Настройка спецификации аудита

Создание учетной записи windows:

1. В панели управления Windows откройте консоль Computer Management (Управление компьютером).
2. В консоли откройте раздел System Tools (Служебные программы) → Local Users and Groups (Локальные пользователи и группы) → Users (Пользователи).
3. В контекстном меню раздела Users (Пользователи) выберите функцию New User (Новый пользователь) для создания нового пользователя (см. рисунок 6). Рисунок 6 — Создание пользователя
4. В открывшемся окне New User (Новый пользователь) введите следующие денные (см. рисунок 7):
   • В поле Name (Имя) ввести имя нового пользователя.
   • В поле Password (Пароль) установить пароль и подтвердить его в поле Confirm Password (Подтвердить).

При необходимости выставить настройки в пунктах:

• User cannot change password (Запретить смену пароля пользователем).
• Password never expires (Срок действия пароля неограничен).
• Для создания пользователя с заданными параметрами нажать кнопку Create.

Рисунок 7 — Настройка параметров пользователя

Предоставление пользователю прав доступа к журналу событий:

1. В консоли Computer Management (Управление компьютером) откройте раздел System Tools (Служебные программы) → Local Users and Groups (Локальные пользователи и группы) → Groups (Группы).
2. Выберите в списке группу Event Log Readers (Читатели журнала событий).
3. Откройте правой кнопкой мыши контекстное меню группы Event Log Readers (Читатели журнала событий) и выберите пункт Add To Group (Добавить в группу). Откроется окно Event Log Readers Properties (Свойства: Читатели журнала событий).
4. Для добавления пользователя в группу:
   • Нажать кнопку Add (Добавить).
   • В открывшемся окне Select Users (Выбор: Пользователи) выбрать в списке ранее созданного пользователя и добавить его в группу, нажав кнопку ОК.
5. Для сохранения введенных настроек в окне Event Log Readers Properties (Свойства: Читатели журнала событий) нажмите кнопку OK.

Добавление новой конфигурации в коллектор:

Передача событий в Платформу Радар осуществляется через eventlog_collector. Ниже приведены настройки с описанием для добавления в config.yaml:

eventlog_collector: &eventlog_collector id: "eventlog_collector" channel: ['Application'] query: "*[System[Provider[@Name='Имя экземпляра СУБД>']]]" batch_size: 31 timeout: 3 poll_interval: 1 read_from_last: false resolve_sid: false log_level: "INFO" worker_count: 1 remote: enabled: true user: "" password: "" domain: "." remote_servers: [""] auth_method: "Negotiate" encoding: change_to_utf8: true original_encoding: "cp1251" 

В качестве данных для подключения необходимо использовать созданную ранее учетную запись. В поле query мы указываем запрос для получения событий только от настраиваемого источника.

Настройка получения событий через odbc коллектор.

Включение аудита MS SQL Server:

1. Запустите Microsoft SQL Server Management Studio.
2. В окне подключения к базе данных укажите название экземпляра и введите учетные данные (см. рисунок 8). Рисунок 8 — Подключение к базе данных
3. В панели Object explorer перейдите во вкладку Security → Audits. По правому щелчку мыши выберите опцию New Audit. (см. рисунок 9). Рисунок 9 — Создание аудита
4. В открывшейся вкладке Create Audit укажите название аудита в поле Audit name. В качестве Audit destination выберите Application Log, нажмите ОК (см. рисунок 10). Рисунок 10 — Настройка аудита
5. В панели Object explorer перейдите во вкладку Security → Server Audit Specification. По правому щелчку мыши выберите опцию New Server Audit Specification. (см. рисунок 11). Рисунок 11 — Создание спецификации аудита
6. В открывшейся вкладке Create Server Audit Specification укажите название спецификации аудита в поле Name. В поле Audit выберите ранее созданный аудит из выпадающего списка. В поле Actions выберите типы событий для отслеживания, нажмите ОК (см. рисунок 12). Рисунок 12 — Настройка спецификации аудита

Установка ODBC драйвера:

1. С официального сайта скачайте ODBC Driver for SQL Server.
2. Установите скачанный драйвер на сервер с коллектором.

Добавление новой конфигурации в коллектор:

Передача событий в Платформу Радар осуществляется через odbc_collector. Ниже приведены настройки с описанием для добавления в config.yaml:

odbc_collector: &odbc_collector id: "odbc_collector" poll_interval: 5 read_from_last: true connection_string: "server=IP-адрес сервера с СУБД;port=1433;driver=;database=master;Encrypt=Optional;UID=;PWD=" sql: > SELECT CAST(DATEDIFF_BIG(ns, '1970-01-01 00:00:00.0000000', event_time) AS BIGINT) as epoch, event_time, action_id, succeeded, session_id, class_type, session_server_principal_name, server_principal_name, server_principal_sid, database_principal_name, target_server_principal_name, target_server_principal_sid, target_database_principal_name, server_instance_name, database_name, schema_name, object_name, statement, additional_information, transaction_id FROM fn_get_audit_file ('C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\*.sqlaudit', default, default) WHERE CAST(DATEDIFF_BIG(ns, '1970-01-01 00:00:00.0000000', event_time) AS BIGINT) > ?; bookmark_field: "epoch" 

В поле connection_string укажите:

• IP-адрес сервера с СУБД
• Порт для подключения к базе данных
• Название драйвера Примечание: Название драйвера можно узнать, запустив Administrative Tools → ODBC Data Sources (64-bit) во вкладке Drivers (поле Name)
• Название базы данных
• Учетные данные для подключения к БД

В разделе с SQL запросом необходимо указать путь к файлам с событиями аудита.

C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\*.sqlaudit

В данном случае коллектор будет читать все найденные файлы аудита по адресу C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\

PostgreSQL

Для настройки логирования событий из БД PostrgreSQL выполните шаги:

В командной строке сервера выполните команду

psql -U -c 'SHOW config_file' 

log_destination = 'syslog' logging_collector = off syslog_facility = 'LOCAL0' syslog_ident = 'postgres' syslog_sequence_numbers = on syslog_split_messages = off client_min_messages = log log_min_messages = info log_min_error_statement = info log_checkpoints = off log_connections = on log_disconnections = on log_duration = off log_error_verbosity = default log_hostname = on log_line_prefix = 'pgmessage: %m %a %u %d %r %i %e ' log_statement = 'mod' lc_messages = 'en_US.UTF-8' 

nano /etc/rsyslog.d/10-pgsql.conf if $programname == 'postgres' then @@rsyslog:4000 

Настройка ODBC PostgreSQL

1. В конфигурационном файле /var/app/data/postgresql.conf настройте тип логирования (csvlog) и включите logging_collector .

log\_destination = 'csvlog' logging\_collector = on client\_min\_messages = log log\_min\_messages = info log\_min\_error\_statement = info log\_checkpoints = off log\_connections = on log\_disconnections = on log\_duration = off log\_error\_verbosity = default log\_hostname = on log\_line\_prefix = 'pgmessage: %m %a %u %d %r %i %e ' log\_statement = 'mod' lc\_messages = 'en\_US.UTF-8' 

CREATE TABLE postgres_log ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text, PRIMARY KEY (session_id, session_line_num) ); 

COPY postgres_log FROM '/var/app/data/pg_log/postgresql-2020-09-01_000000.csv' WITH csv; 

Подробнее о переносе описано в Руководстве PostgreSQL

Настройка ODBC-модуля NXLog

Строка для ODBC-подключения:

 Module im\_odbc ConnectionString Driver=;Server=;Port=;Database=Database\_name;UID=Username;PWD=password; 

Driver — имя драйвера ODBC из п.5 — PostgreSQL ODBC Driver(UNICODE) или PostgreSQL ODBC Driver(ANSI).
Server — имя сервера PostgreSQL.
Port — порт, используемый для подключения к серверу PostgreSQL (default 5432).
Database — имя базы данных PostgreSQL.
Uid и Pwd — Uid (идентификатор пользователя) и Pwd (пароль) для подключения.

Oracle Database

Настройка источника Oracle Database на отправку событий с помощью Oracle Audit.

Настройку источника нужно выполнять от имени учетной записи root, поддерживающей в настраиваемом экземпляре СУБД роль sysadmin с привилегиями sysdba и sysoper:

1. Подключитесь с помощью sqlplus локально, выполнив команду: sqlplus / as sysdba
2. Выполните команду: alter session set «_ORACLE_SCRIPT»=true;
3. Проверьте параметры аудита командой: show parameter audit
4. Выполните команду установки журнала аудита OS: ALTER SYSTEM SET audit_trail=OS SCOPE=SPFILE;
5. Выключите СУБД командой: Shutdown
6. Включите СУБД командой: Startup
7. Проверьте параметры аудита командой: show parameter audit Убедитесь, что audit_trail принял значение OS.
   Запишите значение audit_file_dest , оно понадобится при настройке отправки сообщений для параметра File .
8. Выполните команду: ALTER SYSTEM SET audit_sys_operations=true SCOPE=SPFILE;
9. Установите важность событий командой: alter system set audit_syslog_level=’local5.info’ scope=spfile sid=’*’;
10. Выполните команду ALTER SYSTEM SET audit_trail=DB, EXTENDED SCOPE=SPFILE;
11. Выполните последовательно команды:

Shutdown Startup show parameter audit 

На выходе должны появиться сообщения (см. рисунок 14). Рисунок 14 — Вывод сообщений
Донастройте параметры аудита:

AUDIT ALTER SYSTEM BY ACCESS; AUDIT DELETE ON SYS.AUD$ BY ACCESS; AUDIT DELETE ON SYS.FGA_LOG$ BY ACCESS; AUDIT EXECUTE ON SYS.DBMS_FGA BY ACCESS; AUDIT INSERT ON SYS.AUD$ BY ACCESS; AUDIT INSERT ON SYS.FGA_LOG$ BY ACCESS; AUDIT SELECT ON SYS.DBA_USERS BY ACCESS; AUDIT SELECT ON SYS.LINK$ BY ACCESS; AUDIT SELECT ON SYS.USER_DB_LINKS BY ACCESS; AUDIT SELECT ON SYS.USER_HISTORY$ BY ACCESS; AUDIT SYSTEM AUDIT BY ACCESS; AUDIT TABLE BY ACCESS; AUDIT UPDATE ON SYS.AUD$ BY ACCESS; AUDIT UPDATE ON SYS.FGA_LOG$ BY ACCESS; 

• создайте файл с конфигурацией для rsyslog: nano /etc/rsyslog.d/oracle_audit.conf
• настройте чтение из файла:

input(type="imfile" File=".xml" PersistStateInterval="100" Tag="oracle_audit_trail:" Severity="info" Facility="local5" startmsg.regex="" ) local5.* @@:2770 

Oracle MySQL

Для настройки источника Oracle MySQL выполните следующие шаги:

Установите модуль аудита MariaDB, последовательно выполнив команды:

wget http://mirror.mephi.ru/mariadb/mariadb-10.1.45/bintar-linux-x86_64/mariadb-10.1.45-linux-x86_64.tar.gz sudo tar -xzf mariadb-10.5.5-linux-x86_64.tar.gz sudo install mariadb-10.1.45-linux-x86_64/lib/plugin/server_audit.so /usr/lib/mysql/plugin sudo install mariadb-10.5.5-linux-x86_64/lib/plugin/server_audit.so /usr/lib/mysql/plugin Sudo mysql INSTALL PLUGIN server_audit SONAME 'server_audit.so'; SHOW PLUGINS; Set Global server_audit_logging=on; EXIT; 

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Добавляем настройки plugin-load=server_audit=server_audit.so server_audit_logging=on server_audit_events=connect,query,table,query_ddl,query_dml,query_dcl server_audit_output_type = SYSLOG server_audit_syslog_facility = LOG_SYSLOG server_audit_file_path = /var/log/mysql/audit.log 

``` SHOW VARIABLES LIKE '%audit%'; EXIT; ``` 

Далее необходимо настроить rsyslogd. Для этого:

1. Создайте файл с конфигурацией для rsyslog: sudo nano /etc/rsyslog.d/20-mysql.conf
2. Запишите в созданный файл следующие значения:

template (name="radar" type="string" string="%TIMESTAMP. date-rfc3339% %HOSTNAME% %syslogtag%%$.suffix%%msg. sp-if-no-1st-sp%%msg%") :syslogtag, contains, "mysql" @@:4005;radar 

Orcale NetListener

Для настройки источника Oracle NetListener выполните следующие шаги:

1. Запустите LSNRCTL командой: LSNRCTL
2. Определите экземпляр используемой службы Oracle NetListener командой: show current_listener
3. После выполнения команды отобразится имя экземпляра СУБД.
4. Для смены используемого экземпляра используется команда: set current_listener.
5. Проверьте статус журналирования: show log_status
6. Если для параметра log_status указано OFF, включите журналирование: set log_status on save_config reload
7. Для отправки событий через rsyslog, узнайте путь к лог-файлам командой: show log_directory Он понадобится для следующего этапа настройки в параметре File.
8. Создайте конфигурационный файл для rsyslog: sudo nano /etc/rsyslog.d/oracle_netlistener.conf
9. Настройте чтение из файла:

module(load="imfile" mode="inotify") #PollingInterval="10") #mode="inotify") input(type="imfile" File="//log.xml" PersistStateInterval="100" Tag="oracle_netlistener:" Severity="info" Facility="local3" readMode="2" )local3.* @@:2771 

Просмотр журнала приложений Windows

Если настройками SQL Server предусмотрено использование журнала приложений Microsoft Windows, каждый сеанс SQL Server записывает новые события в этот журнал. В отличие от журнала ошибок SQL Server , новый журнал приложений не создается заново каждый раз при запуске экземпляра SQL Server.

Просмотр и управление журналом приложений Windows осуществляется с помощью средства просмотра событий Windows или средства просмотра журналов в среде SQL Server Management Studio.

С помощью средства просмотра событий можно просмотреть три журнала.

Дополнительные сведения о средстве просмотра событий, управлении журналом приложений и данных, которые он представляет, см. в документации Windows.

Просмотр журнала приложений Windows

Работа с журналом событий SQL Server

В документации по SQL Server отсутствует систематизированное изложение вопросов, связанных с Error Log, — сведения разбросаны по разным разделам, а часть информации, относящаяся к применению Transact SQL при работе с журналом, и вовсе отсутствует. Данная статья призвана в какой-то мере восполнить этот пробел в документации и помочь администраторам баз данных и разработчикам приложений научиться полностью использовать возможности журнала Error Log.

Что такое Error Log

При каждом запуске SQL Server начинает новый журнал Error Log, который представляет собой текстовый файл, расположенный по умолчанию в каталоге MSSQLLog. Файлы журналов, созданные при предыдущих запусках, не удаляются, а просто переименовываются. Текущий файл журнала имеет имя Error Log, файл предыдущего запуска Error Log.1 и т. д. По умолчанию SQL Server хранит файлы шести предыдущих журналов. Проще всего просмотреть журналы в Enterprise Manager (см. Рисунок 1 ). Изменить местоположение файлов журнала можно, используя параметр -e при старте сервера. В Enterprise Manager это делается через контекстное меню SQL Server properties: закладка General — Startup Parameters (см. Рисунок 2).

Рисунок 2. Установка параметров запуска SQL Server.

Число сохраняемых журналов устанавливается в Enterprise Manager c помощью контекстного меню SQL Server Logs Configure (см. Рисунок 3). Можно сохранять не более 99 журналов предыдущих запусков.

Рисунок 3. Установка параметров Error Log.

При запуске SQL Server в журнал заносится следующая информация:

• дата и время запуска;
• используемые версии SQL Server и Windows NT/2000 с учетом Service Pack;
• системный приоритет SQL Server и число процессоров в системе;
• информация о подключении системных и пользовательских баз данных;
• используемые сетевые библиотеки;
• сведения о готовности SQL Server к работе с клиентскими соединениями.

Какие события отражаются в Error Log

• копирование/восстановление базы данных;
• нехватка дискового пространства в базе данных;
• выполнение команды KILL.

Рисунок 4. Установка аудита регистрации пользователей.

Как записывать собственные сообщения в Error Log

Собственные сообщения можно записать в журнал с помощью оператора RAISERROR или хранимой процедуры xp_logevent. В RAISERROR для записи в журнал используется параметр WITH LOG :

RAISERROR ('Ошибка при добавлении данных!',10,1) WITH LOG

Хранимая процедура xp_logevent специально предназначена для записи сообщений в Error Log:

exec master..xp_logevent 60000, ?посылка почты?,ERROR

Первый параметр — код сообщения, он должен быть больше 50 000. Последний параметр может принимать значения INFORMATIONAL, WARNING или ERROR. Необходимо обратить внимание на то, что xp_logevent, в отличие от RAISERROR, не посылает сообщение клиентской программе и не изменяет значения глобальной переменой @@ERROR. Для xp_logevent также нужна настройка прав на выполнение.

Хранимые процедуры для работы Error Log

Хранимая процедура sp_enumerrorlogs служит для получения полного списка журналов с указанием даты окончания записи в них и их объема. Хранимая процедура sp_readerrorlog читает журнал с указанным номером. Если номер не указан или номер 0, читается текущий журнал. Результаты выполнения названных процедур приведены на Рисунке 5 . Обе процедуры возвращают в качестве результата таблицы, структуры которых приведены ниже при описании примера работы с Error Log. Хранимая процедура sp_cycle_errorlog служит для принудительного открытия нового файла журнала. Необходимость открыть новый файл журнала может быть вызвана, например, большим объемом текущего журнала в результате длительной непрерывной работы сервера (журнал большого объема трудно просматривать и анализировать).

Пример практического использования знаний об Error Log

Администратор базы данных должен знать, как часто происходит запуск и остановка SQL Server. Особенно эта информация полезна, если сервер физически расположен не в организации, а у провайдера услуг. При этом желательно знать общее время работы сервера и причины остановки. Сценарий, приведенный в Листинге 1 , решает эту задачу, используя информацию из Error Log. Итог исполнения сценария на тестовом сервере приведен на Рисунке 6 . Здесь показано, что первый, второй, пятый и шестой журналы были закрыты по причине остановки Windows, третий журнал — в результате остановки службы SQL Server, а четвертый — принудительно хранимой процедурой sp_cycle_errorlog. В текущем журнале последним событием является попытка неудачной регистрации пользователя. Ильдар Даутов — MCT, MCDBA, начальник отдела АКБ «Заречье», dia@zarech.ru.