Create a report on Windows DHCP servers

Create-DHCPReport.ps1

<#
.SYNOPSIS
    Creates an Excel workbook with DHCP server information.

.DESCRIPTION
    This script retrieves DHCP server summary data and subnet reservation data from the DHCP server and creates an Excel workbook with the information.

.PARAMETER SummaryData
    Specifies the DHCP server summary data to be included in the workbook.

.PARAMETER ReservationData
    Specifies the subnet reservation data to be included in the workbook.

.PARAMETER filename
    Specifies the path and filename of the Excel workbook to be created. If not provided, the workbook will be created in the user's temporary folder with the name "DHCP_Server_Info.xlsx".

.EXAMPLE
    New-DHCPWorkbook -SummaryData (Get-DhcpServerSummary) -ReservationData (Get-SubnetReservations)
    Creates an Excel workbook named "DHCP_Server_Info.xlsx" in the user's temporary folder with DHCP server summary data and subnet reservation data.

#>

function Get-DhcpServerSummary {

    Import-Module DhcpServer

    $dhcpServer = Get-DhcpServerInDC | Select-Object -First 1

    $superscopes = Get-DhcpServerv4SuperScope -ComputerName $dhcpServer.DnsName

    $summaryData = @()

    $summaryData = foreach ($superscope in $superscopes) {
        foreach ($scopeId in $superscope.ScopeId) {
            $subnets = Get-DhcpServerv4Scope -ComputerName $dhcpServer.DnsName -ScopeId $scopeId
            foreach ($subnet in $subnets) {
                $leases = Get-DhcpServerv4Lease -ComputerName $dhcpServer.DnsName -ScopeId $subnet.ScopeId
                [PSCustomObject]@{
                    "Subnet" = $subnet.ScopeId
                    "SubnetMask" = $subnet.SubnetMask
                    "Active Leases" = $leases.Count
                }
            }
        }
    }

    return $summaryData
}

function Get-SubnetReservations {
    Import-Module DhcpServer

    $dhcpServer = Get-DhcpServerInDC | Select-Object -First 1
    $superscopes = Get-DhcpServerv4SuperScope -ComputerName $dhcpServer.DnsName
    $reservationData = @()

    $reservationData = foreach ($superscope in $superscopes) {
        foreach ($scopeId in $superscope.ScopeId) {
            $subnets = Get-DhcpServerv4Scope -ComputerName $dhcpServer.DnsName -ScopeId $scopeId
            foreach ($subnet in $subnets) {
                $reservations = Get-DhcpServerv4Reservation -ComputerName $dhcpServer.DnsName -ScopeId $subnet.ScopeId
                foreach ($reservation in $reservations) {
                    [PSCustomObject]@{
                        "Subnet" = $subnet.Name -replace ".{3}$"
                        "IP" = $reservation.IPAddress
                        "MAC Address" = $reservation.ClientId
                        "Name" = $reservation.Name
                    }
                }
            }
        }
    }
    return $reservationData
}

function New-DHCPWorkbook {
    param (
        [Parameter(Mandatory=$true)]
        [Object[]]
        $SummaryData,

        [Parameter(Mandatory=$true)]
        [Object[]]
        $ReservationData,

        [System.IO.FileInfo]$filename = "$env:TEMP\DHCP_Server_Info.xlsx"
    )
    Import-Module ImportExcel

    $dhcpWorkBook = Open-ExcelPackage -Path $filename -KillExcel -Create
    Add-Worksheet -ExcelPackage $dhcpWorkBook -WorksheetName 'NetworkSummary'
    $SummaryData | Export-Excel -ExcelPackage $dhcpWorkBook -WorksheetName 'NetworkSummary' -AutoSize

    # Create the worksheets
    foreach ($data in $ReservationData) {
        $dhcpWorkBook = Open-ExcelPackage -Path $filename -KillExcel -Create
        # Check if a worksheet with the same name already exists
        if ($dhcpWorkBook.Workbook.Worksheets.Name -notcontains $($data.Subnet)) {
            Add-Worksheet -ExcelPackage $dhcpWorkBook -WorksheetName $($data.Subnet)
        }
        $data | Export-Excel -ExcelPackage $dhcpWorkBook -WorksheetName $($data.Subnet) -Append -AutoSize
    }
    Write-Output "Excel workbook created: DHCP_Server_Info.xlsx"
}

New-DHCPWorkbook -SummaryData (Get-DhcpServerSummary) -ReservationData (Get-SubnetReservations)