Consolidate several lines of a CSV file with firewall rules,

[starriol]

Consolidate several lines of a CSV file with firewall rules, in order to parse them easier?

Hi guys.
I have a .csv file, which I created using an HTML export from a Check Point firewall.
The objective is to have all the firewall configuration lines where a given host is present.
I have to do this for a few hundred, manually is not a reasonable option. I'm going to write a simple Python script for this.

The problem is that the output from the Check Point firewall is complicated to work with.
If a firewall rule works with several source or destination hosts, services or other configurations, instead of having them separated with a symbol other than a comma, I get a new line.

This prevents me from exporting the line where the host is present, since I would be missing info.

Let me show you an example, hostnames are modified, of course:

NO.;NAME;SOURCE;DESTINATION;VPN**;SERVICE;ACTION;TRACK;INSTALL ON;TIME;COMMENT
1;;fwxcluster;mcast_vrrp;;vrrp;accept;Log;fwxcluster;Any;"VRRP;;*Comment suppressed*
;;;;;igmp;;;;;
2;;fwxcluster;fwxcluster;;FireWall;accept;Log;fwxcluster;Any;"Management FWg;*Comment suppressed*
;;fwmgmpe;fwmgmpe;;ssh;;;;;
;;fwmgm;fwmgm;;;;;;;
3;NTP;G_NTP_Clients;cmm_ntpserver_pe01;;ntp;accept;None;fwxcluster;Any;*Comment suppressed*
;;;cmm_ntpserver_pe02;;;;;;;

As you can see, line 3 should be eliminated and line 2 should have the protocol mentioned in line 3, igmp, added to column number 6.

How can I do this?

[foxidrive]

Does the check point firewall have a forum?

[trebor68]

Your CSV file can write as following text to understand the problem:

Code: Select all

NO.;NAME;SOURCE       ;DESTINATION       ;VPN**;SERVICE ;ACTION;TRACK;INSTALL ON;TIME;COMMENT
1  ;    ;fwxcluster   ;mcast_vrrp        ;     ;vrrp    ;accept;Log  ;fwxcluster;Any ;"VRRP;;*Comment suppressed*
   ;    ;             ;                  ;     ;igmp    ;      ;     ;          ;    ;
2  ;    ;fwxcluster   ;fwxcluster        ;     ;FireWall;accept;Log  ;fwxcluster;Any ;"Management FWg;*Comment suppressed*
   ;    ;fwmgmpe      ;fwmgmpe           ;     ;ssh     ;      ;     ;          ;    ;
   ;    ;fwmgm        ;fwmgm             ;     ;        ;      ;     ;          ;    ;
3  ;NTP ;G_NTP_Clients;cmm_ntpserver_pe01;     ;ntp     ;accept;None ;fwxcluster;Any ;*Comment suppressed*
   ;    ;             ;cmm_ntpserver_pe02;     ;        ;      ;     ;          ;    ;

Problem 1 is when there not a value. The standard CSV file will use "".
Problem 2 is when there is a comment. First character is quotation mark """ but there not a following quotation mark.


The following code will convert to a standard CSV file.
Use "BATCH_file >new_file".
Please change the varible FILE to the correct file.

Code: Select all

@echo off
setlocal ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
set file=141013.csv
::
set lncom=#
findstr /r "%lncom%" %file% >nul
if %errorlevel%==0 goto :error
for /f "tokens=* eol=%lncom%" %%a in (%file%) do call :sub1 %%a
goto :eof

:error
echo.
echo  Error in the file "%file%"
echo.
echo  At the beginning of a line, the "%lncom%" is found.
echo  Please change the variable "lncom" of this batch to other character.
echo  More info "FOR /?"
echo.
goto :eof

:sub1
set var=%*
set num=0
set num_=1
:again1
if "!var:~%num%,1!"=="" goto :again2
if "!var:~%num%,1!"==";" (set num%num_%=%num%) & set /a num_+=1
set /a num+=1
goto :again1
:again2
set /a numb=num1
if %numb% gtr 0 (set str1="!var:~0,%numb%!") else set str1=""
set /a numa=num1 + 1, numb=num2 - num1 - 1
if %numb% gtr 0 (set str2="!var:~%numa%,%numb%!") else set str2=""
set /a numa=num2 + 1, numb=num3 - num2 - 1
if %numb% gtr 0 (set str3="!var:~%numa%,%numb%!") else set str3=""
set /a numa=num3 + 1, numb=num4 - num3 - 1
if %numb% gtr 0 (set str4="!var:~%numa%,%numb%!") else set str4=""
set /a numa=num4 + 1, numb=num5 - num4 - 1
if %numb% gtr 0 (set str5="!var:~%numa%,%numb%!") else set str5=""
set /a numa=num5 + 1, numb=num6 - num5 - 1
if %numb% gtr 0 (set str6="!var:~%numa%,%numb%!") else set str6=""
set /a numa=num6 + 1, numb=num7 - num6 - 1
if %numb% gtr 0 (set str7="!var:~%numa%,%numb%!") else set str7=""
set /a numa=num7 + 1, numb=num8 - num7 - 1
if %numb% gtr 0 (set str8="!var:~%numa%,%numb%!") else set str8=""
set /a numa=num8 + 1, numb=num9 - num8 - 1
if %numb% gtr 0 (set str9="!var:~%numa%,%numb%!") else set str9=""
set /a numa=num9 + 1, numb=num10 - num9 - 1
if %numb% gtr 0 (set str10="!var:~%numa%,%numb%!") else set str10=""
set /a numa=num10 + 1
set str11=!var:~%numa%!
if not defined str11 (set str11="") else set str11="%str11:"=%"

echo %str1%,%str2%,%str3%,%str4%,%str5%,%str6%,%str7%,%str8%,%str9%,%str10%,%str11%
goto :eof

Please check the file before you continue editing the file.