Watching the new leases on the DHCP server using PowerShell

I Want to tell you about one of the ways to monitor the new leases on a Windows DHCP server with multiple Skopje. The task was to find a new rental, run through a list of eyes on the subject of unwanted hosts and their subsequent ban.

For the solution we will create a SQLite database and will save information about existing leases, and then use a query to compare table obtained earlier (e.g., yesterday or a week ago) with actual data.

At the beginning I want to note that I am not a programmer and maybe the code presented here is not very efficient, but it solves the problem. In the script used the cmdlets introduced in PowerShell 3.0, and the work tested on Windows Server 2012 R2 as a DHCP server made for Windows Server 2008 R2.

To use SQLite, we'll need two libraries: System.Data.SQLite.dll and SQLite.Interop.dll. You can download them here as part of the System.Data.SQLite. We need a non-bundle version. I have the version 4.5 of the framework, so I chose sqlite-netFx45-setup-x64-2012-1.0.96.0.exe

Library in this case the dll are in the same folder with the script:

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

For convenience, wrap existing methods in the function script:

the 
# Function command to execute 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
}

Respondents DHCP server on the subject of the relevant leases. If the launch was not specified -IncludeReservations, skipping backup

the 
# Getting the DHCP scopes and leases from a DHCP server, with reserved addresses excludes 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 database or set a connection to the existing systems, and create an object instance of SQLite.SQLiteCommand:

the 
# Creating and opening a SQLite DB connection
# If the 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 the previously created DB connection
$SQLiteCommand = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$SQLiteCommand.Connection = $SQLiteConnection

Create a "today" table, use the query:

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

Fill it received from the server values. Previously defined function str() returns an empty string instead of null:

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

Choose two last names (they are dates) tables to compare their contents:

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

And compare:

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

Output the result to the console:

the 
$Result | ft -Wrap-AutoSize

Example output table:

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

Close the database connection and destroy the objects:

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

The obtained result can, e.g., send mail, and the script to run on a schedule.
the Complete script
[CmdletBinding()]
Param(
# Name of the DHCP server
[Parameter(Mandatory=$True)][string]$ComputerName,

# Directory of the SQLite database file
[Parameter(Mandatory=$True)][string]$DBDirectory,

# Include reserved addresses in the query
[switch]$IncludeReservations
)

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

# Function command to execute 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 the DHCP scopes and leases from a DHCP server, with reserved addresses excludes 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 a SQLite DB connection
# If the 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 the previously created DB connection
$SQLiteCommand = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$SQLiteCommand.Connection = $SQLiteConnection
}

PROCESS {
# Getting current date and choosing the name of the table for current date
$CurrentTableName = "_$((Get-Date -Format "yyyyMMdd").ToString())"

# Check if table exists for current date. 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 (PRIMARY KEY IPAddress, 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 the last two tables (by date) from database to compare and check the difference
# Compare the 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 of SQLite objects
$SQLiteCommand.Dispose()
$SQLiteConnection.Close()
$SQLiteConnection.Dispose()
}


Example run: ..ps1 -ComputerName mydhcp.domain-DBDirectory D:\Temp
Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Wikia Search — first impressions

Изображение
Today, January 7, as promised, was launched search engine Wikia Search , still only in alpha version. ( edit Angela Beasley at 06:58 UTC or 09:58 Moscow time.) Of bugs: when searching in Russian (for example, the word habrahabr ) there are errors with the coding, although it seems that everything is in utf-8. Users who have an account on Wikia, you can login with the same username, but properties users flew in Wikia Search Alpha, and on the Wikia website. About search: while search (English queries), you can create ministate (miniarticles). Next to the search result indicates the number like 1.26 (something like pagerank'a or TIC), clicking on which shows how it was counted. When you hover over the number next to it shows the stars, which don't work (looks like that will be implemented by the ranking of results). It is unclear how results are ranked, because the query " George Bush " first gets a website "George Bush Is A Crackwhore!" wit...
Далее...

Emulator data from GNSS receiver NMEA

Изображение
Introduction I had the need to test an application that uses the GNSS data NMEA. was Due to the fact that worked on the project navigation of the aircraft, test the air expensive naturally on earth to drive a car with a GNSS receiver is not particularly convenient, that like sitting at the table to have a virtual parallel port data Protocol NMEA allegedly moving equipment. First, search for different software had to try and find something suitable, but most paid and control of the emulation data is not very convenient, although practically emulate all settings of the NMEA standard. But I needed something simple emulating coordinates, speed, in principle, nothing more was required quite convenient and logical control. So I had to write an app of this kind in C#. Fly_nmea NMEA ("National Marine Electronics Association") — the full name is "NMEA 0183" is a text communication Protocol, marine (typically navigation) equipment together. Data is transmit...
Далее...

mSearch: search + filter for MODX Revolution

Изображение
Something blog MODX habré completely withered, we need to spice things up. I want to introduce you to your component, which has recently been almost completely rewritten and expanded. It's called mSearch and originally conceived as a simple site search with account of morphology of the Russian language. That is, you had a quick and easy solution for any site without installing Sphinx and other major systems. In the process gugleniya, I came across a interesting this task by phpMorphy . Full text search on a table with an index, with the generation of different word forms. The idea I liked, by my criteria it came up and I wrote my solution, basing this method. the mSearch This snippet and plugin. The snippet is looking for the plug-in index documents when saving. Installation, as usual, from the repository, in 4 clicks. At the same time, you will be prompted to download and install for phpMorphy dictionaries of the Russian language — do not give up. ...
Далее...