topic Re: Management API - Rulebase export as .csv in API / CLI Discussion

Management API - Rulebase export as .csv

Maik — Fri, 08 Jun 2018 12:18:31 GMT

Hello,

Is it possible to use the management API to export the rulebase as a .csv file instead of the json format?

I'm thinking about a similar layout as the option within the GUI. So within the "Security Policy" section after you open a policy there is the "Actions" button at the top. After clicking it you are presented with a menu which in the end lists the "export" option:

The exported .csv file from a previously selected rulebase is exactly what I am looking for - with the exception that I want to create it automatically and not via the GUI.

Hopefully there is a possible solution and somebody can help me.

Best regards,

Maik

Re: Management API - Rulebase export as .csv

Robert_Decker — Fri, 08 Jun 2018 13:02:34 GMT

Here you go -

https://community.checkpoint.com/docs/DOC-1089

Robert.

Re: Management API - Rulebase export as .csv

Maik — Fri, 08 Jun 2018 13:11:45 GMT

Many thanks!

Re: Management API - Rulebase export as .csv

Maik — Mon, 11 Jun 2018 07:17:48 GMT

Today I was able to test the script, but unfortunately it does not work as expected.

I put it directly on the management server itself, however, it throws a KeyError each time I try to execute it.

The output below shows what I enter and also the error code which is shown afterwards. Please notice that I changed the input by removing the IP address as well as the username and the rulebase name. Those parameters shouldn't be the cause of my issue, as the login appears to fine & the rulebase that I want to export as a csv can be found (I verified this by entering a wrong rule base name and received a different error which said that the rule base could not be found).

./rulebasecsv.py -o test.csv -u [MYUSERNAME] -t access -m [MGMT_SERVER_IP] -i [MY_RULEBASE_NAME]

Enter password:

Traceback (most recent call last):

File "./rulebasecsv.py", line 392, in <module>

main()

File "./rulebasecsv.py", line 68, in main

elif args.type[0] == "access": print_access_control_rulebase_csv(rulebase, session, args.output[0])

File "./rulebasecsv.py", line 252, in print_access_control_rulebase_csv

access_control_rulebase_csv(get_rulebase("access", rulebase, session), session, output_file)

File "./rulebasecsv.py", line 260, in access_control_rulebase_csv

print(print_access_rule(rule, parent_prefix=parent_prefix, enabled=enabled), file=outputfile)

File "./rulebasecsv.py", line 267, in print_access_rule

string += print_field_csv(rule["rule-number"], parent_prefix=parent_prefix, enabled=(enabled and rule["enabled"]))

KeyError: 'rule-number'

I tried several rule bases but the "KeyError: 'rule-number' is shown for each one. I also tried to change the IP address to 127.0.0.1 instead of the actual IP of the servers interface (as I am running this script locally from the management server), but this does not change anything as well. The management server runs version 80.10 and Python version 2.7.

Hopefully somebody experienced this issue before and can help me.

Thanks in advance for any advice!

Regards,

Maik

Re: Management API - Rulebase export as .csv

Robert_Decker — Mon, 11 Jun 2018 07:56:31 GMT

Hi Maik,

I'll check the script and inform you.

Robert.

Re: Management API - Rulebase export as .csv

Robert_Decker — Mon, 11 Jun 2018 13:10:03 GMT

Found a bug in source code. This code is two years old...

Will fix and update.

Robert.

Re: Management API - Rulebase export as .csv

Robert_Decker — Mon, 11 Jun 2018 14:52:12 GMT

Fixed.

Please download and run again.

Robert.

Re: Management API - Rulebase export as .csv

Maik — Tue, 12 Jun 2018 07:20:00 GMT

Hello Robert,

First of all I want to thank you very much for your effort and help!

Unfortunately I did experiece a few more bugs but was able to fix one of them.

So to start with, in the beginning when I used your new version of the script I received the following error:

"UnicodeEncodeError: 'ascii' codec can't encode character u'\xfc' in position xxx: ordinal not in range(128)"

Due to my (to this day) medium experience with python I was able to handle this one. So the error mentioned that the character "ü" could not be printed. The weird thing for me was, that "ü" or any other special character was not mentioned in any rule at all. Nevertheless, the error mentioned the "print_access_rule" method for the return error. Therefore I added ".encode("utf-8") to line 283 of the script, so that the returned value of the method print_access_rule is encoded in utf-8 which has a more complex characterset after all. (Of course this should also be done for the other two available export options/their related methods: print_nat_rule + print_threat_rule)

But now I experience the problem that only the first 50 rules of any tried rulebase are getting exported. After that the script simply stops exporting more. I tried several rulebases and also created a new one for testing purposes with just 51 rules - and also here, the export stops after rule 50. Unfortunately I am not so familiar with the whole script structure and could not find the related issue in the code. But I guess the solution of this one could be pretty simple for someone who knows the whole script. Maybe you can have a look once more - I promise that I won't bother you any more after this is working Really appreciate your support so far!

Best regards,

Maik

Re: Management API - Rulebase export as .csv

Robert_Decker — Tue, 12 Jun 2018 09:18:39 GMT

Hi Maik,

The magic 50 is the default limit when querying objects/rules in API.

I'll fix it to return all data, and also the unicode problem.

You are not bothering me - it is your full right to ask and it is my job to answer.

Robert.

Re: Management API - Rulebase export as .csv

Robert_Decker — Tue, 12 Jun 2018 09:39:49 GMT

Maik,

This fix may take some time, so if you have less than 500 rules in your policy, you can have a quick and dirty fix, as follows:

in method "get_rulebase" on lines 243 and 244, add this parameter to the "data" container -

"limit" : 500

This will return up to 500 rules.

Robert.

Re: Management API - Rulebase export as .csv

Maik — Tue, 12 Jun 2018 10:56:17 GMT

Hello Robert,

Thanks for your answer and suggestion!

The addition of the limit parameter within the data container did the job for now.

All the rules are getting exported (I used the value 1000 instead of 500, not recommended I know, but the rulebase is quite big and sits on top of a 64000 appliance). Now that I was able to create a first csv files via the script I saw, that new lines are getting exported in the rule strings as well. This means, that if a rule has e.g. a comment which contains the following information...

"Comment...

Comment...

Comment!"

...the related entry in the csv file will have "Comment" in the same line as the rest of the rule, but "Comment..." and "Comment!" in the next two lines. Such a format makes the csv file itself quite unusable. Maybe you can also check the for new lines and basically ignore them when writing the csv file, so that each rule is really represented as only one line within the csv?

I am starting to feel really bad for all the requests I have, don't hesitate to tell me if there is any way I can help with the script modification.

Regards,

Maik

Re: Management API - Rulebase export as .csv

Robert_Decker — Tue, 12 Jun 2018 16:57:23 GMT

Maik, no problem, don't feel uncomfortable.

I'll look at it tomorrow at work.

Robert.

Re: Management API - Rulebase export as .csv

Maik — Thu, 14 Jun 2018 09:45:52 GMT

As a temporary solution I have updated the script to also handle the issue with new lines, tabs and commas within the comment section of rules.

I guess in the eyes of a developer my solution is not the prettiest one, but for now it works.

Nevertheless one important feature that still does not work is the verification of the actual rule numbers and the related, correct, polling. For now my changes to the script just poll the first 4000 rules which currently is more than enough to achieve my plans (seems like the counter starts from 0 for each sub layer - maybe you can verify that?).

By the way, in addition one more thing that I added is that the exported csv file now also contains the custom fields of the rules. If you want to change the "header" of the csv to represent the correct name of the fields (as used in the individual envorinemnt) you need to change them correspondingly in the method "print_access_control_rulebase_csv" (no other changes needed as the name of these fields is always "field-1" etc. within the actual json data that the api is sending).

So long story short, here is my version of the script with the changes that I have added, maybe it is helping someone during the time window of the official script change:

Regards,

Maik

Re: Management API - Rulebase export as .csv

Robert_Decker — Thu, 14 Jun 2018 11:15:52 GMT

Hi Maik,

Kudos for the effort!

Some usefull information:

1. You cannot enter new-line/tab into rule comment in GUI, it is blocked, and GUI replaces"," with ";" when exporting the rulebase into CSV file.

2. The maximum limit for rules query in rulebase is 500, the offset starts at 0, so you can query in loop until the total is reached (advance the offset by the limit).

Robert.

Re: Management API - Rulebase export as .csv

