Mysql windows event log

I’m currently in the process of planning for an AppLocker rollout to all my PCs (about 7,500 of them) due to an increasing amount of malware. You should probably be doing this too. Anyway, a sensible first step is to identify which paths things are running from, which is pretty easy – you just turn AppLocker on in Audit mode. This makes it write messages into its event log telling what has been allowed to run, and what would be blocked from running were it in enforce mode rather than audit mode.

You now have your PCs collecting all this useful info in their event logs. Now you need to collate it centrally and process it. “Aha”, I thought, “this will be a great time to try out using the built in Windows Event Forwarding“. I followed some instructions, and it worked fine on my Windows 8.1 PC, and also on a colleague’s Windows 10 PC. Sadly, it failed on the other 7498 Windows 7 PCs. After a few days of trying to get it to work on them, I gave up and wrote my own version in PowerShell.

I think my version is better, because it allows you to query events in SQL, which is easier for me than trying to extract sensible information directly out of an event log – especially if the info you’re after is in the Messages field. You could of course modify the script and the SQL table to collect whatever fields you want. This is also not restricted to the AppLocker event log, you can collect events from any Windows Event Log.

This is going to be a big post with multiple sections, sorry about that, but it is pretty straightforward.

• You need an SQL server to store the events that you’re going to collect from the PCs. Maybe use SQL Server 2014 Express – which is free.
• The PCs push the events to the SQL server using an SQL bulk copy, which is pretty efficient.
• The event collection script is written in PowerShell, you should probably have at least version 3 of this on your PCs, and .Net 3.5, ideally PowerShell 4 and .Net 4.5 (at time of writing).
• The collection script is launched via a scheduled task, runs as the Network Service account, does not show on the user’s desktop whilst running, and only requires the Domain Computers group to have access to the SQL database. I’ve configured the scheduled task via a Group Policy Preference.
• The script writes a registry marker when it runs, and on subsequent runs only uploads events that have occurred since its last run. This means you can run it as often as you like an not get duplicate events in your SQL table.
• The script takes two parameters, the event log name to collect from, and the SQL server to send the events to.

PowerShell Script

A few points to note on the script:

• Pass it the name of the event log to collect events from, e.g. Microsoft-Windows-AppLocker/EXE and DLL
• If the upload to SQL Server fails, the timestamp marker is not written to the registry and thus the events in the event log will try to be uploaded again on the next run of the script. i.e. you will not be missing events in the SQL table if the SQL server is unavailable when the script runs.
• The log name is simplified to a form that SQL Server is happy with by removing spaces and hyphens, and converting forward slashes to underscores.
• The script assumes the database name of EventCollection
• The script requires you to create a table within this database for each log that you want to collect from, the table name needs to be the simplified version of the log name passed to the script, e.g.
  Microsoft-Windows-AppLocker/EXE and DLL becomes
  MicrosoftWindowsAppLocker_EXEandDLL
• See below for SQL script to create the database and table
• ALWAYS use ISO 8601 format datetime with PowerShell! (especially if you live outside USA)
• I have created a GP Pref to copy the script onto the C drive of all my PCs
• Storing the event data in a DataTable is bit more fiddly than using a simple array of objects, but it makes the bulk copy into SQL Server much easier – you just dump the whole thing across.
• The UserId returned from the event log in in the form of a SID, which is not terribly useful to me, so I wrote the Get-UserFromSID function to change this into a username.

SQL Database and Table Creation

Here’s the SQL Server code, paste into SQL Management Studio and run it.

You also need to grant the Domain Computers group permission to bulk copy into the table.

From SQL Management Studio:

1. Go to Security – Logins
2. Create a new Login for the Domain Computers group, on the User Mapping page tick the EventCollection database.
3. Go to Databases – EventCollection – Security – Users
4. Double-click the Domain Computers group, go to the Securables page
5. Click Search… – All objects of the types… – OK
6. Tick Tables – OK
7. Ensure the table is selected in the Securables section, then in the Explicit permission tab tick:
   1. Insert: Grant
   2. Select: Grant
8. Click OK.

Note that I am not a SQL Server expert, so whilst this all works it may be missing many optimisations. I believe SQL bulk copies do not cause much transaction log activity, but you could always set your database to simple recovery mode anyway.

Scheduled Task Configuration

I’ve configured this via a Group Policy Preference. Here’s what I did.

1. Create a new GPO, or edit an existing one. In the Group Policy Management Editor go to Computer Configuration, Preferences, Control Panel Settings, Scheduled Tasks.
2. Right-click, New – Scheduled Task (At least Windows 7). Leave the settings at their defaults except as detailed below.
3. General tab
   1. Action: Replace
   2. Name: Collect AppLocker Events EXE DLL
   3. When running the task, use the following user account: NT AUTHORITY\Network Service
4. Triggers tab – you can use any trigger you like, personally I’m doing it once a day based on time and day of the week
   1. Click New…
   2. Begin the task: On a schedule
   3. Settings: Weekly
   4. Start: 15:00:00
   5. Recur every: 1 weeks on: Monday Tuesday Wednesday Thursday Friday
   6. Delay task for up to (random delay): 1 hour (stops your SQL server being overwhelmed with all the collections happening at once)
   7. Stop task if it runs longer than: 30 minutes (this is just a safety net in case the script errors badly/hangs)
   8. Enabled needs to be ticked
5. Actions tab
   1. Click New…
   2. Action: Start a program
   3. Program/script: %WindowsDir%\System32\WindowsPowerShell\v1.0\powershell.exe (note that GPPrefs use their own “environment variables, hence %WindowsDir% and not %WinDir%. Hit F3 to view & insert GPPref variables)
   4. Add arguments(optional): -ExecutionPolicy Bypass -File “C:\Program Files\RCMTech\CollectEvents.ps1” (see my note earlier about using a GPPref to copy the script locally onto the PCs)
6. Settings tab
   1. Allow task to be run on demand: ticked (useful for testing, and why not anyway)
   2. Run task as soon as possible after a scheduled start is missed: ticked (in case the PC is switched off when the task is scheduled to run)
7. Common tab
   1. Remove this item when it is no longer applied: ticked

Note that running a scheduled task as a specific user is no longer possible via GPPref due a security flaw. Network Service is a good choice in this situation anyway. It causes the connection to the SQL server to be using the credentials of the computer’s own Active Directory account, e.g. RCMTech\MYPC$ which means you don’t need to give your users access to the database. This is good from a data protection point of view as the resulting database contains personally identifiable information.

Group Policy Preference to copy script onto target machines

1. Computer Configuration, Preferences, Windows Settings, Files.
2. Right-click, New – File
3. General tab
   1. Source File(s): \\rcmtech.co.uk\NETLOGON\LocalScripts\*.*
   2. Destination Folder: %ProgramFilesDir%\RCMTech
   3. Suppress errors on individual file actions: ticked
4. Common tab
   1. Remove this item when it is not longer needed: ticked
   2. Item-level targeting:
      1. the folder \\rcmtech.co.uk\NETLOGON\LocalScripts exists

All done

Once all the above is in place, you’re good to go. Now you just need to do something with all that event data sat in your SQL database.

Журналы (logs) в MySQL

В MySQL на данный момент существуют 4 вида журнала (лога) и при достаточно серьёзной работе с базами на MySQL необходимо за ними следить. Например, бинарный лог у нас за сутки набирает около гигабайта, а размер жёсткого диска на сервере ограничен и за ними надо следить. Однако следить следует не только за бинарным логом, так как логи (журналы) в MySQL могут принести немалую пользу.

Итак, какие логи ведёт MySQL? Это:
1. бинарный лог (binary log)
2. лог ошибок (error log)
3. лог медленный запросов (slow query log)
4. лог запросов (general query log)
5. лог репликаций (relay log)

Каждый из них по-своему полезен.

Бинарный лог

В первую очередь полезен с точки зрения репликаций. Можно его бэкапить, можно использовать для восстановления данных на более точное время при использовании бэкапов. Лог содержит все команды изменений базы данных, выборки (select, show) не сохраняет, для таблиц, поддерживающих транзакции (BDB, InnoDB) запись в лог выполняется только после выполнения команды COMMIT . Для лога можно указывать список баз данных, которые надо логировать и список баз данных, которые не надо логировать. В более ранних версиях вместо бинарного лога использовался лог обновлений. Использование бинарного лога снижает производительность базы данных, однако его польза настолько велика, что крайне не рекомендуется его отключать. Рекомендуется защищать бинарный лог паролем, так как он может данные также о паролях пользователей. При достижении максимально разрешённого размера (1 гиг по умолчанию) создаётся следующий файл. Каждый новый файл имеет порядковый номер после имени.

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

