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

Eric_Beasley — Wed, 29 Jun 2016 16:22:48 GMT

Overview

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.

Description

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:

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

Instructions

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 Not authorized to view the specified document 1042