Мониторинг 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: [email protected]
           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