PowerShellを使用して、DHCPサーバー上のアドレスの新しいリースを観察します

 [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() }