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.

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

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

Firewall rules in Excel

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

  • Rule numberYou may pick whatever you want, but the rule group name will also include this (see Rule Group below).
  • Source Servers groupWhere traffic will originate from.
  • Destination Servers GroupWhere the rules will be created.
  • Traffic TypeTCP, UDP, Any
  • Destination PortIf 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 descriptionOptional, 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 GroupThis 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

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

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

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.

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 ^

Running the script

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

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

Rule details

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

Further reading ^

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

  1. olivier 3 weeks ago

    Hi Emmanuel,

    Some comments I want constructive :

    • Avoid Backtick, use splatting
    • In many organization Servers have many Network Interfaces dedicated for a goal (i.e. "Production" for end-users access, "Service" used by Admin only, "Backup" used only for backup). Only the Production Network Interface has a record in the DNS. In this case, the script doesn't reach the goal.
    • Then you could query the server to get the Network Interface, but careful, some Network cards are not physical but virtual (i.e. teaming card). The criteria to select a network card should be "IPv4Address not empty"
    • The time to prepare the .csv file is the big challenge and it must not neglected. It's 80% of the job
    • This script could be useful in some cases probably (I'm thinking this : 1 master for servers, but FW rules diffrents depending of the server role. Using the script during the deployment), but, personally i prefer to do the same with Group Policy cause GPO will be re-applied regularly. If there are some changes, this come from Admin User Group. In  many Organization, there are many Admin Support Group (i.e. : Admin Tiers-1, admin Tier-2, Admin-Tier3) but only one could create/modify GPO. Then this limit inappropriate changes.
    • A last thind : How I appreciate to read the property "Description" filled. EveryDay I say "this property is a master piece to maintain operational an infrastructure".





    • Author

      Hi Oliver.

      Wow, thanks for the comments and feedback. There's a lot of it, and it's much appreciated.

      Let me try to answer to some bits:

      • Splatting is a very good suggestion, and I've started to use it more and more often. I always preach about it to others, but... I'm still guilty of not doing it myself from time to time.
      • I'm aware that servers usually have more than one NIC (usually the front-end ones), and yes, this would certainly be an area of improvement. However, I still care about DNS everywhere. If anything else, for Kerberos authentication. You can't have that using a server's IP instead of hostname/FQDN. That aside, I might change the script at some point to pick a specific NIC. And yes, having an IP address is a good requirement for creating a firewall rule, although there are many ways to select a certain NIC when you have more than one.
      • I'd say creating the Excel file is more time-consuming, saving it as CSV is not that difficult. It takes a bit longer to create lists of all the groups, but after that it requires little maintenance, though. And the traffic rules would be filled in by other than you, ideally (my whole idea is that the application owner or architect should fill it in, not the sysadmin).
      • I understand your preference for GPOs, and I agree with it. However, there are cases (few, admittedly) where the servers are not domain members, and (and this is my main objection, really) the GPOs creation is not as easy to automate. 
      • I don't understand the last bullet. If it was appreciation for using descriptions, thank you. 🙂 I prefer to use them whenever possible (AD Groups, special user accounts, computer accounts). I also use them heavily in GPOs, for trickier settings and also I put a description for the GPO itself (it's not very intuitive, but lovely to do and have). If it wasn't a compliment but a criticism/ objection, please clarify. I developed the habit to take criticism and complaints as compliments, it makes corporate life so much easier. However, sometimes it backfires. smiley

      Once again, thank you for the comments and suggestions/hints for improvement. Cheers! Emanuel


  2. Olivier 3 weeks ago

    Hi Emanuel

    My last remark was not a "bullet", just I really appreciate when the description Property is filled. How many times i've lot of groups with similar names (naming convention) but I had to scratch my head to figure out what exactly this group is used (looking at the group type Universal, Global or Domain Local, and MemberOf and Members)

    Sorry for my poor english, it's not my native language (it's not an excuse, just an explanation) 🙂




    • Author

      Hi, Olivier.

      Thanks for the clarification. Don't worry, English is not my native language, either. And yes, I do like to use descriptions. I find them as important as comments in scripts. 

      For groups you can use a prefix or suffix like U-Something, Something-DL, etc.

      Fortunately, I have been blessed with working in single-domain environments. But I still favour/use group suffixes like -RO and -RW for read-only or read-write access. It just makes life a bit easier for me. 

      Cheers. Emanuel


Leave a reply

Please enclose code in pre tags

Your email address will not be published. Required fields are marked *


© 4sysops 2006 - 2021


Please ask IT administration questions in the forums. Any other messages are welcome.


Log in with your credentials


Forgot your details?

Create Account