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.