HowTo - Export a Security Policy to Excel

[Danny]

If you'd like to export your Check Point security policy into Microsoft Excel, look no further.
Within Excel you can then easily sort rules by their hit count, first hit, last hit, you name it. So let's start.

1. Use Check Point's Show Package Tool to export your security policy to HTML
   • SSH login into the export mode of your SmartCenter and run $MDS_FWDIR/scripts/web_api_show_package.sh -n 443 -c
2. Copy the resulting .tgz to your Admin PC and unarchive to a new folder
3. Open the exported security policy in your web browser of choice
4. View the generated html via DOM inspect and save it into a new .html file
5. Remove unnecessary JavaScript code from that .html with your text editor of choice
6. Replace <div>, </div> and </div></td>with #!%!#
7. Open the .html file with a web browser and copy all content to your clipboard (crtl-a, crtl-c)
8. Create an empty Excel sheet and paste your clipboard to it (crtl-v)
   • Replace the string #!%!# with crtl-j (creates newlines within the same row)
   • Remove any groupings to make sorting functional
   • Adjust formatting according to your needs (freeze title pane, hide section titles to allow sorting by hit count etc. etc.)
9. Repeat the steps above for all your security policies

Want to add first hit and last hit columns to your Excel sheet? Here you go:

1. Unarchive the .tgz from step 1 above into a new directory within expert mode on your SmartCenter Server
2. Run the following one-liner on the extracted policy.json file
   • cat policy.json|tr ',' '\n'|egrep -A6 hits|tr -d '"{}'|sed -e '/hits:level:zero/,/--/c\hits:level:zero\nfine\n--'|egrep 'first-date|last-date|posix|fine'|tr -d '\n'|sed 's/fine/\<\/td\>\<\/tr\>\n\<tr\>\<td\>-\<\/td\>\<td\>-\<\/td\>\<td\>-\<\/td\>\<td\>-/g'|sed 's/first-date:iso-8601:/\<\/td\>\<\/tr\>\n\<tr\>\<td\>/g'|sed 's/posix:/\<\/td\>\<td\>/g'|sed 's/last-date:iso-8601:/\<\/td\>\<td\>/g'
3. Copy the output from step 2 into a new .html file and open it with a Web browser on your Admin PC
4. Copy the web content into your clipboard (crtl-a, crtl-c)
5. Paste your clipboard into two new colums within your Excel sheet (crtl-v)
6. Adjust formatting and make sortable
7. Verify result > Done!

Want a video that documents the described procedure? Like this thread and I'll create one.

[the_rock]

Thanks Danny, very nice! Man, if you could hit us with a video, that would be superb!

Andy

[_Val_]

Nice! Add the video, @Danny 

[Duane_Toler]

Interesting. 

Instead of multiple pipelines to sed, you can use '-e' to chain the expressions:

sed -e '/blah/,/foo/s/foo/bar/g' -e '/match/s/thing1/thing2/g' -e 's/etc/var/g'

[genisis__]

Nice Danny!

I assume this may need R81.10 so it actually has hit count info, does it also deal with hit count on NAT policy? and a Video would be awesome!

[George_Ellis]

The only issue I find with this is both disabled rules and negated sections are not carried over.  In the HTML, the row is highlighted with at tr description.  These are shown as:

<tr class="disabled_rule"><td class="rule_number">1</td>

.disabled_rule {
color: lightgray;
}

.disabled_rule .rule_number:after {
content: "\A[Disabled]";
display: block;
}

.negate:before {
content: "---Negated---\A";
display: block;
color: red;
}

.negate:after {
content: "------------------";
display: block;
color: red;

That yields a grey row with disabled following it.  It does not change data "1".  So there is no indication that the first rule is disabled (or any sections are negated).

  

Still trying to figure out if I can use the data query tool to tie in the objects and security file jsons to yield a result.

[dunkelmorten]

Thank you very much. Nice approach.
Tried this on MDS but when comparing output with Hit Counts of the rule set it doesn't seem to match.

Turn it to CSV with ; as delimeter.
Having a smaller rule set with 312 rules, checking rule 305 examplaric it is showing: First Hit 05.12.2022 08:20, Last Hit 02.12.2024 12:28
The one liner is returning 312 lines but checking line 305 is showing: 2022-08-26T12:45+0200;1661510725000;2024-12-02T11:53+0100;1733136802000;

Any idea on this?

[Danny]

@dunkelmorten : I sent you a PM. Let's check this together. Do you have any global rules in place?

[dunkelmorten]

There were no global rules in place in this policy, but on other policies in the same domain.

Global object usages were already replaced by local objects across this policy, but other policies in this domain are still using global objects. But I guess as long as the affected policy is not using any global stuff it doesn't matter at all, isn't it?

[dunkelmorten]

Thank you  very much for your support on this.

We have identified that the number of rules is matching and the first and last hits seem to be correct.

It was confusing that the SmartConsole is showing a later first hit than the "Show Package Tool" exports and also different counters.

Example:
Rule 1 in Smart Console
- Hits: 6
- First Hit: 20.07.2023
- Last Hit: 10.03.2024

Rule 1 in Show Package Tool Export:
- Hits: 8
- First Hit: 25.08.2022
- Last Hit: 10.03.2024

Last Hit seems to match for all rules but not the First Hit entries. Show Package Tool seem to have a longer history, thus, may also show higher hit counts related to the longer history.

[Danny]

I'm glad I could be of help.
It feels good to see taht my oneliner still works and is useful to people.