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