Import Microsoft Defender Firewall rules from an Excel sheet with PowerShell in Windows 10

Emanuel Halapciuc Tue, Jul 6 2021 firewalls, powershell, security 4

With my PowerShell script, you can easily import rules from an Excel sheet to the Microsoft Defender Firewall in Windows 10. The script reads the ports that need to be opened or blocked from a simple CSV file.

Author
Recent Posts

Emanuel Halapciuc

Emanuel is an IT consultant who has worked for 20 years in the industry. He is currently involved in a deployment project for the European Commission and focuses on Active Directory, Group Policy, Azure, and automation with PowerShell.

Latest posts by Emanuel Halapciuc (see all)

Display the progress of long-running tasks in PowerShell with Show-Progress instead of Write-Progress - Wed, Jul 28 2021
Import Microsoft Defender Firewall rules from an Excel sheet with PowerShell in Windows 10 - Tue, Jul 6 2021
Convert a DHCP reservation to a static IP with PowerShell - Thu, Dec 17 2020

Contents of this article

Prerequisites
Creating the Excel sheet
The PowerShell script
Running the script
Verifying the created rules
Further reading

PowerShell, as it turns out, has a handy cmdlet called Import-Csv that enables you to read CSV files easily. Not only that, if you need to collaborate with others on the ports that need to be opened, you can also share this CSV file with your team so that they can add rules.

Prerequisites ^

If you'd like to follow along, be sure you have the following in place beforehand:

A Windows client and at least one Windows Server joined to an Active Directory domain
At least one Active Directory group with at least one Windows Server computer account in it
The ActiveDirectory PowerShell module installed on your local computer
Microsoft Excel installed locally or Excel Online

Creating the Excel sheet ^

You can create your own CSV file, or you can save yourself some time and download a predefined Excel workbook. The Excel workbook has two main worksheets: Rules and Server Groups.

The Rules worksheet

The Rules worksheet has various columns, each representing parameters you'll eventually need to pass to a PowerShell script. In the following screenshot, you can see that many of the rows have been filled in for you as examples. Each row contains one Windows Defender Firewall rule.

Rules worksheet

Excel cells with data validation

You'll see that many of the cells have Excel's data validation feature enabled. Data validation allows you to simply click on a cell and select from a list of predefined values.

Firewall rules in Excel

Below, you'll find explanations for each column of the Rules worksheet.

Rule number—You may pick whatever you want, but the rule group name will also include this (see Rule Group below).
Source Servers group—Where traffic will originate from.
Destination Servers Group—Where the rules will be created.
Traffic Type—TCP, UDP, Any
Destination Port—If you have more than one port, you can separate them by a semicolon or a dash if there is a range of ports. It is important to use a semicolon for the CSV structure and to prevent Excel from converting the values to numbers.
Bi-directional rule?—Specify whether traffic needs to be allowed in both directions.
Allow?—(or block) traffic. Most often, this will be set to Allow, but the Block option is available, too. For example, you may want only your web servers to communicate with database servers but block client computers.
Traffic description—Optional, but useful. This will populate the description for each rule. It may be useful in six months when you wonder why you created that rule (or tomorrow, for your colleagues).
Rule Group—This is a name that enables you to sort through all the firewall rules easily. You may choose whatever you want, but consistency and relevance will help you a lot.

The Server Groups worksheet

The next worksheet you'll see is called Server Groups. This worksheet enables you to define the names of the Active Directory groups you have on the servers you'll be targeting remotely, where the Windows Defender Firewall rules will be created.

Server groups

Note that the names you provide on the Server Groups worksheet need to match the servers in your Active Directory environment.

Creating the groups on this worksheet will spare you from typing them for each rule. The server groups also act as inputs to the Source Server Groups column on the Rules worksheet.

Defining rules

Perhaps you have a web application that requires database servers, web servers, and cache servers. You most likely need to restrict the traffic flow among these servers. Maybe you need to control HTTPS from the clients to the web servers, maybe some DNS and LDAP queries to the domain controllers, and so on. This is the purpose of the Server Groups worksheet. For instance, you can create groups for web servers, domain controllers, database servers, etc. These group names represent actual groups of computers from your Active Directory structure. The PowerShell script will create firewall rules on computers belonging to these groups later on.