Основные настройки в my.cnf

Местоположение лога:
log_bin = /var/log/mysql/mysql-bin.log

Максимальный размер, минимум 4096 байт, по умолчанию 1073741824 байт (1 гигабайт):
max_binlog_size= 500M

Сколько дней хранится:
expire_logs_days = 3

Наиболее часто использующиеся команды

Повторение действий после операции восстановления:
shell> mysqlbinlog log_file | mysql -h server_name

Удаление логов до определённого файла:
PURGE BINARY LOGS TO ‘mysql-bin.000’;

Удаление логов до определённой даты:
PURGE BINARY LOGS BEFORE ‘YYYY-MM-DD hh:mm:ss’;

Лог ошибок

Особенно полезен в случаях сбоев. Лог содержит информацию об остановках, запусках сервера, а также сообщения о критических ошибках. Может содержать сообщения с предупреждениями (warnings).

Основные настройки в my.cnf

Местоположение лога:
log_error = /var/log/mysql/mysql.err

Флаг, указывающий стоит ли записывать в лог в том числе предупреждения (записываются, если значение больше нуля):
log_warnings = 1

Наиболее часто использующиеся команды

Переход к новому файл лога:
shell> mysqladmin flush-logs

Копирование старой части лога (необходимо, так как в случае повторного выполнения fluch он будет удалён):
shell> mv host_name.err-old backup-directory

Лог медленных запросов

Если есть подозрение, что приложение работает медленно из-за неэффективных запросов к базе, то в первую очередь следует проверить лог медленных запросов. В случае оптимизации запросов этот лог поможет выяснить, что необходимо оптимизировать в первую очередь.

Основные настройки в my.cnf

Местоположение лога:
log_slow_queries = /var/log/mysql/mysql_slow.log

Со скольки секунд выполнения запрос считается медленным, минимальное значений — 1 секунда, по умолчанию 10 секунд:
long_query_time = 10

Если надо логировать запросы, которые не используют индексы, надо добавить строку:
log-queries-not-using-indexes

Если надо вести лог медленных команд, таких как OPTIMIZE TABLE , ANALYZE TABLE и ALTER TABLE :
log-slow-admin-statements

Лог запросов

Лог содержит информацию о подключениях и отключениях клиентов, а также все SQL запросы, которые были получены. Фактически, это временный лог. Обычно лог удаляется автоматически сразу после выполнения всех команд (т.е. как только он стал ненужным). Лог ведётся в соответствии с очередность поступления запросов. Этот лог содержит все запросы к базе данных (независимо от приложений и пользователей). Так что если есть желание (или необходимость) проанализировать, какие необходимы индексы, какие запросы могли бы оптимизированы, то этот лог как раз может помочь в таких целях. Лог полезен не только для случаев, когда необходимо знать, какие запросы выполняются с базой данных, но и в случаях, когда ясно, что возникла ошибка с базой данных, но неизвестно, какой запрос был отправлен к базе данных (например, в случае генерации динамического SQL-а). Рекомендуется защищать лог запросов паролем, так как он может данные также о паролях пользователей.

Основные настройки в my.cnf

Местоположение лога:
log = /var/log/mysql/mysql.log

Наиболее часто использующиеся команды

В отличии от других логов, перезагрузка сервера и команда fluch не инициирует создание нового лога. Но это можно сделать вручную:
shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory

Лог репликаций

Здесь логируются изменения, выполненные по инициации сервера репликаций. Как и бинарный лог, состоит из файлов, каждый из которых пронумерован.

Основные настройки в my.cnf

Местоположение лога:
relay-log = /var/log/mysql/mysql-relay-bin.log

Максимальный размер:
max_relay_log_size = 500М

Наиболее часто использующиеся команды

Начать новый файл лога можно только при остановленном дополнительном (slave) сервере:
shell> cat new_relay_log_name.index >> old_relay_log_name.index
shell> mv old_relay_log_name.index new_relay_log_name.index

Команда fluch logs инициирует ротацию лога.

Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.