How To Mass Import Whitelists and Blacklists

Questions and answers about how to do stuff
Post Reply
User avatar
shawniverson
Posts: 3087
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

How To Mass Import Whitelists and Blacklists

Post by shawniverson » 21 Sep 2014 19:40

Mass import script is now available here:

https://raw.githubusercontent.com/E-F-A ... timport.sh

To download:

Code: Select all

wget https://raw.githubusercontent.com/E-F-A/v3/master/tools/listimport.sh 
chmod ugo+x userimport.sh
EFA Mass Whitelist and Blacklist Import Help

listimport.sh Copyright (C) 2014 efa-project.org
Licened GNU GPL v3. This program comes with ABSOLUTELY NO WARRANTY
This is free software, and you are welcome to redistribute it under
certain conditions. See http://www.gnu.org/licenses for more details

Usage: listimport.sh -f mylist -b|-w [-a|-o [-q]]
-f Whitelist or Blacklist File
-a append to existing list
-b File is a Blacklist
-q force overwrite database tables without prompting
-o overwrite existing list
-w File is a Whitelist

Whitelist and Blacklist is newline separated list with each
line in either of the following formats:

<From Address Domain or IP>, <To Address Domain or IP>
<From Address Domain or IP>
Examples:

Code: Select all

sudo /path/to/listimport.sh -f /path/to/mywhitelist.txt -a -w

Code: Select all

sudo /path/to/listimport.sh -f /path/to/myblacklist.txt -a -b
Use the overwrite feature with caution. Always back up prior to executing your mass import.
Version eFa 4.0.2 now available!

SupportOU
Posts: 47
Joined: 12 Sep 2016 18:47

Re: How To Mass Import Whitelists and Blacklists

Post by SupportOU » 17 Feb 2017 15:19

Hola!

Is there also a mass export functionality??

Grtz,
Ronald

User avatar
shawniverson
Posts: 3087
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: How To Mass Import Whitelists and Blacklists

Post by shawniverson » 17 Feb 2017 23:33

Should be pretty straightforward.
Version eFa 4.0.2 now available!

User avatar
shawniverson
Posts: 3087
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: How To Mass Import Whitelists and Blacklists

Post by shawniverson » 17 Feb 2017 23:33

Version eFa 4.0.2 now available!

enricosx
Posts: 1
Joined: 29 Mar 2018 11:52

Re: How To Mass Import Whitelists and Blacklists

Post by enricosx » 29 Mar 2018 12:08

thanks for yours hard work.

what is the correct syntax?

domain.com
or
*domain.com
or
*@domain.com

User avatar
shawniverson
Posts: 3087
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: How To Mass Import Whitelists and Blacklists

Post by shawniverson » 05 Jan 2019 18:48

Wildcards should not be used.
Version eFa 4.0.2 now available!

liam87
Posts: 5
Joined: 05 Dec 2017 08:38

Re: How To Mass Import Whitelists and Blacklists

Post by liam87 » 14 Feb 2020 20:26

If you would like to use this script with eFa 4.0 just replace the line

Code: Select all

MAILWATCHSQLPWD=`grep MAILWATCHSQLPWD /etc/EFA-Config | sed 's/.*://'`
with

Code: Select all

MAILWATCHSQLPWD="`grep MAILWATCHSQLPWD /etc/eFa/MailWatch-Config | sed 's/.*://'`"

PABBY
Posts: 1
Joined: 19 May 2020 13:04

Re: How To Mass Import Whitelists and Blacklists

Post by PABBY » 27 May 2020 09:38

I'm posting this in case anyone comes across a similar problem while migrating their white and black lists from a Windows environment.

I recently installed my first EFA4 appliance using the ISO image. After a few minor teething problems (DNS), it was soon up and running, and I was keen to migrate our white and black lists from our existing system (Sophos PureMessage running on Windows 2016 & Exchange 2016). I scoured the website & forums and eventually ended up on this "How to" post.

I installed the script with no issues and tested it with a couple of domain names - perfect. I went ahead and exported my lists from PureMessage, transferred them over to the appliance and imported them using the script, all with no issues. My white and black lists were fully populated - job done.

Except… only domains I entered manually into the lists were working and those I'd imported were being ignored.

I re-scoured the forums and found a couple of people with similar problems, but no resolution. I then came across an article that detailed how to export your lists from the MySQL database for transfer into another appliance, so I tried this with my lists, but the results were the same - only manually entered domains were being processed against incoming email.

It was only when I loaded the exported SQL table into vi that I discovered what my problem was - each imported domain name had \r tagged onto the end of it, similar to this example:

Code: Select all

(438,'default','','sampledomain.com\r')
I used vi to search and replace all instances of \r in the exported SQL file and re-imported it into my database. My white and black lists now worked perfectly.

I know why the domain names are appended with \r but can find no simple way of avoiding it when migrating lists from a Windows system. Is the removal of \r something that could be included in the import script to avoid others experiencing the same problem?

Post Reply