Saving the Rules worksheet as a CSV file

Since it's much easier to work with a CSV in PowerShell than with an Excel workbook, the next step is saving the Excel workbook in CSV format. To do so, ensure that you have the Rules worksheet open in Excel. Then select File > Save As, and choose CSV.

Save as CSV

Your interface may look a bit different depending on your version of Excel.

The PowerShell script ^

My PowerShell script, provided below, performs the following functions:

Imports the CSV file of firewall rules.
Generates lists of servers based on group names.
Generates a list of remote IP addresses based on the function Get-ServerIPv4ListFromGroup, which is included in the script. IP addresses are retrieved from a DNS server (if specified) or from a domain controller (which is usually a DNS server) if no DNS server is specified.
For each rule, the script first deletes the existing rule from the destination servers if it has been changed since the last time it was created. It then creates the rule again, line by line.
If there is a bidirectional rule, it creates two "mirror" rules (on both the source and the destination servers).

The comments explain how the PowerShell script works.

Param(
        [Parameter(Mandatory=$false)]$SourceFile = "C:\Temp\Firewall Rules from text file.csv" #The CSV file to import.
    )

# This function returns the list of IPv4 addresses for one or more servers.
Function Get-ServerIPv4ListFromGroup {

#Requires -Modules ActiveDirectory,DNSServer    
<#
    .SYNOPSIS
    Get-ServerIPv4ListFromName returns the list of IPv4 addresses for one or more servers. The list is required since FQDNs and NetBIOS names cannot be added to simple firewall rules in Windows Defender Firewall.
    .EXAMPLE
    Get-ServerIpList -GroupName Server1,Server5 -DnsServer NS4
    Returns the IP(v4) addresses of the servers Server1,Server5. The IP addresses are retrieved from the DNS server NS4.
    .EXAMPLE
    Get-ServerIpList -GroupName $DbServers
    Returns the IP(v4) addresses of the server(s) in $DbServers. The DNS server queried for the IP addresses will be a domain controller in the user's domain.
   .NOTES
  Version:        1.0
  Author:         Emanuel Halapciuc 
.EXAMPLE
  'C:\New-WindowsFirewallRule.ps1' -SourceFile 'C:\rules.csv'
 #>

    Param(
        [Parameter(Mandatory=$true)][array]$GroupName, #List of computer(s)  for which the IP address will be retrieved.
        [Parameter(Mandatory=$false)][string]$DnsServer = (Get-ADDomainController).Hostname #If no DNS server is provided, the script will use a domain controller as a DNS server.
    )
    #Retrieve the list of computers.
    [array]$ComputersList = (Get-ADGroup -Filter * | Where-Object Name -like "$GroupName" | Get-ADGroupMember | Where-Object ObjectClass -EQ "computer").Name   
    #Initialize the list of IP Addresses
    [array]$IpAddressList = @()
    #Populate $IpAddressList with the IP addresses of the computers in the $ComputersList
    foreach ($c in $ComputersList) {
        $IpAddressList += (Get-DnsServerResourceRecord -RRType A -Name $c -ComputerName $DnsServer -ZoneName $env:USERDNSDOMAIN -ErrorAction SilentlyContinue).RecordData.IPv4Address.IPAddressToString
    }   
    return $IpAddressList
}

#Headers for each column. The column names in the original CSV are less relevant, but it makes things easier to follow.
$ColumnHeader = ("RuleNumber","Source","Destination","TrafficType","LocalPorts","Bidirectional","AllowOrBlock","Description","Rule")

#Import the CSV file (this is the $SourceFile whose path you provided earlier).
$ImportData = Get-Content -Path $SourceFile | Select-Object -Skip 1 | ConvertFrom-Csv -Delimiter "," -Header $ColumnHeader

