Geekly Articles each Day
📜 ⬆️ ⬇️

Watching new lease addresses on a DHCP server using PowerShell

I want to talk about one of the ways to monitor new lease addresses on a Windows DHCP server with a lot of Osprey. The task was the following: to find new rentals and to run through the list with eyes on the subject of objectionable hosts and their subsequent ban.

To solve, we will create a SQLite database and save information about existing leases in it, and then, using the query, compare the table obtained earlier (for example, yesterday or a week ago) with the actual data.

At the beginning I want to note that I am not a programmer and, perhaps, the code presented here is not very effective, but it solves the problem. The script uses the cmdlets that appeared for the first time in PowerShell 3.0, and the work was tested on Windows Server 2012 R2; Windows Server 2008 R2 was the DHCP server.

To use SQLite, we need two libraries: System.Data.SQLite.dll and SQLite.Interop.dll. Download them here as part of System.Data.SQLite. We need a non-bundle version. The version 4.5 of the framework is installed on my computer, so I chose sqlite-netFx45-setup-x64-2012-1.0.96.0.exe
')
We connect the library, in this case the dll are in the same folder with the script:

# Adding SQLite libraries, should be two files in script directory: System.Data.SQLite.dll, SQLite.Interop.dll Add-Type -Path "$PSScriptRoot\System.Data.SQLite.dll" -ErrorAction Stop

For convenience, we wrap the existing methods in the script function:

 # Function to execute command non query function ExecuteNonQuery($commandtext) { $SQLiteCommand.CommandText = $commandtext [void]$SQLiteCommand.ExecuteNonQuery() } # Function to execute command query function ExecuteReader($commandtext) { $SQLiteCommand.CommandText = $commandtext $DataReader = $SQLiteCommand.ExecuteReader() $Data = New-Object -TypeName System.Data.DataTable $Data.Load($DataReader) $DataReader.Close() return $Data }

We survey the DHCP server for current rentals. If the -IncludeReservations parameter was not specified at the start, skip the reservations:

 # Getting DHCP scopes and leases from DHCP server, excludes reserved addresses if -IncludeReservations not specified as parameter $Scopes = Get-DhcpServerv4Scope -ComputerName $ComputerName if ($IncludeReservations) { $Leases = foreach ($Scope in $Scopes) { Get-DhcpServerv4Lease -ScopeId $Scope.ScopeId -ComputerName $ComputerName } } else { $Leases = foreach ($Scope in $Scopes) { Get-DhcpServerv4Lease -ScopeId $Scope.ScopeId -ComputerName $ComputerName | where AddressState -notlike "*Reservation" } }

Create a database or establish a connection with an already existing one, and also create an instance of the SQLite.SQLiteCommand object:

 # Creating and opening SQLite DB connection # If DB file does not exist, create a new file. DB file name is "<name_of_server>.sqlite" $SQLiteConnection = New-Object -TypeName System.Data.SQLite.SQLiteConnection $DBFile = "$DBDirectory\$ComputerName.sqlite" if (Test-Path $DBFile) { Write-Verbose "Opening $DBFile" $SQLiteConnection.ConnectionString = "Data Source=$DBFile;" } else { Write-Verbose "Creating $DBFile" $SQLiteConnection.ConnectionString = "Data Source=$DBFile;New=True;" } $SQLiteConnection.Open() # Create SQLite command object using previously created DB connection $SQLiteCommand = New-Object -TypeName System.Data.SQLite.SQLiteCommand $SQLiteCommand.Connection = $SQLiteConnection

Create a "today's" table using the query:

 CREATE TABLE $CurrentTableName (IPAddress PRIMARY KEY, ScopeId, AddressState, ClientId, HostName);

We fill it with the values ​​received from the server. The previously defined str () function returns an empty string instead of null:

 INSERT OR IGNORE INTO $CurrentTableName VALUES('$(str($Lease.IPAddress))', '$(str($Lease.ScopeId))', '$(str($Lease.AddressState))', '$(str($Lease.ClientId))', '$(str($Lease.HostName))');

Select the last two by name (they are the same dates) of the table in order to compare their contents:

 SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name DESC LIMIT 2;

And compare:

 SELECT * FROM $LastTableName WHERE $LastTableName.ClientId NOT IN (SELECT ClientId FROM $PrevTableName);

Print the result in the console:

 $Result | ft -Wrap -AutoSize

An example of the output table:

 IPAddress ScopeId AddressState ClientId HostName --------- ------- ------------ -------- -------- 10.10.10.22 10.10.10.0 Active a5-b4-c3-d2-e1-f0 UsersOwnDevice.domain

Close the connection with the database and destroy the objects:

 # Closing and disposing SQLite objects $SQLiteCommand.Dispose() $SQLiteConnection.Close() $SQLiteConnection.Dispose()

The result can be, for example, sent by mail, and the script itself can be run on a schedule.

