Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
Holy
Contributor

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

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

NSE 8 

NSE 1 - 7

 

NSE 8 NSE 1 - 7
2 Solutions
ede_pfau
Esteemed Contributor III

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


Ede

"Kernel panic: Aiee, killing interrupt handler!"

View solution in original post

Ede"Kernel panic: Aiee, killing interrupt handler!"
rwpatterson
Valued Contributor III

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

Bob - self proclaimed posting junkie!
See my Fortigate related scripts at: http://fortigate.camerabob.com

View solution in original post

Bob - self proclaimed posting junkie!See my Fortigate related scripts at: http://fortigate.camerabob.com
10 REPLIES 10
rwpatterson
Valued Contributor III

What are the available columns in the spreadsheet?

IP address, subnet, name, etc.

Bob - self proclaimed posting junkie!
See my Fortigate related scripts at: http://fortigate.camerabob.com

Bob - self proclaimed posting junkie!See my Fortigate related scripts at: http://fortigate.camerabob.com
rwpatterson
Valued Contributor III

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

 

Bob - self proclaimed posting junkie!
See my Fortigate related scripts at: http://fortigate.camerabob.com

Bob - self proclaimed posting junkie!See my Fortigate related scripts at: http://fortigate.camerabob.com
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

NSE 8 

NSE 1 - 7

 

NSE 8 NSE 1 - 7
rwpatterson
Valued Contributor III

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

Bob - self proclaimed posting junkie!
See my Fortigate related scripts at: http://fortigate.camerabob.com

Bob - self proclaimed posting junkie!See my Fortigate related scripts at: http://fortigate.camerabob.com
rwpatterson
Valued Contributor III

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

Bob - self proclaimed posting junkie!
See my Fortigate related scripts at: http://fortigate.camerabob.com

Bob - self proclaimed posting junkie!See my Fortigate related scripts at: http://fortigate.camerabob.com
ede_pfau
Esteemed Contributor III

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


Ede

"Kernel panic: Aiee, killing interrupt handler!"
Ede"Kernel panic: Aiee, killing interrupt handler!"
rwpatterson
Valued Contributor III

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

Bob - self proclaimed posting junkie!
See my Fortigate related scripts at: http://fortigate.camerabob.com

Bob - self proclaimed posting junkie!See my Fortigate related scripts at: http://fortigate.camerabob.com
daac
New Contributor

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
Esteemed Contributor III

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.


Ede

"Kernel panic: Aiee, killing interrupt handler!"
Ede"Kernel panic: Aiee, killing interrupt handler!"
Labels
Top Kudoed Authors