How to make a excel script to add >1000 IP-Adresses to FortiGate?

[Holy]

Hello Guys,

i need to import >1000 IP-Adresses from a Excel Table to FortiGate, so i somehow need to make a script that will convert the IPs to the CLI Add Firewall Adress objects.

Do someone has a excel scriipt for that?

How do you deal with it , if you have to add a large amount of firewall objects?

Thank you

[ede_pfau · Accepted solution]

You may have a look at my tool which I've written in python.

It takes into account that address groups are limited in size, and will create address groups and a super-address group automatically. FQDNs are allowed in the input list (which uses the hosts.deny format) but are converted to static IPs before importing. If you don't like that you are free to change the source code, to be found here: https://beneicke-edv.de/s.../tools/#ext_blacklists

[rwpatterson]

What are the available columns in the spreadsheet?

IP address, subnet, name, etc.

[rwpatterson]

If your Excel spread sheet contains the columns:

0.0.0.0,255.255.255.0,"test zero" 1.1.1.1,255.255.255.0,"test one" 2.2.2.2,255.255.255.0,"test two"

Save to a comma delimited file named "names.txt".

Save the below into a batch file (I called it "addresses.bat")

@echo off
echo:config firewall address
FOR /F "tokens=1-3 delims=," %%A IN (C:\<file path>\names.txt) DO (
echo    edit %%C
echo        set associated-interface "%1"
echo        set subnet %%A %%B
echo    next
)

:end
echo:end

From a command prompt, invoke the script as:

C:\>Addresses.bat internal

where internal is the name of the interface. The output will be as below:

C:\>Addresses.bat internal
config firewall address
   edit "test zero"
       set associated-interface "internal"
       set subnet 0.0.0.0 255.255.255.0
   next
   edit "test one"
       set associated-interface "internal"
       set subnet 1.1.1.1 255.255.255.0
   next
   edit "test two"
       set associated-interface "internal"
       set subnet 2.2.2.2 255.255.255.0
   next
end

Hope that is what you are looking for.

Bob

[Holy]

Hello,

thank you very much!

i will try it tomorrow, but i also need to define some FQDNs

will the script be simillar to that?

Thank you

[rwpatterson]

For FQDN, file fqdn.txt format:

"www.test-one.com","test zero"
"www.testtwo.com","test one"
"www.testthree.com","test two"

Note that quotes are needed around the FQDN. The script will not add them.

The batch is:

@echo off
echo:config firewall address
FOR /F "tokens=1-2 delims=," %%A IN (C:\<file path>\fqdn.txt) DO (
echo    edit %%B
echo        set associated-interface "%1"
echo        set type fqdn
echo        set fqdn %%A
echo    next
)

:end
echo:end

The output:

config firewall address
   edit "test zero"
       set associated-interface "internal"
       set type fqdn
       set fqdn "www.test-one.com"
   next
   edit "test one"
       set associated-interface "internal"
       set type fqdn
       set fqdn "www.testtwo.com"
   next
   edit "test two"
       set associated-interface "internal"
       set type fqdn
       set fqdn "www.testthree.com"
   next
end

[rwpatterson]

Similarly for ranges:

192.168.0.1,192.168.0.254,"test zero"
172.16.0.1,172.16.0.254,"test one"
10.0.0.1,10.0.0.254,"test two"

The batch:

@echo off
echo:config firewall address
FOR /F "tokens=1-3 delims=," %%A IN (C:\<file path>\range.txt) DO (
echo    edit %%C
echo        set associated-interface "%1"
echo        set type iprange

echo        set end-ip %%B

echo        set start-ip %%A
echo    next
)
:end
echo:end

The output:

config firewall address
   edit "test zero"
       set associated-interface "internal"

       set type iprange

       set end-ip 192.168.0.254
       set start-ip 192.168.0.1
   next
   edit "test one"
       set associated-interface "internal"
       set type iprange

       set end-ip 172.16.0.254
       set start-ip 172.16.0.1
   next
   edit "test two"
       set associated-interface "internal"
       set type iprange

       set end-ip 10.0.0.254
       set start-ip 10.0.0.1
   next
end

[ede_pfau · Accepted solution]

You may have a look at my tool which I've written in python.

It takes into account that address groups are limited in size, and will create address groups and a super-address group automatically. FQDNs are allowed in the input list (which uses the hosts.deny format) but are converted to static IPs before importing. If you don't like that you are free to change the source code, to be found here: https://beneicke-edv.de/s.../tools/#ext_blacklists

[rwpatterson · Accepted solution]

ede_pfau wrote:

You may have a look at my tool which I've written in python.

It takes into account that address groups are limited in size, and will create address groups and a super-address group automatically. FQDNs are allowed in the input list (which uses the hosts.deny format) but are converted to static IPs before importing. If you don't like that you are free to change the source code, to be found here: https://beneicke-edv.de/s.../tools/#ext_blacklists

Showoff. ;)

[daac]

Hello I use excel with formulas, then I move to notepad and eliminate the additional entries that are created as (view file attachment):

"    edit ""firefox update server1""         set type fqdn         set fqdn ""aus*.mozilla.org""     next"

after that I recommend you to copy partials of 100 in the cli so you can see if some error was generated in some object (or generating a log in the putty to then filter error during the configuration of the script).

 Aaaa has happened to me that in small teams I have to send few lines because some do not copy them, for the particular case of a Fortigate 3700 I copied between 100 - 200 objects and I pass them as if nothing.

regards

[ede_pfau]

Using a direct paste into the CLI is error prone, more specifically, you will fight with timeout problems. The input buffer will overflow at some point, leading to skipped input. If you need to add config then use the 'Upload bulk command line file' in System>Advanced which does not show these problems.

While you submit the file activate CLI debugging in a Console window (diag deb ena, diag deb cli 7) and watch the output. If there is an error you will know where.