topic Re: HowTo - Export a Security Policy to Excel in API / CLI Discussion

HowTo - Export a Security Policy to Excel

Danny — Tue, 14 Nov 2023 17:53:34 GMT

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.

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
Copy the resulting .tgz to your Admin PC and unarchive to a new folder
Open the exported security policy in your web browser of choice
View the generated html via DOM inspect and save it into a new .html file
Remove unnecessary JavaScript code from that .html with your text editor of choice
Replace <div>, </div> and </div></td>with #!%!#
Open the .html file with a web browser and copy all content to your clipboard (crtl-a, crtl-c)
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.)
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:

Unarchive the .tgz from step 1 above into a new directory within expert mode on your SmartCenter Server
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'
Copy the output from step 2 into a new .html file and open it with a Web browser on your Admin PC
Copy the web content into your clipboard (crtl-a, crtl-c)
Paste your clipboard into two new colums within your Excel sheet (crtl-v)
Adjust formatting and make sortable
Verify result > Done!

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

Re: HowTo - Export a Security Policy to Excel

the_rock — Wed, 20 Oct 2021 23:21:42 GMT

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

Andy

Re: HowTo - Export a Security Policy to Excel

_Val_ — Thu, 21 Oct 2021 07:00:53 GMT

Nice! Add the video, @Danny

Re: HowTo - Export a Security Policy to Excel

Duane_Toler — Mon, 25 Oct 2021 17:31:13 GMT

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'

Re: HowTo - Export a Security Policy to Excel

genisis__ — Mon, 25 Oct 2021 19:50:23 GMT

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!

Re: HowTo - Export a Security Policy to Excel

George_Ellis — Fri, 06 Jan 2023 16:59:27 GMT

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:

.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.

Re: HowTo - Export a Security Policy to Excel

dunkelmorten — Mon, 02 Dec 2024 15:14:32 GMT

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?

Re: HowTo - Export a Security Policy to Excel

Danny — Mon, 02 Dec 2024 15:30:35 GMT

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

Re: HowTo - Export a Security Policy to Excel

dunkelmorten — Mon, 02 Dec 2024 15:35:45 GMT

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?

Re: HowTo - Export a Security Policy to Excel

dunkelmorten — Tue, 03 Dec 2024 11:38:00 GMT

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.

Re: HowTo - Export a Security Policy to Excel

Danny — Tue, 03 Dec 2024 09:03:04 GMT

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