How to manage traffic rules in Windows Firewall from an Excel Sheet

Emanuel Halapciuc

Emanuel Halapciuc

Read more posts by this author.

Setting up lots of Windows firewall rules is terribly time-consuming. It’s not exciting and is prone to errors due to all of the clicking around in the menus. Instead, why not automate this process! In this article, you’re going to learn how to quickly and easily add ports to the Windows 10 (and other OSes) firewalls using Excel and a simple CSV file as input.

Why a CSV file, you ask? In the article, we’ll be using PowerShell to create Windows firewall rules. PowerShell, as it turns out, has a handy cmdlet called Import-Csv that allows you to easily read CSV files. Not only that, if you need to collaborate with others on the expected ports to open, you can share this CSV file with your team and have them edit it themselves.

Let’s jump into how you can create Windows firewall rules using a CSV file with PowerShell!

Prerequisites

This blog post is going to be a how-to article. If you’d like to follow along, please 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 either installed locally or Excel Online

Using an Excel Workbook

You’re going to need input to the PowerShell script that includes all of the Windows firewall rules to create. To do that, you can use Excel. You can create your own CSV file or, you can save yourself some time and download a pre-created Excel workbook for you.

The Excel workbook has two main worksheets; Rules and Server Groups as you can see below.

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 to show examples. Each row indicates represents one Windows firewall rule.

Excel Cells with Data Validation
Excel Cells with Data Validation

If you click around in the rows, you’ll see that many of the cells have Excel’s data validation feature enabled on them. Data validation allows you to simply click on a cell and select from a list of pre-defined values.

Data Validation List
Data Validation List

Go ahead and remove all of the examples rows if you wish and add your own following the same patterns in the example rows. You’ll soon learn how to build a script that reads this data and builds the Windows firewall rules you’re defining.

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, they can be separated by a semicolon. Or a dash, in case there is a range of ports. It is important to use a semicolon (for the CSV structure, and for preventing Excel from converting the values to numbers).
  • Bi-directional rule? – Here you can specify if traffic needs to be allowed in both directions
  • Allow? – (or block) traffic. Most often this will be Allow, but the option Block is available, too. For example, you may want only your web servers to communicate with the database servers, and prevent Client Computers from doing so.
  • Traffic description Optional, but useful. This will populate the description for each rule. It may be useful in 6 months when you wonder why you created that rule (or tomorrow, for your colleagues)
  • Rule Group This is a name that allows you to easily sort through all the firewall rules. 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 allows you to define the names of the Active Directory groups you have the servers you’ll be remotely targeting to create these Windows firewall rules on.

Server Groups
Server Groups

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

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

Source Server Group
Source Server Group

Example usage: Perhaps you have a web application that runs on database servers, web servers, and cache servers. You most likely need to restrict the traffic flow between 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. In this case, in the Server Groups worksheet, you can create the Groups 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

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

Save the CSV file. Your interface may look a bit different, depending on your version of Excel.
Save the CSV file. Your interface may look a bit different, depending on your version of Excel.

Finally it’s time to use a bit of magic PowerShell generate the Firewall rules. Don’t worry, the longest part is already over!

Building the PowerShell Script

Now that you have the properly-structured CSV file built, it’s now time to create the PowerShell script to read it and to automate creating all of those Windows firewall rules.

The script provided below performs the following:

  • Imports the CSV file of firewall rules
  • Generates the lists of servers, based on the group names
  • Generates a list of the remote IP addresses based on the function Get-ServerIPv4ListFromGroup included in the script. IP addresses are retrieved from a DNS server (if you specify one) or from a Domain Controller (which is usually a DNS server) if no DNS server was specified.
  • For each rule, it first deletes the existing rule from the destination servers in case the rule already exists and has been changed since the last time the rule has been created and creates it again. It then creates the rule, line by line.
  • If there is a bi-directional rule, it will create two “mirror” rules (both on destination and on the source servers).
#requires -Modules ActiveDirectory,DNSServer

# $SourceFile is the csv file you created earlier. You need to provide a valid path to the file.
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 {
    <#
    .SYNOPSIS
    Get-ServerIPv4ListFromName returns the list of IPv4 addresses for one or more servers. The list is required since FQDNs or NetBIOS names cannot be added to simple firewall rules in Windows Firewall
    .EXAMPLE
    Get-ServerIpList -GroupName Server1,Server5 -DnsServer NS4
    Returns the IP(v4) addresses of the servers Server1,Server5. The IP Addresses will be 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
    Last revision on 21 Dec 2020
    #>
    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 bi-directional 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 similare 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 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 that creates the firewall rules from the CSV file
$ 'C:\\New-WindowsFirewallRule.ps1' -SourceFile 'C:\\rules.csv'
The script in action. You will see information (such as ports, destination and source servers) for each rule that was created.
The script in action. You will see information (such as ports, destination 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 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.

The rules, as they were created in Windows Defender Firewall. This is where the description comes in handy.
The rules, as they were created in Windows Defender Firewall. This is where the description comes in handy.

You can then double-click a rule to verify the rules just created by the script were as intended.

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

Further Reading

If you want to learn a bit more about creating Windows Firewall rules from PowerShell, check out the official documentation:

Subscribe to Adam the Automator

Get the latest posts delivered right to your inbox

Looks like you're offline!