CLI API Example json to CSV Export for later use in batch add API commands

Document created by Eric Beasley Employee on Jun 29, 2016
Version 1Show Document
  • View in full screen mode


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 (","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"


"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:


  • mgmt_cli show services-tcp details-level full limit 500 --root true --format json

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.


  • … and now for the tricky part:

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: ,  and this link points to mgmt_cli examples in the  Check Point community portal:

“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


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 Version

Code example version 1.0.0


Tested on version

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 Terms and Conditions


2 people found this helpful