# Once the data is imported from CSV, the script goes through each line (rule) to create the firewall rules.
foreach ($i in $ImportData) {
    #Construct the rule group name.
    [string]$RuleGroupName = "< "+$i.Rule+"."+$i.RuleNumber+" >"
    $Source = $i.Source
    $Destination = $i.Destination
    #Retrieve the list of source computers.
    [array]$SourceServersList = (Get-ADGroup -Filter * | Where-Object Name -like $Source | Get-ADGroupMember | Where-Object ObjectClass -EQ "computer").Name
    $SourceAddress = Get-ServerIPv4ListFromGroup -GroupName $Source
    #Retrieve the list of source computers. 
    [array]$DestServersList = (Get-ADGroup -Filter * | Where-Object Name -like $Destination | Get-ADGroupMember | Where-Object ObjectClass -EQ "computer").Name
    $DestinationAddress = Get-ServerIPv4ListFromGroup -GroupName $Destination
    Write-Host -ForegroundColor Cyan ("Rule #"+ $i.RuleNumber + ": " + $i.AllowOrBlock +" "+ $i.Description + " on Port(s) " + $i.LocalPorts + ", from: " + $Source + " to: " +$Destination)
    #Clear the existing rules (assuming the firewall rules were not defined as part of a different group name).
    Get-NetFirewallRule -CimSession $DestServersList -Group $RuleGroupName -ErrorAction SilentlyContinue | Remove-NetFirewallRule
    #The firewall rule is created, using the information provided in the CSV file.
    New-NetFirewallRule `
    -CimSession $DestServersList `
    -RemoteAddress $SourceAddress `
    -Action $i.AllowOrBlock `
    -Protocol $i.TrafficType `
    -LocalPort ($i.LocalPorts -split ";") `
    -Direction Inbound `
    -Profile Domain `
    -Name ($i.Description +": "+ $Source + " to " +$Destination+ " [" + $i.LocalPorts + "]") `
    -DisplayName ($i.Description +": "+ $Source + " to " +$Destination + " [" + $i.LocalPorts + "]") `
    -Description ("Rule #"+ $i.RuleNumber + ": " + $i.AllowOrBlock +" "+ $i.Description + " -on port(s) " + $i.LocalPorts + "- from the " + $Source + " to the " +$Destination) `
    -Group $RuleGroupName `
    -Enabled True -ErrorAction SilentlyContinue | Out-Null
    
    # Create bidirectional rules if this was specified in the Excel file.
    if ($i.Bidirectional -eq "Yes") {
        Write-Host -ForegroundColor Cyan ("Rule #"+ $i.RuleNumber + " (Bi-directional): " + $i.AllowOrBlock +" "+ $i.Description + " on Port(s) " + $i.LocalPorts + ", from: " + $Destination + " to: " +$Source)
        #Clear the existing rules (assuming the firewall rules were not defined as part of a different group name).
        Get-NetFirewallRule -CimSession $SourceServersList -Group $RuleGroupName -ErrorAction SilentlyContinue | Remove-NetFirewallRule
        #This is similar to the rule above, but the source and destination have been swapped.
        New-NetFirewallRule `
        -CimSession $SourceServersList `
        -RemoteAddress $DestinationAddress `
        -Action $i.AllowOrBlock `
        -Protocol $i.TrafficType `
        -LocalPort ($i.LocalPorts -split ";") `
        -Direction Inbound `
        -Profile Domain `
        -Name ($i.Description +": "+ $Destination + " to " +$Source + " [" + $i.LocalPorts + "]") `
        -DisplayName ($i.Description +": "+ $Destination + " to " +$Source + " [" + $i.LocalPorts + "]") `
        -Description ("Rule #"+ $i.RuleNumber + ": " + $i.AllowOrBlock + " "+ $i.Description + " -on port(s) " + $i.LocalPorts + "- from the " + $Destination + " to the " +$Source) `
        -Group $RuleGroupName `
        -Enabled True -ErrorAction SilentlyContinue | Out-Null
    }
}
To use the script above, copy it, and save it as a PS1 file. Be sure to update the default SourceFile parameter value or use the SourceFile parameter when calling it. For example, perhaps you've saved this script as New-WindowsFirewallRule.ps1 and saved the Excel workbook as a CSV file as rules.csv. You'd then call this script as shown below.
Running the script