Thomas_Arrowsmi — Thu, 14 Jun 2018 18:23:30 GMT

After the API you can send to jq to further parse it. pipe jq then at the end use jq to create a csv.
|jq 'some-example-of-filter)|@csv'

For example, one I use for printing out unused rules that have not been modified within the last 6 months:

mgmt_cli show access-rulebase name "$NAME" use-object-dictionary true show-hits true --format json|jq -r '.rulebase[].rulebase[]|select(.hits.value == 0)|select(."meta-info"."last-modify-time".posix <$POSIX)| (."rule-number", ."name", ."uid", ."meta-info" ."last-modify-time" ."iso-8601")|@csv'

Re: Management API - Rulebase export as .csv

RPdeBeer — Fri, 13 Dec 2019 09:04:21 GMT

Hi,

In R80.30 i receive the following error:

[Expert@MGMT:0]# ./export_rulebase_csv.py -o test.csv -u admin -t access -m 192.168.1.2 -i Standard -d
Enter password:
Traceback (most recent call last):
File "./export_rulebase_csv.py", line 401, in <module>
main()
File "./export_rulebase_csv.py", line 66, in main
session = connect(ip_address=management, username=username, password=password, session_id=session_id, readonly="true", fingerprint=fingerprint, verify=(False if management == "127.0.0.1" else not args.ignore_ssl))
File "./export_rulebase_csv.py", line 233, in connect
session.login(username=username, password=password, session_id=session_id, readonly=readonly)
File "./export_rulebase_csv.py", line 159, in login
self.headers["X-chkp-sid"] = api_call(command="login", session=self, data={"user": username, "password": password, "continue-last-session": "false", "read-only": readonly}, level=level)["sid"]
File "./export_rulebase_csv.py", line 173, in api_call
body = json.loads(body)
File "/opt/CPsuite-R80.30/fw1/Python/lib/python2.7/json/__init__.py", line 339, in loads
return _default_decoder.decode(s)
File "/opt/CPsuite-R80.30/fw1/Python/lib/python2.7/json/decoder.py", line 364, in decode
obj, end = self.raw_decode(s, idx=_w(s, 0).end())
File "/opt/CPsuite-R80.30/fw1/Python/lib/python2.7/json/decoder.py", line 382, in raw_decode
raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Could you please help me to solve this?

Thank you in advance!

Re: Management API - Rulebase export as .csv

PhoneBoy — Fri, 13 Dec 2019 16:10:30 GMT

That looks like you haven't enabled the API server properly.
See: https://community.checkpoint.com/t5/API-CLI-Discussion-and-Samples/Enabling-web-api/m-p/32641#M2011

Re: Management API - Rulebase export as .csv

RPdeBeer — Fri, 13 Dec 2019 16:30:17 GMT

Hi,

I restarted the API but that solved partially the problem. I receive now the following errors:

[Expert@MGMT:0]# ./export_rulebase_csv.py -o test.csv -u admin -t access -m 192.168.1.2 -i standard
Enter password:
Please wait, the script is currently creating the csv file of your choice: access-rulebase...
|--- >>> Error occured: Requested object [standard] not found. Error type: generic_err_object_not_found.
|--- Headers: {'transfer-encoding': 'chunked', '_reason': 'Not Found', 'strict-transport-security': 'max-age=31536000; includeSubDomains', '_version': 11, 'server': 'CPWS', 'x-ua-compatible': 'IE=EmulateIE8', 'date': 'Fri, 13 Dec 2019 16:25:47 GMT', 'x-frame-options': 'SAMEORIGIN', 'x-forwarded-host-port': '443', 'content-type': 'application/json', '_status': 404}
|--- Body: {u'message': u'Requested object [standard] not found', u'code': u'generic_err_object_not_found'}
Script aborted.
All objects were restored to their previous state.

Standard is the name of the rulebase, but it seems like he is not able to find this object..

Greetings, Rutger

Re: Management API - Rulebase export as .csv

PhoneBoy — Fri, 13 Dec 2019 18:24:57 GMT

Try Standard with a capital S.
Some API calls are case-sensitive.

Re: Management API - Rulebase export as .csv

RPdeBeer — Sat, 14 Dec 2019 18:29:36 GMT

I already tried that 🙂 Didn't solve the problem...