Мониторинг VPN
Хотите знать кто, когда и откуда подключался к вашему VPN серверу? Для этого понадобится один скрипт Powershell и база данных.
Собственно, сам Powershell скрипт, расположенный на RAIDUS сервере и который запускает планировщик задач каждую ночь в 00:05:
Add-Type -Path "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.8\Assemblies\v4.5\MySql.Data.dll"
$connectionString = "server=mysql1;uid=root;pwd=asdASD;database=VPN;"
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $connection
$today = get-date -DisplayHint date -UFormat %Y-%m-%d
$yesterday = get-date (get-date).addDays(-1) -UFormat %Y-%m-%d
#Get-WinEvent -FilterHashTable @{LogName="security";endtime="$today";id=6272} | Foreach {
Get-WinEvent -FilterHashTable @{LogName="security";starttime="$yesterday";endtime="$today";id=6272} | Foreach {
$Name = $_.Properties[0].Value
$FullName = (Get-ADUser $Name).Name
$LocalIP = $_.Properties[8].Value
$RemoteIP = $_.Properties[9].Value
$Time = Get-Date $_.TimeCreated -UFormat "%H:%M:%S"
$Date = Get-Date $_.TimeCreated -UFormat "%Y-%m-%d"
$sql.CommandText = "INSERT INTO connections (Day,Login,Name,VPN_IP,Client_IP) VALUES ('$Date','$Time','$FullName','$LocalIP','$RemoteIP')"
$sql.ExecuteNonQuery()
}
$connection.Close()
Комментарии по скрипту:
- первая строка: путь к коннектору, с помощью которого Powershell подключается к базе данных MySQL на Debian.
- вторая строка: подключение к серверу mysql1 под учётной записью root (root от MySQL, а не от сервера Debian).
- восьмая строка: определение текущей даты.
- девятая строка: определение вчерашней даты.
- десятая строка: чтение всех событий в журнале безопасности. Эту строку следует раскомментировать при первом ручном запуске скрипта.
- одиннадцатая строка: чтение событий в журнале безопасности за вчерашний день. Эту строку следует закомментировать при первом ручном запуске скрипта.
- Name и FullName: определение полного имени пользователя по его SID. SID всегда постоянен, а вот в логах могут стоять имена пользователей в разных видах. Например, Username или DOMAIN\Username.
- LocalIP: IP адрес VPN сервера. Вдруг их несколько.
- RemoteIP: внешний IP адрес, с которого подключается клиент.
- Time: время подключения.
- Date: день подключения.
- Последние четыре строки: выполнение запроса MySQL - запись в базу данных.
Вот такие две таблицы должны быть созданы в базе данных VPN:
mysql> show create table `connections`;
+-------------+------------------------------+
| Table | Create Table |
+-------------+------------------------------+
| connections | CREATE TABLE `connections` (
`Day` date NOT NULL,
`Login` time NOT NULL,
`Name` varchar(30) NOT NULL,
`VPN_IP` varchar(15) NOT NULL,
`Client_IP` varchar(15) NOT NULL,
`Country2` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+------------------------------+
1 row in set (0.00 sec)
mysql> show create table `IPs`;
+-------+------------------------------------+
| Table | Create Table |
+-------+------------------------------------+
| IPs | CREATE TABLE `IPs` (
`StartIP` varchar(15) NOT NULL,
`EndIP` varchar(15) NOT NULL,
`Country` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------+
1 row in set (0.00 sec)
После этого, база данных будет выглядеть так:
Теперь необходимо определить, какой стране принадлежит IP адрес пользователя VPN. Есть несколько способов, в том числе определение онлайн через php, но мне это намного сложнее сделать, да к тому же определение будет выполняться всякий раз, при обновлении веб-страницы. По этому, создаём ещё одну таблицу в базе данных, в которую импортируем файл CSV с IP и странами, последнюю версию "GeoLite Country" которого можно скачать тут. Импорт я делал через HeidiSQL.
Теперь необходимо настроить MySQL так, чтобы он проверял IP, и записывал соответствующую страну в поле Country2. Для этого используется планировщик событий MySQL. Скрипт выполняется на две минуты позже, после скрипта PowerShell:
CREATE EVENT `Country Update` ON SCHEDULE EVERY 1 DAY STARTS "2016-06-26 00:07:00" DO UPDATE connections SET connections.country2 = (SELECT IPs.country as country2 FROM IPs WHERE INET_ATON(connections.Client_IP) >= INET_ATON(IPs.StartIP) AND INET_ATON(connections.Client_IP) <= INET_ATON(IPs.EndIP) ) WHERE Client_IP=Client_IP AND COUNTRY2 = "";
Команду после DO нужно выполнить сразу после ручного запуска скрипта Powershell. Если она написана правильно, то таблица примет вид:
Проверку созданного события по расписанию можно проверить с помощью команды:
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'Country update' AND EVENT_SCHEMA = 'VPN'\G;
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: VPN
EVENT_NAME: Country Update
DEFINER: root@mysql1.domainad.domain.com
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: UPDATE connections SET connections.country2 = (SELECT IPs.country as country2 FROM IPs WHERE INET_ATON(connections.Client_IP) >= INET_ATON(IPs.StartIP) AND INET_ATON(connections.Client_IP) <= INET_ATON(IPs.EndIP) ) WHERE Client_IP=Client_IP AND COUNTRY2 = ""
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE:
STARTS: 2016-06-26 00:07:00
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2016-06-25 14:50:25
LAST_ALTERED: 2016-06-25 14:50:25
LAST_EXECUTED: 2016-07-02 00:07:00
EVENT_COMMENT:
ORIGINATOR: 0
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified
На этом можно заканчивать. Если у вас есть знакомые веб-программисты, то они легко вам сделают веб-страницу с выводом нужной информации. Я же пока не буду выкладывать свой вариант, так как там не всё гладко работает... Примерно, это выглядит так:
Если есть вопросы по настройке - спрашивайте, помогу. К сожалению, пока не нашёл, как можно определить выход пользователя, чтобы рассчитать время его сессии. А так же не нашёл как определить в логах по каким протоколам пользователь подключается к VPN. Например, у меня два сервера: L2TP на 2012 R2, и IKEv2 на 2008 R2, т.к. второй на 2012 R2 не работает. Если у вас есть какая-либо информация, буду рад если поделитесь в комментариях. Спасибо!
02.07.2016