C:\New-WindowsFirewallRule.ps1' -SourceFile 'C:\rules.csv'

100

Param(

[Parameter(Mandatory=$false)]$SourceFile = "C:\Temp\Firewall Rules from text file.csv" #The CSV file to import.

)

# This function returns the list of IPv4 addresses for one or more servers.

Function Get-ServerIPv4ListFromGroup {

#Requires -Modules ActiveDirectory,DNSServer

.SYNOPSIS

Get-ServerIPv4ListFromName returns the list of IPv4 addresses for one or more servers. The list is required since FQDNs and NetBIOS names cannot be added to simple firewall rules in Windows Defender Firewall.

.EXAMPLE

Get-ServerIpList -GroupName Server1,Server5 -DnsServer NS4

Returns the IP(v4) addresses of the servers Server1,Server5. The IP addresses are retrieved from the DNS server NS4.

.EXAMPLE

Get-ServerIpList -GroupName $DbServers

Returns the IP(v4) addresses of the server(s) in $DbServers. The DNS server queried for the IP addresses will be a domain controller in the user's domain.

.NOTES

Version: 1.0

Author: Emanuel Halapciuc

.EXAMPLE

'C:\New-WindowsFirewallRule.ps1' -SourceFile 'C:\rules.csv'

Param(

[Parameter(Mandatory=$true)][array]$GroupName, #List of computer(s) for which the IP address will be retrieved.

[Parameter(Mandatory=$false)][string]$DnsServer = (Get-ADDomainController).Hostname #If no DNS server is provided, the script will use a domain controller as a DNS server.

)

#Retrieve the list of computers.

[array]$ComputersList = (Get-ADGroup -Filter * | Where-Object Name -like "$GroupName" | Get-ADGroupMember | Where-Object ObjectClass -EQ "computer").Name

#Initialize the list of IP Addresses

[array]$IpAddressList = @()

#Populate $IpAddressList with the IP addresses of the computers in the $ComputersList

foreach ($c in $ComputersList) {

$IpAddressList += (Get-DnsServerResourceRecord -RRType A -Name $c -ComputerName $DnsServer -ZoneName $env:USERDNSDOMAIN -ErrorAction SilentlyContinue).RecordData.IPv4Address.IPAddressToString

}

return $IpAddressList

}

#Headers for each column. The column names in the original CSV are less relevant, but it makes things easier to follow.

$ColumnHeader = ("RuleNumber","Source","Destination","TrafficType","LocalPorts","Bidirectional","AllowOrBlock","Description","Rule")

#Import the CSV file (this is the $SourceFile whose path you provided earlier).

$ImportData = Get-Content -Path $SourceFile | Select-Object -Skip 1 | ConvertFrom-Csv -Delimiter "," -Header $ColumnHeader

# Once the data is imported from CSV, the script goes through each line (rule) to create the firewall rules.

