- Products
- Learn
- Local User Groups
- Partners
- More
CheckMates Fifth Birthday
Celebrate with Us!
days
hours
minutes
seconds
Join the CHECKMATES Everywhere Competition
Submit your picture to win!
Check Point Proactive support
Free trial available for 90 Days!
As YOU DESERVE THE BEST SECURITY
Upgrade to our latest GA Jumbo
The 2022 MITRE Engenuity ATT&CK®
Evaluations Results Are In!
Now Available: SmartAwareness Security Training
Training Built to Educate and Engage
MITRE ATT&CK
Inside Check Point products!
CheckFlix!
All Videos In One Space
This CLI API example addresses the answer I was provided to my question, "Is there a way [easy would be nice], to get the output from a show command to generate a csv output and not a xml style text or json format?"
Sensei Uri Bialik provided this excellent example with an overview of how it works, which can be very helpful in designing mgmt_cli show queries in the Gaia CLI [bash] for exporting select configuration values to a CSV file for later import.
The following command:
mgmt_cli show services-tcp details-level full limit 500 –root true --format json | $CPDIR/jq/jq '.objects[] | [ .["name"], .["color"], .["comments"], .["port"], .["meta-info"]["last-modifier"] ] | @csv' -r
Would generate the following csv output:
"MS-SQL-Server","magenta","Microsoft SQL Server","1433","System"
"Yahoo_Messenger_Voice_Chat_TCP","black","Yahoo Messenger Voice Chat","5000-5001","System"
"Bionet-Setup","magenta","Also used by: Blazer5 , Bubbel and Back-door trojans","5000","System"
"DNP3","sienna","DNP3 is a set of communications protocols used between components in process automation systems. Its main use is in utilities such as electric and water companies","20000","System"
"indeni","black","Communicate with indeni (www.indeni.com)","8181","System"
"FW1_sds_logon_NG","firebrick","SecuRemote Distribution Server Protocol (VC and higher)","65524","System"
"SocketsdesTroie","deep pink","Also used by the: tcpmux service","1","System"
"SubSeven-G","magenta","Also used by: Tiles and Backdoor_g trojans","1243","System"
"Mneah","magenta","Mneah trojan","4666","System"
"SIC-TCP","black","SIC TCP service","18190-19191","System"
"HTTPS_proxy","navy blue","","8080","System"
"Napster_directory_7777","black","Napster directory connections","7777","System"
"Backage","deep pink","Backage trojan","411","System"
"CheckPointExchangeAgent","black","","18301","System"
"Terrortrojan","magenta","Terror trojan","3456","System"
"OPC","sienna","OPC specifies the communication of real-time plant data between control devices from different manufacturers","3480","System"
"Yahoo_Messenger_Webcams","black","Yahoo Messenger Webcams video","5100","System"
.
.
.
Although the command is a short one I would not go and call it “easy”…
Anyway this is a very detailed explanation of how this works:
This command outputs the list of the first 500 tcp service objects with the maximum level of details.
I used “--root true” instead of supplying the username and password because I was running the command as root user in expert-mode and as a root user I don’t have to provide username and passwords.
I used “--format json” to get the output in json format.
The output of the mgmt_cli command is passed to this command:
$CPDIR/jq/jq '.objects[] | [ .["name"], .["color"], .["comments"], .["port"], .["meta-info"]["last-modifier"] ] | @csv' -r
$CPDIR/jq/jq : jq is a tool that can do wonders with json data, it can extract the fields you want, filter them and even make a csv out of them.
“jq” is an open source tool. To learn more about the tool visit: https://stedolan.github.io/jq/ , https://jqplay.org/ and this link points to mgmt_cli examples in the Check Point community portal: https://community.checkpoint.com/message/1151
“jq” is now part of Gaia and you can find it in the $CPDIR/jq/ folder.
The “jq” command takes the json information from the mgmt_cli command and decides what to do with this json according to this string:
'.objects[] | [ .["name"], .["color"], .["comments"], .["port"], .["meta-info"]["last-modifier"] ] | @csv'
This string is made of 3 steps, each step separated by the “|” character.
JQ process each step and passes its output as the input for the next step.
Step 1:
The json object that we got from the mgmt_cli command looks like this:
{
"objects" : [ { data on tcp-object1}, {data on tcp-object 2} , … } ],
"from" : 1,
"to" : 217,
"total" : 217
}
The text “.objects[]”, tells JQ that we’re interested in the objects array and would like to pass the data in that array to the next step.
Step 2:
[ .["name"], .["color"], .["comments"], .["port"], .["meta-info"]["last-modifier"] ]
This step takes the tcp-objects and for every tcp-object creates an array with 5 fields.
Notice that the “last-modifier” field is a field inside an object called “meta-data”.
Here’s a screen-shot from https://jqplay.org/
I copied and pasted the output of the mgmt_cli command in the “json” panel.
The “filter” field holds the text from step1 + step2.
The result panel shows the output for the given json and filter.
Step 3:
This step takes the output of step2 and converts it from a list of arrays (as shown in the result panel in the screen shot) to a csv text file.
Each line in the output will now look like this:
"\"MS-SQL-Server\",\"magenta\",\"Microsoft SQL Server\",\"1433\",\"System\""
Step 4:
Adding the –r at the end of the JQ command removes the redundant “ characters surrounding each line and so lines would now look like this:
"MS-SQL-Server","magenta","Microsoft SQL Server","1433","System"
This is a sample one line operation demonstrating the interplay between mgmt_cli API call for object output via show command, and piping json formatted output through jq to a CSV output based on a limited list of object elements to include.
To use this example, copy-paste and modify the appropriate sections to suite the needs of your operation.
While the example is specifically using "show services-tcp", any show operation should be able to utilize the approach, with tailoring in the object element section.
Example (expanded to include authentication with "-s <session_id_file>"):
mgmt_cli show hosts details-level full limit 250 --format json -s id.txt | $CPDIR/jq/jq '.objects[] | [ .["name"], ["ipv4-address"], .["color"], .["comments"], .["nat-settings"]["auto-rule"], .["nat-settings"]["ipv4-address"], .["nat-settings"]["install-on"], .["nat-settings"]["method"] ] | @csv' -r
Code example version 1.0.0
R80, API version 1.0@
Thank you to Uri Bialik for providing this very instructive answer and code sample for json to CSV conversion using jq from the Gaia CLI.
NOTICE: By using this sample code you agree to terms and conditions in this Not authorized to view the specified document 1042
...
About CheckMates
Learn Check Point
Advanced Learning
YOU DESERVE THE BEST SECURITY