CSV converter - One-liner to convert CSV for Management API

[RickHoppe]

Have you ever run into a situation where there was a need to add loads of host objects to a Check Point management server which also have to be added to the same already existing group? And did you receive them in a CSV? When you look into that CSV you also notice there are a lot of colums that are not interesting at all when adding the object to the management server.

I’ve created a one-liner that instantly rewrites the CSV into a format that is accepted by the Check Point Management API  for adding host objects.

for f in *.csv; do dos2unix "$f" && echo "name,ip-address,groups.1" > "$f.tmp" && sed '1d;' "$f" | awk -F "," '{print "IP_"$1","$1",gHosts"}' >>"$f.tmp" && uniq -u "$f.tmp" >"$f.new" && mv "$f" "$f.org" && mv "$f.new" "$f" && rm "$f.tmp" && printf "Converted CSV, removed duplicates and renamed original file to $f.org\n\n";done

Take this example CSV. It has a header and the only interesting thing is the first column with the IP address. When you add those hosts manually you normally prepend the IP with IP_. All objects also have to be added to the group gHosts.

IP,date-seen,country
194.168.1.1,04-03-2020,NL
101.0.0.1,03-03-2020,UK
145.33.1.3,02-01-2020,UK
43.2.2.1,03-03-2020,NL
8.8.8.8,13-03-2020,DE
8.8.4.4,02-03-2020,FR
44.32.5.32,05-04-2020,ES

What will happen if you run the one-liner on a typical Management Server?

[Expert@MGMT:0]# for f in *.csv; do dos2unix "$f" && echo "name,ip-address,groups.1" > "$f.tmp" && sed '1d;' "$f" | awk -F "," '{print "IP_"$1","$1",gHosts"}' >>"$f.tmp" && uniq -u "$f.tmp" >"$f.new" && mv "$f" "$f.org" && mv "$f.new" "$f" && rm "$f.tmp" && printf "Converted CSV, removed duplicates and renamed original file to $f.org\n\n";done

dos2unix: converting file example.csv to Unix format ...
Converted CSV, removed duplicates and moved original file to example.csv.org

To enable the Management API to accept a valid CSV it needs a header line. Just three columns are needed for this example.

name,ip-address,groups.1

The one-liner will do the following:

1. It uses a loop function so it will run on all files ending with .csv. In this example we only have 1 file: example.csv
2. dos2unix will be executed on the CSV to make sure Windows formatting is removed.
3. It will create the temporary file example.csv.tmp with the new header line as described.
4. It will ignore the header line of example.csv
5. It is configured to understand comma seperated files, not semicolon (;).
6. As the IP address is in the first column it will take $1, prepends IP_ to write the name of the object, adds a comma, writes $1 again, adds a comma, writes groups.1
7. It adds all of this to example.csv.tmp that was created in step 3.
8. It will remove duplicates from example.csv.tmp and saves it as example.csv.new.
9. It will rename example.csv to example.csv.org
10. It will rename example.csv.new to example.csv
11. It will delete example.csv.tmp
12. It will print a message that it has converted, removed duplicates and renamed the original file.
13. If there a more CSV files to process it will start all over again at step 2. If not, than the one-liner ends here.

New example.csv:

name,ip-address,groups.1
IP_194.168.1.1,194.168.1.1,gHosts
IP_101.0.0.1,101.0.0.1,gHosts
IP_145.33.1.3,145.33.1.3,gHosts
IP_43.2.2.1,43.2.2.1,gHosts
IP_8.8.8.8,8.8.8.8,gHosts
IP_8.8.4.4,8.8.4.4,gHosts
IP_44.32.5.32,44.32.5.32,gHosts

Now you can use this CSV to add those new objects with the Management API:

mgmt_cli login user "username" > id.txt
mgmt_cli add host -b example.csv -s id.txt
mgmt_cli publish -s id.txt
mgmt_cli logout -s id.txt

Before I had time to create this one-liner I had to use Excel, Notepad++ and some Linux tools like uniq and dos2unix. Where it took a lot of time to do it that way this one-liner just does the same within a second.

Now this was an example specifically for my situation. You might want to tweak the one-liner for your own situation if for instance the CSV’s you receive are formatted in another way. Just modify the following part:

{print "IP_"$1","$1",gHosts"}

$1 is for the first column in the original CSV. If the IP in your CSV resides in column 3, just replace the second $1 with $3. If there is a hostname for the IP in column 2, just replace the first $1 with $2. In that case also remove “IP_” otherwise all hostnames are prepended with IP_. And if there is a group name in column 5, just replace “,gHosts” with “,”$5

If you want to add more parameters available to the add host command then just modify the same line and add the extra fields. You also need to modify the header line.

echo "name,ip-address,groups.1"

Enjoy!

[Danny]

This is really specific but I like all One-liners in general.

[RickHoppe]

True. I really believe I'm not the only one running into this usecase. With minor tweaks (and a bit of Linux knowledge) you can modify it for your own situation. It always feels good to give something back to the CheckMates community.