foreach ($i in $ImportData) {

#Construct the rule group name.

[string]$RuleGroupName = "< "+$i.Rule+"."+$i.RuleNumber+" >"

$Source = $i.Source

$Destination = $i.Destination

#Retrieve the list of source computers.

[array]$SourceServersList = (Get-ADGroup -Filter * | Where-Object Name -like $Source | Get-ADGroupMember | Where-Object ObjectClass -EQ "computer").Name

$SourceAddress = Get-ServerIPv4ListFromGroup -GroupName $Source

#Retrieve the list of source computers.

[array]$DestServersList = (Get-ADGroup -Filter * | Where-Object Name -like $Destination | Get-ADGroupMember | Where-Object ObjectClass -EQ "computer").Name

$DestinationAddress = Get-ServerIPv4ListFromGroup -GroupName $Destination

Write-Host -ForegroundColor Cyan ("Rule #"+ $i.RuleNumber + ": " + $i.AllowOrBlock +" "+ $i.Description + " on Port(s) " + $i.LocalPorts + ", from: " + $Source + " to: " +$Destination)

#Clear the existing rules (assuming the firewall rules were not defined as part of a different group name).

Get-NetFirewallRule -CimSession $DestServersList -Group $RuleGroupName -ErrorAction SilentlyContinue | Remove-NetFirewallRule

#The firewall rule is created, using the information provided in the CSV file.

New-NetFirewallRule `

-CimSession $DestServersList `

-RemoteAddress $SourceAddress `

-Action $i.AllowOrBlock `

-Protocol $i.TrafficType `

-LocalPort ($i.LocalPorts -split ";") `

-Direction Inbound `

-Profile Domain `

-Name ($i.Description +": "+ $Source + " to " +$Destination+ " [" + $i.LocalPorts + "]") `

-DisplayName ($i.Description +": "+ $Source + " to " +$Destination + " [" + $i.LocalPorts + "]") `

-Description ("Rule #"+ $i.RuleNumber + ": " + $i.AllowOrBlock +" "+ $i.Description + " -on port(s) " + $i.LocalPorts + "- from the " + $Source + " to the " +$Destination) `

-Group $RuleGroupName `

-Enabled True -ErrorAction SilentlyContinue | Out-Null

# Create bidirectional rules if this was specified in the Excel file.

if ($i.Bidirectional -eq "Yes") {

Write-Host -ForegroundColor Cyan ("Rule #"+ $i.RuleNumber + " (Bi-directional): " + $i.AllowOrBlock +" "+ $i.Description + " on Port(s) " + $i.LocalPorts + ", from: " + $Destination + " to: " +$Source)

#Clear the existing rules (assuming the firewall rules were not defined as part of a different group name).

Get-NetFirewallRule -CimSession $SourceServersList -Group $RuleGroupName -ErrorAction SilentlyContinue | Remove-NetFirewallRule

#This is similar to the rule above, but the source and destination have been swapped.

New-NetFirewallRule `

-CimSession $SourceServersList `

-RemoteAddress $DestinationAddress `

-Action $i.AllowOrBlock `

-Protocol $i.TrafficType `

-LocalPort ($i.LocalPorts -split ";") `

-Direction Inbound `

-Profile Domain `

-Name ($i.Description +": "+ $Destination + " to " +$Source + " [" + $i.LocalPorts + "]") `

-DisplayName ($i.Description +": "+ $Destination + " to " +$Source + " [" + $i.LocalPorts + "]") `

-Description ("Rule #"+ $i.RuleNumber + ": " + $i.AllowOrBlock + " "+ $i.Description + " -on port(s) " + $i.LocalPorts + "- from the " + $Destination + " to the " +$Source) `

-Group $RuleGroupName `

-Enabled True -ErrorAction SilentlyContinue | Out-Null

}

To use the script above, copy it, and save it as a PS1 file. Be sure to update the default SourceFile parameter value or use the SourceFile parameter when calling it. For example, perhaps you've saved this script as New-WindowsFirewallRule.ps1 and saved the Excel workbook as a CSV file as rules.csv. You'd then call this script as shown below.

Running the script

C:\New-WindowsFirewallRule.ps1' -SourceFile 'C:\rules.csv'

To use the script above, copy it, and save it as a PS1 file. Be sure to update the default SourceFile parameter value or use the SourceFile parameter when calling it. For example, perhaps you've saved this script as New-WindowsFirewallRule.ps1 and saved the Excel workbook as a CSV file as rules.csv. You'd then call this script as shown below.

Running the script ^

C:\New-WindowsFirewallRule.ps1' -SourceFile 'C:\rules.csv'

1	C:\New-WindowsFirewallRule.ps1' -SourceFile 'C:\rules.csv'

Running the script

The script in action. You will see information (such as ports, destinations, and source servers) for each rule that was created.

Verifying the created rules ^

Once the script has finished, be sure to check for the new Windows Defender Firewall rules using PowerShell Get-NetFirewallRule or the Windows Defender Firewall console (wf.msc). You can see an example below of viewing the newly created rules in the Windows Defender Firewall GUI.

Firewall rules

In the screenshot above you see the created rules in the Windows Defender Firewall. You can then double-click a rule to verify that the rules were created by the script as intended. This is where the description comes in handy as shown in the screenshot below.

Rule details

Double-click a rule to see more information about it.

Prerequisites ^