Complete script
 [CmdletBinding()] Param( # Name of DHCP server [Parameter(Mandatory=$True)][string]$ComputerName, # Directory of SQLite database file [Parameter(Mandatory=$True)][string]$DBDirectory, # Include reserved addresses in query [switch]$IncludeReservations ) BEGIN { # Adding SQLite libraries, should be two files in script directory: System.Data.SQLite.dll, SQLite.Interop.dll Add-Type -Path "$PSScriptRoot\System.Data.SQLite.dll" -ErrorAction Stop # Function to execute command non query function ExecuteNonQuery($commandtext) { $SQLiteCommand.CommandText = $commandtext [void]$SQLiteCommand.ExecuteNonQuery() } # Function to execute command query function ExecuteReader($commandtext) { $SQLiteCommand.CommandText = $commandtext $DataReader = $SQLiteCommand.ExecuteReader() $Data = New-Object -TypeName System.Data.DataTable $Data.Load($DataReader) $DataReader.Close() return $Data } # Converting value to string, returns empty string from null value function str($value) { if ($value -ne $null) { return $value.ToString() } else { return [string]::Empty } } # Getting DHCP scopes and leases from DHCP server, excludes reserved addresses if -IncludeReservations not specified as parameter Write-Verbose "Getting leases from $ComputerName" $Scopes = Get-DhcpServerv4Scope -ComputerName $ComputerName if ($IncludeReservations) { $Leases = foreach ($Scope in $Scopes) { Get-DhcpServerv4Lease -ScopeId $Scope.ScopeId -ComputerName $ComputerName } } else { $Leases = foreach ($Scope in $Scopes) { Get-DhcpServerv4Lease -ScopeId $Scope.ScopeId -ComputerName $ComputerName | where AddressState -notlike "*Reservation" } } # Creating and opening SQLite DB connection # If DB file does not exist, create a new file. DB file name is "<name_of_server>.sqlite" $SQLiteConnection = New-Object -TypeName System.Data.SQLite.SQLiteConnection $DBFile = "$DBDirectory\$ComputerName.sqlite" if (Test-Path $DBFile) { Write-Verbose "Opening $DBFile" $SQLiteConnection.ConnectionString = "Data Source=$DBFile;" } else { Write-Verbose "Creating $DBFile" $SQLiteConnection.ConnectionString = "Data Source=$DBFile;New=True;" } $SQLiteConnection.Open() # Create SQLite command object using previously created DB connection $SQLiteCommand = New-Object -TypeName System.Data.SQLite.SQLiteCommand $SQLiteCommand.Connection = $SQLiteConnection } PROCESS { # Getting current date and choosing the name of table for current date $CurrentTableName = "_$((Get-Date -Format "yyyyMMdd").ToString())" # Check if table for current date exists. If not, create a new one if ($CurrentTableName -ne (ExecuteReader("SELECT name FROM sqlite_master WHERE type = 'table' AND name = '$CurrentTableName';") | select -ExpandProperty name)) { Write-Verbose "Creating table $CurrentTableName" ExecuteNonQuery("CREATE TABLE $CurrentTableName (IPAddress PRIMARY KEY, ScopeId, AddressState, ClientId, HostName);") } else { Write-Verbose "Table $CurrentTableName exists" } # Update the current date table with collected leases Write-Verbose "Updating table $CurrentTableName" foreach ($Lease in $Leases) { ExecuteNonQuery("INSERT OR IGNORE INTO $CurrentTableName VALUES('$(str($Lease.IPAddress))', '$(str($Lease.ScopeId))', '$(str($Lease.AddressState))', '$(str($Lease.ClientId))', '$(str($Lease.HostName))');") } # Selecting last two tables (by date) from database to compare and check difference # Compare tables and print the difference between them. New leases from previous date to current date will be printed $TablesToCompare = ExecuteReader("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name DESC LIMIT 2;") if ($TablesToCompare.Count -gt 1) { Write-Verbose "Going to compare" $LastTableName = $TablesToCompare[0] | select -ExpandProperty name $PrevTableName = $TablesToCompare[1] | select -ExpandProperty name $Result = ExecuteReader("SELECT * FROM $LastTableName WHERE $LastTableName.ClientId NOT IN (SELECT ClientId FROM $PrevTableName);") write "`r`nNew leases between $($PrevTableName.Trim("_")) and $($LastTableName.Trim("_"))" $Result | ft -Wrap -AutoSize } else { write "No new leases" Write-Verbose "Not enough data to compare. First run?" } } END { # Closing and disposing SQLite objects $SQLiteCommand.Dispose() $SQLiteConnection.Close() $SQLiteConnection.Dispose() }


Example startup: patrol.ps1 -ComputerName mydhcp.domain -DBDirectory D: \ Temp

Source: https://habr.com/ru/post/252979/

More articles:


All Articles