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:

palo_xml_import

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.

Step_1

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:

Step_2

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

Step_3

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.

Advertisements

About sitweak
Monitoring, Network, Firewall, Mobile Security. I´m totally into that stuff!

11 Responses to Importing Palo Alto Firewall´s Policies into Microsoft Excel

  1. Anonymous says:

    Very informative post, thank you very much for the write up.

    How about to export NAT rules and service objects?

    • sitweak says:

      Hey there,

      I actually never tried to import these objects. I assume it would work in the same way, however I am not in touch with Palo Alto firewalls anymore ;( Sorry. A good source for questions like this is the official Palo Alto community.

  2. Anonymous says:

    I got it, search and , and etc…, Thank you very much again. 🙂

  3. A big thank you goes to OP. This worked flawlessly and will help me immensely going forward as both a way to document the current rule set and provide data to other network teams. Superb work laying this out as I would’ve never figured this out on my own.

  4. nostromo says:

    Nice! Thank you for putting this up, it was very helpful to me. In my case after a few minutes of mucking around I found that what I needed to do a find and replace on for step 8 was 20 spaces (replaced with nothing).

  5. Anonymous says:

    Nice, thanks!

  6. Anonymous says:

    Thank you very much this is a great help on my part!

  7. Anonymous says:

    thanks for this write up but im still not sure what to do. shows up more than once

  8. Anonymous says:

    Thanks, very useful

  9. Philipp says:

    Thank you very much! This information helped us a lot!

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: