Importing Palo Alto Firewall´s Policies into Microsoft Excel

 

Last week I started my second approach on importing the Policies (Ruleset) from our PaloAlto firewall into Microsoft Excel. I really love the way PaloAlto designed the web-interface on their Pan-OS: It has a cross-browser compatibility and works flawless on any screen resolution. Unfortunately, it is missing an option to export the policies into any standardized form, which would give me the possibility to keep a record of the ruleset outside the web-interface.

However, this was something I wanted to do for quite a while. More and more often we have the problem that system administrators complain about a missing transparency of the ruleset. This problem usually comes up when they are deploying a new service, and there is exactly one policy missing in order to complete the deployment.

One solution for that problem is granting a read-only access on the firewall. Downside: They have to be taught on how to use the web-interface and how to correctly read the policies. More important the will have access to other areas of the configuration, which may be considered sensitive or conflict with the goal of protecting sensitive data.

Presenting the ruleset in an Excel sheet is a good solution in order to address these problems: Everyone is able to open and read an Excel-sheet (Freeware viewers are also available). As a plus you can hide or exclude any information that is not necessary and likely people will know how to find the information they are looking for. On the other hand you have endless options on how to add additional information with the help of texts or conditional formatting.

Anyhow, enough for now – let´s start with the solution.

A while ago I already found two articles in the PaloAlto forums on how to import the Policies into Microsoft Excel. One is here: https://live.paloaltonetworks.com/docs/DOC-1617, the other one you will find here: https://live.paloaltonetworks.com/docs/DOC-5754. Unfortunately, both solutions didn´t work for me.

In fact, I got the following result using the tutorials:

As you can see, there is a new column for every source and target address. The reason is, that the every address-object is enclosed by <member> tags, which leads to a misinterpretation in the structure of the XML-file.

The advice from my co-worker was to use formulas in order to fix the layout. This didn´t seem to be the perfect solution for me, since I wanted a procedure which could be eas

ily reproduced by any other firewall-administrator.

After checking a few of the comments under the above stated links I found a comment mentioning a solution based on a linux bash command. After analyzing the command line I could adopt the procedure to a windows environment. Here is what you have to do (tested on Pan-OS 5.X with PAN-5050 and Google Chrome browser):

1)      Export your current running configuration:  In the web-interface you go to Device -> Setup -> Operations -> Export named configuration snapshot.

2)      Open the configuration snapshot with a compatible text-editor (as Notepad++ for example).

3)      Search for string <security> (press Strg+H in most ext-editors) and deleted everything before the tag.

4)      Search for string </security> (press Strg+H in most ext-editors) and deleted everything after the tag. You now should have everything between <security> and </security>. Save the file (for security ;))

5)      Delete all tags <member> and </member>: Press Strg+H again for search and replace. Search for string <member> and replace it with nothing (delete it!). Do the same for the string </member>.

6)      Save the file as an XML document.

7)      Open Excel and import the XML file by clicking: Data -> Import –> other Sources –> XML-Dataimport and choose the XML file.

8)      As a result you should see your complete ruleset, where every rule is in exactly one row. However, there were slight layout problems caused by blanks in front of the address objects:

You can easily fix that by using the replace-function again, and replace the blanks with nothing:

As a side-note:  You can use that procedure also for importing the address-objects of your PAN-Firewall. In that case you have to import everything between <address> and </address> tags.

Side-Note 2: For additional XML settings you may want to activate the developers tab in Excel. It offers you additional features when working with XML data.