Логирование входа (аутентификации) пользователей и компьютеров в домене в базу MSSQL

Давным-давно, еще во времена Windows Server 2003, у меня возникла необходимость логировать информацию о входах пользователей на компьютеры домене в удобном виде. Тогда задача решалась простым батник, который запускался как логон скрипт в GPO. Батник записывал в блокнот логин пользователя, имя компьютера, дата и время входа. С тех пор утекло много воды, батник усложнялся и переезжал с одного места работы на другое. Со временем скрипт был переделан на рельсы PowerShell, а информация стала писаться во внешнюю базу данных MS SQL.

Скрипт будет полезен администраторам, безопасникам и людям, которые занимаются инвентаризацией компьютеров в организации. Изначально скрипт писал данные в csv файл в сетевой папке. Но со временем, размера CSV файл сильно вырастал, и он стал открываться очень долго. Приходилось создавался новый файл. В итоге решил складывать информацию о регистрации пользователей в сети в отдельной небольшой базе. Для удобства просмотра содержимого базы данных сделал небольшую веб-страницу на PHP.

PowerShell скрипт для сбора инвентаризационной информации о компьютере и времени входа пользователя выглядит так:

#Собираем данные
$computerSystem = Get-CimInstance CIM_ComputerSystem
$windowsInfo = Get-CimInstance -ClassName win32_operatingsystem
$username = $env:USERNAME
$computername = $env:COMPUTERNAME
$vendor = Get-CimInstance -ClassName Win32_ComputerSystemProduct | foreach { $_.vendor }
$computerver = Get-CimInstance -ClassName Win32_ComputerSystemProduct | foreach { $_.version }
$serial = get-wmiobject win32_bios | foreach { $_.serialnumber }
$computerCPU = Get-CimInstance CIM_Processor |foreach { $_.Name }
$computerHDD = Get-CimInstance Win32_LogicalDisk -Filter "DeviceID = 'C:'"
$lastboot = Get-CimInstance CIM_OperatingSystem
$timeformat='MM-dd-yyyy hh:mm:ss tt'
$time = (Get-Date).ToString($timeformat)
$model = $computerSystem.Model
$HDD = "{0:N2}" -f ($computerHDD.Size/1GB)
$HDDFree = "{0:N2}" -f ($computerHDD.FreeSpace/1GB)
$RAM = "{0:N2}" -f ($computerSystem.TotalPhysicalMemory/1GB)
$Build = $windowsInfo.Version
$lastbootUP = $lastboot.LastBootUpTime
#Подключаемся к базе
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Data Source=servername,1433;Initial Catalog=basename;Integrated Security=SSPI;"
$connection.Open()
#Вставляем данные в базу
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $connection
$cmd.CommandText = "INSERT INTO BaseTable (Datetime,Username,Computer,vendor,model, compversion,Serial,CPU,HDD,HDDFree,RAM,Build,lastboot)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')" -f
$time,$username,$computername,$vendor, $model, $computerver,$serial,$computerCPU , $HDD, $HDDFree,$RAM,$Build, $lastbootUP
$cmd.ExecuteNonQuery()
#Закрываем соединение
$connection.Close()

Данный PowerShell скрипт нужно запускать при входе пользователя через GPO .

В качестве базы данных используется бесплатная редакция Microsoft SQL Server в редакции Express. Это бесплатная редакция с максимальным размером БД 10 Гб, чего за глаза хватит для наших нужд. MSSQL Server можно установить на выделенный хост, или использовать один из существующих серверов. Кроме того, за счет использования нативной БД Microsoft не придется ставить дополнительных драйверов или коннекторов на компьютеры пользователей для подключения к MSSQL из PowerShell .

Рассмотрим строку подключения к базе данных:

$connection.ConnectionString = "Data Source=servername,1433;Initial Catalog=basename;Integrated Security=SSPI;"

• servername — имя сервера, на котором запущена база данных MS SQL
• basename — имя базы данных

При добавлении информации в базу данных SQL запросом указывается:

• BaseTable — имя таблицы базы данных
• Datetime,Username,Computer,vendor,model, compversion,Serial,CPU,HDD,HDDFree,RAM,Build,lastboot — имена столбцов.

За 3 месяца у меня набежало 7190 строк.

Для вывода информации было решено создать простую страничку на php, которая будет читать с базы данных. Для этого на сервере с БД нужно установить веб сервер IIS и PHP.

К сожалению, знаний php не имел, поэтому пришлось учиться на ходу. У меня получился такой код:

<form name="search" method="POST" action-xhr="#">
<input name="query" placeholder="Поиск">
<button type="submit">Go</button>
</form>
<?php
if (isset($_POST['query'])) $query = $_POST['query'];
$serverName = "servername\sqlexpress";
$connectionInfo = array( "Database"=>"basename" , "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if (isset($query)) {
$tsql = "SELECT * FROM BaseTable WHERE Computer LIKE '%$query%' OR Username LIKE '%$query%' OR vendor LIKE '%$query%' OR Model LIKE '%$query%' OR Serial LIKE '%$query%'";
} else {
$tsql = "SELECT * FROM BaseTable";
}
$stmt = sqlsrv_query( $conn, $tsql );
//$stext = $_POST['name'];
if ($stmt) {
echo '<table border="1" cellpadding="7" cellspacing="0">';
/* Header */
?>
<tr>
<th>Username</th>
<th>Computer</th>
<th>Vendor</th>
<th>Model</th>
<th>Compversion</th>
<th>Serial</th>
<th>CPU</th>
<th>HDD</th>
<th>HDDFree</th>
<th>RAM</th>
<th>Build</th>
<th>DateTime</th>
<th>Lastboot</th>
</tr>
<?php
/* Body */
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo '<tr>';
foreach ($row as $key => $value) {
echo "<td  bgcolor='#D3EDF6' >{$value}</td>";
}
echo '</tr>';
}
echo '</table>';
}
?>

Ну и теперь просто переходя на URL адрес http://computername/ можно видеть информацию о входах пользователей в домен, а также инвентаризационную информацию.

При желании скритпы можно усложнить, добавить возможность поиска фильтрации, но это уже совсем другая история.