Home > DHCP, Powershell Scripts, Windows Server > Export DHCP scopes and their address pools to a csv file

Export DHCP scopes and their address pools to a csv file

Because I love regular expressions, and I had a need for it, I have modified my previously posted DHCP export script:

$a = (netsh dhcp server 1.1.1.1 show scope)

$lines = @()
#start by looking for lines where there is IP present
foreach ($i in $a){
    if ($i -match "\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"){
            $lines += $i.Trim()
    }
}

$csvfile = @()
$lines2 = @()
foreach ($l in $lines){
    $Row = "" | select Subnet,SubNetMask,ScopeStart,ScopeEnd,Location
    $l = $l + "`n"
    $l = $l -replace '-Active',''
    $l = $l -replace '-',','
    $l = $l -replace '\s',''
    $Row.Subnet = ($l.Split(","))[0]
    $c = $Row.Subnet
    $Row.SubNetMask = ($l.Split(","))[1]
    $Row.Location = ($l.Split(","))[2]
    $b = (netsh dhcp server 1.1.1.1 scope $c show iprange)
    foreach ($i2 in $b){
        if ($i2 -match "\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}" -and $i2 -notmatch 'Changed the current scope context to' `
            -and $i2 -notmatch 'No of IP Ranges : 1 in the Scope'){
            $lines2 += $i2.Trim()
        }
    }
    Foreach ($l2 in $lines2){
        $l2 = $l2 -replace '-',','
        $l2 = $l2 -replace '\s',''
        $Row.ScopeStart = ($l2.Split(","))[0]
        $Row.ScopeEnd = ($l2.Split(","))[1]
    }
    $csvfile += $Row
}    
$csvfile | sort-object Subnet | Export-Csv "C:\Users\Public\Documents\DHCPExport.csv"

The script put all the server scopes into a variable and then processes it.

As the netsh output is filled with headers and other stuff, quite a number of trims and –replacements are done.

The whole thing is then put into a csv file.

As usual any hints and pointers are welcomed.

/theadminguy

  1. Dorin
    08/11/2011 at 12:02

    Hello,
    Big thanks to you for sharing that script…
    One more help request for you… pleaseeeee… please…
    🙂
    can you help me? I need to scan all my network, there are a lot of scopes, from 10.24.11.0 to 10.24.41.0, and there is no pattern by default to support that. So I need to write the script something like:

    $a = (netsh dhcp server 10.24.2.2 scope 10.24.11.0 show clients 1,netsh dhcp server 10.24.2.2 scope 10.24.12.0 show clients 1)

    But it does not work this way. Can you help me, please?

    Thanks anyway for a great job on that.

    • The Admin Guy
      08/11/2011 at 19:29

      You need to first get the scopes (netsh dhcp server show scope 1), and then for each scope do the $a = (netsh dhcp server scope show clients 1).

      However try to look into this PS module from MS Technet: PowerShell Module for DHCP
      The Get-DHCPScope function should return a nice collection of scopes, which you can then export to HTML. I have just tested it briefly, and could not get it to work in my test lab, so I cannot confirm that it works.

      I will be posting an updated version of the script to handle multiple scopes within the next couple of days.

    • The Admin Guy
      27/03/2012 at 00:39

      Hi
      In case you still need it, updated version of the script has been posted. The script can now dump all scopes on a given DHCP server

      /Anders

  2. Krishna
    21/11/2011 at 18:45

    Hello,

    Do you have a script which can export the exclusions as well ?
    We have a requirement where we need the exclusions of each and every scope.

    The above script works great but it doesnt export the exclusions in a particular scope.

    Thanks.

  3. Nicoyve
    25/12/2011 at 00:52

    please
    What can I change in the script so that I can export the full name of scope
    because “Location” take the name of the scope with no spaces and not full name (except 13 characters)
    I wait your response
    thx

  4. Vishal Khanna
    29/01/2013 at 18:43

    will it get the reservation IPs in all the scopes

  5. eyeeneff
    02/10/2014 at 17:14

    Nice article. Stole some bits 🙂

    Used column alignment to place items into hash table. Had to re-align some dhcp lease rows first. Some of our leases had “-” in the name, which the script didn’t handle well.
    The attached script:
    – Exports DHCP Scopes
    – Exports DHCP Leases for each of these DHCP scopes

    Works on WIndows 2008 R2 – may not work on other OS’ if the output format is different. Good Luck

    # ——————————
    # Retrieve Scopes from Server
    # ——————————

    $server = “1.1.1.1”

    $lines = @()
    $columns= @()
    $rows = @()
    $headers= @()

    # Retrieve DHCP scopes on named server
    $input = (netsh dhcp server show scope)

    # Extract header row and rows containing scopes
    foreach ($i in $input){
    # Search for IP address
    if ($i -match “\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}”){
    $lines += $i
    $b=0
    $j=0

    # Create associative array for scopes
    $row = “” | select $headers
    # Split row into columns and store in array
    $columns | % {$row.($headers[$j]) = ($i.SubString($b,$_-$b)).Trim();$b=$_+1;$j++}
    $rows+=$row

    } elseif ($i -match “Scope Address”){
    # Parse the header row and retrieve column positions
    $eol = $i | measure-object –character | select -expandproperty characters
    (Select-String -Pattern ([regex]’-‘) -InputObject $i -AllMatches).Matches | % {$columns += $_.Index}
    $columns += $eol
    # Next line is equivalent of $headers= “ScopeAddress”,”SubnetMask”,”State”,”ScopeName”,”Comment”
    $columns | % {$headers+=(($i.SubString($b,$_-$b)).Trim()).Replace(” “,””);$b=$_+1}
    }
    }

    $scopes = $rows

    # Output DHCP scopes
    # $scopes | select * | ft

    # ——————————
    # Retrieve Leases for each Scope
    # ——————————

    # Parse all scopes. For each scope export DHCP leases
    $scopes.GetEnumerator() | % {

    $lines = @()
    $columns = @()
    $headers = @()
    $rows = @()

    $maxeol = 0
    $eol = 0
    $eoi = 0
    $b = 0

    # Extract leases from scopes
    $input = (netsh dhcp server $server scope $_.ScopeAddress show clients 1)
    # Extract header row and rows containing leases. Reformat lines into aligned columns
    foreach ($i in $input){
    $eoi = $i | measure-object –character | select -expandproperty characters; $maxeol = [math]::max($maxeol,$eoi)
    # Search for IP address
    if ($i -match “\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}”){
    # Search for MAC Address
    if ($i -match “[0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}”){
    # Reformat misaligned columns
    $i = $i.Replace(“-D- “, “-D -“)
    $i = $i.Replace(“-U- “, ” -U -“)
    $i = $i.Replace(“-N- “, ” -N -“)
    $i = $i.Replace(“- INACTIVE”, ” -INACTIVE”)
    $i = $i.Replace(“- NEVER EXPIRES”, ” -NEVER EXPIRES”)
    $lines += $i
    }
    } elseif ($i -match “IP Address”){
    # This section should be the first line processed as it pertains to the Header row
    # Reformat header to align columns: Replace “-Type -Name” with ” – T – Name”
    # IP Address – Subnet Mask – Unique ID – Lease Expires -Type -Name
    # IP Address – Subnet Mask – Unique ID – Lease Expires -Typ- Name
    $i = $i.Replace(“-Type -Name”, ” -Typ- Name”)
    $eol = $i | measure-object –character | select -expandproperty characters
    (Select-String -Pattern ([regex]’-‘) -InputObject $i -AllMatches).Matches | % {$columns += $_.Index}
    $columns += $eol
    $columns | % {$headers+=(($i.SubString($b,$_-$b)).Trim()).Replace(” “,””);$b=$_+1}

    }
    }
    # Set maximum length of row as maximum EOL (header row is shorter than data rows)
    $columns[$columns.count-1] = $maxeol

    foreach ($l in $lines){
    $b=0
    $j=0
    # Create associative array for scopes
    $row = “” | select $headers
    # Increase length of line to length of longest dhcp-lease row within this scope using padding
    $l=$l.PadRight($maxeol,” “)

    # Store dhcp leases into named values in hash table $row
    $columns | % {$row.($headers[$j]) = ($l.SubString($b,$_-$b)).Trim();$b=$_+1;$j++}
    # Add row to table
    $rows+=$row
    }
    $leases += $rows
    } # End – $scopes.GetEnumerator()

    # Output all leases
    $scopes | select * | export-csv -NoTypeInformation -Path c:\cache\Reports\dhcp-scopes-$server.csv
    $leases | select * | export-csv -NoTypeInformation -Path c:\cache\Reports\dhcp-leases-$server.csv

    # Destroy variables
    $lines = $Null
    $columns = $Null
    $headers = $Null
    $rows = $Null
    $leases = $Null
    $scopes = $Null

  1. 20/08/2012 at 19:37

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: