Automatically import ALL AD users in version 3.0.2.3

General E.F.A. discussion
Post Reply
dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 03 Jul 2017 21:21

I would like to automatically import ALL AD users in version 3.0.2.3

I can add AD users by manually having them login but I want to import them automatically from AD so if I add a new user in AD within a set schedule they will appear in EFA /Mailwatch user management.
an article I found says to update mailwatch_ldap_synch.sh
I did not see the file so I created it

I found this code but it does not seem to work, what am I missing? I am not sure which MSQL DB / user and password to use. Also, is there someplace I need to call this script?

Code: Select all


#!/bin/bash

#

################################################################################

#

# mailwatch_ldap_sync.sh: A shell script to import Microsoft Exchange Users from

# Active Directory into the MailWatch user database.

#

# Version: 1.1

#

# Copyright (C) 2012 Daniel Himler <d.himler@netsense.at>

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

#

################################################################################

#

# CHANGES

# =======

#

# Version 1.1

# -----------

# - Concatenate multiline results returned by ldapsearch

# - Decode Base64 strings returned by ldapsearch

# - Fix lookups of DNs with commas in their name

# - Exclude Exchange 2010 system mailboxes

# - Use mktemp for temporary file creation

#

# Version 1.0

# -----------

# - Initial Release



#################

# Configuration #

################################################################################



LDAP_URI="192.168.111.32:389"

LDAP_BASE="DC=mydomain,DC=org"

LDAP_USER=" myserviceaccount@mydomain.org"

LDAP_PASS="mysecretpass"

MYSQL_HOST="localhost"

MYSQL_PORT="3306"

MYSQL_NAME="mailscanner"

MYSQL_USER="mailwatch"

MYSQL_PASS="secret"



##################### DON'T TOUCH ANYTHING BELOW THIS LINE #####################



TEMPFILE="$(mktemp)"



LDAP_USERS="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" \

"(&

(objectClass=user)

(proxyAddresses=*)

)" \

"proxyAddresses" |

sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}' |

sed -ne "s/^proxyAddresses: SMTP:\(.*\)/\1/p" |

grep -Ev "DiscoverySearchMailbox|FederatedEmail|SystemMailbox" | sort)"



[ -f "$TEMPFILE" ] && rm -f "$TEMPFILE"

for USER in ${LDAP_USERS}; do

unset DN FULLNAME ALIASES GROUP_ALIASES TYPE REPORT SPAMSCORE HIGHSPAMSCORE NOSCAN RECIPIENT RESULT

USER="$(echo "$USER" | sed -e "s/\(.*\)/\L\1/")"

RESULT="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" \

"(proxyAddresses=SMTP:$USER)" \

"dn" "displayName" "proxyAddresses" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}')"

echo "$RESULT" | grep -qs "dn:: "

if [ "$?" -eq "0" ]; then

DN="$(echo "$RESULT" | sed -ne "s/^dn:: \(.*\)/\1/p" | base64 -d)"

else

DN="$(echo "$RESULT" | sed -ne "s/^dn: \(.*\)/\1/p")"

fi

DN="$(echo "$DN" | sed -e "s/\\\,/\\\\\\\\,/g")"

echo "$RESULT" | grep -qs "displayName:: "

if [ "$?" -eq "0" ]; then

FULLNAME="$(echo "$RESULT" | sed -ne "s/^displayName:: \(.*\)/\1/p" | base64 -d)"

else

FULLNAME="$(echo "$RESULT" | sed -ne "s/^displayName: \(.*\)/\1/p")"

fi

ALIASES="$(echo "$RESULT" | sed -ne "s/^proxyAddresses: smtp:\(.*\)/\1/p")"

GROUP_ALIASES="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" \

"(&(objectClass=group)(proxyAddresses=*)(member=$DN))" \

"proxyAddresses" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}' | sed -ne "s/^proxyAddresses: [sS][mM][tT][pP]:\(.*\)/\1/p")"

eval $(echo "SELECT type, quarantine_report, spamscore, highspamscore, noscan, quarantine_rcpt FROM users WHERE username = '$USER';" |

mysql -B -N --host="$MYSQL_HOST" --port="$MYSQL_PORT" --user="$MYSQL_USER" --password="$MYSQL_PASS" "$MYSQL_NAME" |

sed -e "s/\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)/TYPE=\1 REPORT=\2 SPAMSCORE=\"\3\" HIGHSPAMSCORE=\"\4\" NOSCAN=\"\5\" RECIPIENT=\"\6\"/")

USER="$(echo "$USER" | sed -e "s/@/\\\@/g")"

[ -n "$TYPE" ] || TYPE="U"

[ -n "$REPORT" ] || REPORT="0"

[ -n "$SPAMSCORE" ] || SPAMSCORE="0"

[ -n "$HIGHSPAMSCORE" ] || HIGHSPAMSCORE="0"

[ -n "$NOSCAN" ] || NOSCAN="0"

[ -n "$RECIPIENT" ] || RECIPIENT="NULL"

[ "$RECIPIENT" != "NULL" ] && RECIPIENT="'$RECIPIENT'"

RECIPIENT="$(echo "$RECIPIENT" | sed -e "s/@/\\\@/g")"

echo "REPLACE INTO users (username, password, fullname, type, quarantine_report, spamscore, highspamscore, noscan, quarantine_rcpt)

VALUES ('$USER', NULL, '$FULLNAME', '$TYPE', $REPORT, $SPAMSCORE, $HIGHSPAMSCORE, $NOSCAN, $RECIPIENT);" >> "$TEMPFILE"

echo "DELETE FROM user_filters WHERE username = '$USER';" >> "$TEMPFILE"

for ALIAS in ${ALIASES} ${GROUP_ALIASES}; do

ALIAS="$(echo "$ALIAS" | sed -e "s/\(.*\)/\L\1/" -e "s/@/\\\@/g")"

echo "INSERT INTO user_filters (username, filter, active) VALUES ('$USER', '$ALIAS', 'Y');" >> "$TEMPFILE"

done

done

MYSQL_USERS="$(echo "SELECT username FROM users WHERE password IS NULL;" |

mysql --host "$MYSQL_HOST" \

--port="$MYSQL_PORT" \

--user="$MYSQL_USER" \

--password="$MYSQL_PASS" \

--skip-column-names "$MYSQL_NAME")"

for USER in ${MYSQL_USERS}; do

unset DN RESULT

RESULT="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" "(proxyAddresses=SMTP:$USER)" "dn" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}')"

echo "$RESULT" | grep -qs "dn:: "

if [ "$?" -eq "0" ]; then

DN="$(echo "$RESULT" | sed -ne "s/^dn:: \(.*\)/\1/p" | base64 -d)"

else

DN="$(echo "$RESULT" | sed -ne "s/^dn: \(.*\)/\1/p")"

fi

DN="$(echo "$DN" | sed -e "s/\\\,/\\\\\\\\,/g")"

if [ "$DN" == "" ]; then

echo "DELETE FROM user_filters WHERE username = '$USER';" >> "$TEMPFILE"

echo "DELETE FROM users WHERE username = '$USER';" >> "$TEMPFILE"

fi

done



mysql --host="$MYSQL_HOST" --port="$MYSQL_PORT" --user="$MYSQL_USER" --password="$MYSQL_PASS" "$MYSQL_NAME" < "$TEMPFILE"

RETVAL="$?"



rm -f "$TEMPFILE"



exit "$RETVAL"


wilbourne
Posts: 52
Joined: 22 Sep 2016 09:04

Re: Automatically import ALL AD users in version 3.0.2.3

Post by wilbourne » 04 Jul 2017 15:27

Hello,

You can find the MSQL DB / user and password to used in the EFA-Config file locate in /etc/.

And you can put the script into /usr/local/bin

and add a line into crontab or cron.daily to lauch the script.

dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Re: Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 05 Jul 2017 15:43

I actually put the script in the cron daily folder (/etc/cron.daily)
I used the username: MAILWATCH

and the "MAILWATCHSQLPWD" password found in (/etc/EFA-Config)

still not populating users.

I moved the script and setup as you suggested and now i GET AN ERROR:

Output from command /usr/local/bin/mailwatch_ldap_sync.sh ..
/bin/sh: /usr/local/bin/mailwatch_ldap_sync.sh: Permission denied



Any suggestions? I am so close here...

dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Re: Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 06 Jul 2017 20:17

has anyone made importing users automatically work in version 3.0.2.3? I am at a loss

:oops: :( :? :cry:

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

Re: Automatically import ALL AD users in version 3.0.2.3

Post by shawniverson » 06 Jul 2017 20:46

Did you

Code: Select all

sudo chmod +x /usr/local/bin/mailwatch_ldap_sync.sh 
?
Version 3.0.2.6 released! Update now to keep your eFa secure!

dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Re: Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 06 Jul 2017 20:49

just triied that and ran the script I get this now:

Output from command /usr/local/bin/mailwatch_ldap_sync.sh ..

Could not parse LDAP URI(s)=192.168.111.32:389 (3)
/usr/local/bin/mailwatch_ldap_sync.sh: line 144: (&

(objectClass=user)

(proxyAddresses=*)

): command not found
/usr/local/bin/mailwatch_ldap_sync.sh: line 145: proxyAddresses: command not found
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
/usr/local/bin/mailwatch_ldap_sync.sh: line 242: --port=3306: command not found
/usr/local/bin/mailwatch_ldap_sync.sh: line 243: --user=mailwatch: command not found
<redacted sensitive data>
/usr/local/bin/mailwatch_ldap_sync.sh: line 245: --skip-column-names: command not found
/usr/local/bin/mailwatch_ldap_sync.sh: line 273: /tmp/tmp.ri8jB4m7CO: No such file or director

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

Re: Automatically import ALL AD users in version 3.0.2.3

Post by shawniverson » 06 Jul 2017 20:52

Looks like a typo in the script.

Can you post the code you have, minus sensitive data (such as the password)?
Version 3.0.2.6 released! Update now to keep your eFa secure!

dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Re: Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 06 Jul 2017 21:05

Code: Select all

#!/bin/bash

#

################################################################################

#

# mailwatch_ldap_sync.sh: A shell script to import Microsoft Exchange Users from

# Active Directory into the MailWatch user database.

#

# Version: 1.1

#

# Copyright (C) 2012 Daniel Himler <d.himler@netsense.at>

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

#

################################################################################

#

# CHANGES

# =======

#

# Version 1.1

# -----------

# - Concatenate multiline results returned by ldapsearch

# - Decode Base64 strings returned by ldapsearch

# - Fix lookups of DNs with commas in their name

# - Exclude Exchange 2010 system mailboxes

# - Use mktemp for temporary file creation

#

# Version 1.0

# -----------

# - Initial Release



#################

# Configuration #

################################################################################



LDAP_URI="192.168.111.32:389"

LDAP_BASE="DC=mydomain,DC=org"

LDAP_USER=" service_account@mydomain.org"

LDAP_PASS="mypassword"

MYSQL_HOST="localhost"

MYSQL_PORT="3306"

MYSQL_NAME="mailscanner"

MYSQL_USER="mailwatch"

MYSQL_PASS="secretpassword"



##################### DON'T TOUCH ANYTHING BELOW THIS LINE #####################



TEMPFILE="$(mktemp)"



LDAP_USERS="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" \

"(&

(objectClass=user)

(proxyAddresses=*)

)" \

"proxyAddresses" |

sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}' |

sed -ne "s/^proxyAddresses: SMTP:\(.*\)/\1/p" |

grep -Ev "DiscoverySearchMailbox|FederatedEmail|SystemMailbox" | sort)"



[ -f "$TEMPFILE" ] && rm -f "$TEMPFILE"

for USER in ${LDAP_USERS}; do

unset DN FULLNAME ALIASES GROUP_ALIASES TYPE REPORT SPAMSCORE HIGHSPAMSCORE NOSCAN RECIPIENT RESULT

USER="$(echo "$USER" | sed -e "s/\(.*\)/\L\1/")"

RESULT="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" \

"(proxyAddresses=SMTP:$USER)" \

"dn" "displayName" "proxyAddresses" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}')"

echo "$RESULT" | grep -qs "dn:: "

if [ "$?" -eq "0" ]; then

DN="$(echo "$RESULT" | sed -ne "s/^dn:: \(.*\)/\1/p" | base64 -d)"

else

DN="$(echo "$RESULT" | sed -ne "s/^dn: \(.*\)/\1/p")"

fi

DN="$(echo "$DN" | sed -e "s/\\\,/\\\\\\\\,/g")"

echo "$RESULT" | grep -qs "displayName:: "

if [ "$?" -eq "0" ]; then

FULLNAME="$(echo "$RESULT" | sed -ne "s/^displayName:: \(.*\)/\1/p" | base64 -d)"

else

FULLNAME="$(echo "$RESULT" | sed -ne "s/^displayName: \(.*\)/\1/p")"

fi

ALIASES="$(echo "$RESULT" | sed -ne "s/^proxyAddresses: smtp:\(.*\)/\1/p")"

GROUP_ALIASES="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" \

"(&(objectClass=group)(proxyAddresses=*)(member=$DN))" \

"proxyAddresses" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}' | sed -ne "s/^proxyAddresses: [sS][mM][tT][pP]:\(.*\)/\1/p")"

eval $(echo "SELECT type, quarantine_report, spamscore, highspamscore, noscan, quarantine_rcpt FROM users WHERE username = '$USER';" |

mysql -B -N --host="$MYSQL_HOST" --port="$MYSQL_PORT" --user="$MYSQL_USER" --password="$MYSQL_PASS" "$MYSQL_NAME" |

sed -e "s/\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)/TYPE=\1 REPORT=\2 SPAMSCORE=\"\3\" HIGHSPAMSCORE=\"\4\" NOSCAN=\"\5\" RECIPIENT=\"\6\"/")

USER="$(echo "$USER" | sed -e "s/@/\\\@/g")"

[ -n "$TYPE" ] || TYPE="U"

[ -n "$REPORT" ] || REPORT="0"

[ -n "$SPAMSCORE" ] || SPAMSCORE="0"

[ -n "$HIGHSPAMSCORE" ] || HIGHSPAMSCORE="0"

[ -n "$NOSCAN" ] || NOSCAN="0"

[ -n "$RECIPIENT" ] || RECIPIENT="NULL"

[ "$RECIPIENT" != "NULL" ] && RECIPIENT="'$RECIPIENT'"

RECIPIENT="$(echo "$RECIPIENT" | sed -e "s/@/\\\@/g")"

echo "REPLACE INTO users (username, password, fullname, type, quarantine_report, spamscore, highspamscore, noscan, quarantine_rcpt)

VALUES ('$USER', NULL, '$FULLNAME', '$TYPE', $REPORT, $SPAMSCORE, $HIGHSPAMSCORE, $NOSCAN, $RECIPIENT);" >> "$TEMPFILE"

echo "DELETE FROM user_filters WHERE username = '$USER';" >> "$TEMPFILE"

for ALIAS in ${ALIASES} ${GROUP_ALIASES}; do

ALIAS="$(echo "$ALIAS" | sed -e "s/\(.*\)/\L\1/" -e "s/@/\\\@/g")"

echo "INSERT INTO user_filters (username, filter, active) VALUES ('$USER', '$ALIAS', 'Y');" >> "$TEMPFILE"

done

done

MYSQL_USERS="$(echo "SELECT username FROM users WHERE password IS NULL;" |

mysql --host "$MYSQL_HOST" \

--port="$MYSQL_PORT" \

--user="$MYSQL_USER" \

--password="$MYSQL_PASS" \

--skip-column-names "$MYSQL_NAME")"

for USER in ${MYSQL_USERS}; do

unset DN RESULT

RESULT="$(ldapsearch -LLL -H "$LDAP_URI" -D "$LDAP_USER" -w "$LDAP_PASS" -x -b "$LDAP_BASE" "(proxyAddresses=SMTP:$USER)" "dn" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}')"

echo "$RESULT" | grep -qs "dn:: "

if [ "$?" -eq "0" ]; then

DN="$(echo "$RESULT" | sed -ne "s/^dn:: \(.*\)/\1/p" | base64 -d)"

else

DN="$(echo "$RESULT" | sed -ne "s/^dn: \(.*\)/\1/p")"

fi

DN="$(echo "$DN" | sed -e "s/\\\,/\\\\\\\\,/g")"

if [ "$DN" == "" ]; then

echo "DELETE FROM user_filters WHERE username = '$USER';" >> "$TEMPFILE"

echo "DELETE FROM users WHERE username = '$USER';" >> "$TEMPFILE"

fi

done



mysql --host="$MYSQL_HOST" --port="$MYSQL_PORT" --user="$MYSQL_USER" --password="$MYSQL_PASS" "$MYSQL_NAME" < "$TEMPFILE"

RETVAL="$?"



rm -f "$TEMPFILE"



exit "$RETVAL"

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

Re: Automatically import ALL AD users in version 3.0.2.3

Post by shawniverson » 06 Jul 2017 21:50

That script is really old :hand: don't use it. The mailwatch tables don't even look like this any more and have evolved since then.

Although a couple other scripts are out on the forums, I don't recommend them.

I think we need to make a script that is compatible with the newer versions of MailWatch.

I'm going to ponder this for a bit and see how much effort this will take...
Version 3.0.2.6 released! Update now to keep your eFa secure!

dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Re: Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 10 Jul 2017 19:07

SO I was curious if you cant create a script soon to import all users, is there a manual way to create all users from a csv file? Would I need their passwords?

PS thanks for all your hard work!

dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Re: Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 13 Jul 2017 20:06

shawniverson wrote:
06 Jul 2017 21:50
That script is really old :hand: don't use it. The mailwatch tables don't even look like this any more and have evolved since then.

Although a couple other scripts are out on the forums, I don't recommend them.

I think we need to make a script that is compatible with the newer versions of MailWatch.

I'm going to ponder this for a bit and see how much effort this will take...
Any word on this yet? My boss is hoping you can make something happen, he has faith in you! :pray: :clap:

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

Re: Automatically import ALL AD users in version 3.0.2.3

Post by shawniverson » 13 Jul 2017 22:52

I should have some time this weekend to work on this ;)
Version 3.0.2.6 released! Update now to keep your eFa secure!

mkamal
Posts: 5
Joined: 08 Feb 2017 11:08

Re: Automatically import ALL AD users in version 3.0.2.3

Post by mkamal » 06 Aug 2017 11:43

I had Working Copy of this script i had modify in the ldapsearch syntax and modify in script to get user email kindly find the attached script
i had test it with version 3.0.1.8 and 3.0.2.3 and against AD 2008 - 2008 R2 -2012 R2 and it work fine

Only Need to change the Ldap Search variable and Change Word Changepass With User password in Whole Script

Code: Select all



#!/bin/bash
#
################################################################################
#
# mailwatch_ldap_sync.sh: A shell script to import Microsoft Exchange Users from
#                         Active Directory into the MailWatch user database.
#
# Version:                1.1
#
# Copyright (C) 2012  Daniel Himler  <d.himler@netsense.at>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
################################################################################
#
# CHANGES
# =======
#
# Version 1.1
# -----------
# - Concatenate multiline results returned by ldapsearch
# - Decode Base64 strings returned by ldapsearch
# - Fix lookups of DNs with commas in their name
# - Exclude Exchange 2010 system mailboxes
# - Use mktemp for temporary file creation
#
# Version 1.0
# -----------
# - Initial Release

#################
# Configuration #
################################################################################

LDAP_URI="server.domain.com"
LDAP_BASE="DC=domain,DC=com"
LDAP_USER="user@domain.com"
LDAP_PASS="changepass"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_NAME="mailscanner"
MYSQL_USER="mailwatch"
MYSQL_PASS="kD1NVfO333fGje9XDwki8yhLfsjWQAeC9"

##################### DON'T TOUCH ANYTHING BELOW THIS LINE #####################

DIRNAME="$(dirname "$0")"
TEMPFILE="$(mktemp)"

LDAP_USERS="$(ldapsearch -LLL -h "$LDAP_URI" -D "$LDAP_USER" -w 'changepass' -x -b "$LDAP_BASE" \
		"(&
		  (objectClass=user)
		  (mail=*)
		 )" \
		"mail" |
		sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}' |
		sed -ne "s/^mail:\(.*\)/\1/p" |
		grep -Ev "DiscoverySearchMailbox|FederatedEmail|SystemMailbox" | sort)"

[ -f "$TEMPFILE" ] && rm -f "$TEMPFILE"
for USER in $LDAP_USERS; do
	unset DN FULLNAME ALIASES GROUP_ALIASES TYPE REPORT SPAMSCORE HIGHSPAMSCORE NOSCAN RECIPIENT RESULT
	USER="$(echo "$USER" | sed -e "s/\(.*\)/\L\1/")"
	RESULT="$(ldapsearch -LLL -h "$LDAP_URI" -D "$LDAP_USER" -w 'changepass' -x -b "$LDAP_BASE" \
		"(mail:=$USER)" \
		"dn" "displayName" "mail" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}')"
	echo "$RESULT" | grep -qs "dn:: "
	if [ "$?" -eq "0" ]; then
		DN="$(echo "$RESULT" | sed -ne "s/^dn:: \(.*\)/\1/p" | base64 -d)"
	else
		DN="$(echo "$RESULT" | sed -ne "s/^dn: \(.*\)/\1/p")"
	fi
	DN="$(echo "$DN" | sed -e "s/\\\,/\\\\\\\\,/g")"
	echo "$RESULT" | grep -qs "displayName:: "
	if [ "$?" -eq "0" ]; then
		FULLNAME="$(echo "$RESULT" | sed -ne "s/^displayName:: \(.*\)/\1/p" | base64 -d)"
	else
		FULLNAME="$(echo "$RESULT" | sed -ne "s/^displayName: \(.*\)/\1/p")"
	fi
	ALIASES="$(echo "$RESULT" | sed -ne "s/^mail:\(.*\)/\1/p")"
	GROUP_ALIASES="$(ldapsearch -LLL -h "$LDAP_URI" -D "$LDAP_USER" -w 'changepass' -x -b "$LDAP_BASE" \
		"(&(objectClass=group)(mail=*)(member=$DN))" \
		"mail" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}' | sed -ne "s/^mail: [sS][mM][tT][pP]:\(.*\)/\1/p")"
	eval $(echo "SELECT type, quarantine_report, spamscore, highspamscore, noscan, quarantine_rcpt FROM users WHERE username = '$USER';" |
		mysql -B -N --host="$MYSQL_HOST" --port="$MYSQL_PORT" --user="$MYSQL_USER" --password="$MYSQL_PASS" "$MYSQL_NAME" |
		sed -e "s/\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)\s\(\S*\)/TYPE=\1 REPORT=\2 SPAMSCORE=\"\3\" HIGHSPAMSCORE=\"\4\" NOSCAN=\"\5\" RECIPIENT=\"\6\"/")
	USER="$(echo "$USER" | sed -e "s/@/\\\@/g")"
	[ -n "$TYPE" ] || TYPE="U"
	[ -n "$REPORT" ] || REPORT="0"
	[ -n "$SPAMSCORE" ] || SPAMSCORE="0"
	[ -n "$HIGHSPAMSCORE" ] || HIGHSPAMSCORE="0"
	[ -n "$NOSCAN" ] || NOSCAN="0"
	[ -n "$RECIPIENT" ] || RECIPIENT="NULL"
	[ "$RECIPIENT" != "NULL" ] && RECIPIENT="'$RECIPIENT'"
	RECIPIENT="$(echo "$RECIPIENT" | sed -e "s/@/\\\@/g")"
	echo "REPLACE INTO users (username, password, fullname, type, quarantine_report, spamscore, highspamscore, noscan, quarantine_rcpt)
	VALUES ('$USER', NULL, '$FULLNAME', '$TYPE', $REPORT, $SPAMSCORE, $HIGHSPAMSCORE, $NOSCAN, $RECIPIENT);" >> "$TEMPFILE"
	echo "DELETE FROM user_filters WHERE username = '$USER';" >> "$TEMPFILE"
#	for ALIAS in $ALIASES $GROUP_ALIASES; do
#		ALIAS="$(echo "$ALIAS" | sed -e "s/\(.*\)/\L\1/" -e "s/@/\\\@/g")"
#		echo "INSERT INTO user_filters (username, filter, active) VALUES ('$USER', '$ALIAS', 'Y');" >> "$TEMPFILE"
#	done
done
MYSQL_USERS="$(echo "SELECT username FROM users WHERE password IS NULL;" |
	mysql --host "$MYSQL_HOST" \
		--port="$MYSQL_PORT" \
		--user="$MYSQL_USER" \
		--password="$MYSQL_PASS" \
		--skip-column-names "$MYSQL_NAME")"
for USER in $MYSQL_USERS; do
	unset DN RESULT
	RESULT="$(ldapsearch -LLL -h "$LDAP_URI" -D "$LDAP_USER" -w 'changepass' -x -b "$LDAP_BASE" "(mail:=$USER)" "dn" | sed -n '1 {h;$!d}; ${x;s/\n //g;p}; /^ /{H;d}; /^ /!{x;s/\n //g;p}')"
	echo "$RESULT" | grep -qs "dn:: "
	if [ "$?" -eq "0" ]; then
		DN="$(echo "$RESULT" | sed -ne "s/^dn:: \(.*\)/\1/p" | base64 -d)"
	else
		DN="$(echo "$RESULT" | sed -ne "s/^dn: \(.*\)/\1/p")"
	fi
	DN="$(echo "$DN" | sed -e "s/\\\,/\\\\\\\\,/g")"
	if [ "$DN" == "" ]; then
		echo "DELETE FROM user_filters WHERE username = '$USER';" >> "$TEMPFILE"
		echo "DELETE FROM users WHERE username = '$USER';" >> "$TEMPFILE"
	fi
done

mysql --host="$MYSQL_HOST" --port="$MYSQL_PORT" --user="$MYSQL_USER" --password="$MYSQL_PASS" "$MYSQL_NAME" < "$TEMPFILE"
RETVAL="$?"

rm -f "$TEMPFILE"

exit "$RETVAL"




dsheetz
Posts: 35
Joined: 01 Jun 2017 17:36

Re: Automatically import ALL AD users in version 3.0.2.3

Post by dsheetz » 07 Aug 2017 16:25

sorry but I still have 2 dumb questions:

1. what folder should this be copied to?
( I am using /usr/bin/mailwatch_ldap_sync.sh )

2. what credentials should I run this under? I set it to run as a cron job using root but got an error access denied...

error: (/bin/sh: /usr/bin/mailwatch_ldap_sync.sh: Permission denied)

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

Re: Automatically import ALL AD users in version 3.0.2.3

Post by shawniverson » 12 Aug 2017 12:43

Here's an official script for eFa v3.

https://raw.githubusercontent.com/E-F-A ... D-Sync.php

What it does do:
Uses existing MailWatch LDAP configuration
Creates new accounts/aliases
Properly sanitizes and validates emails from AD/LDAP before importing into MailWatch

What it does not do yet:
Delete old accounts
Update existing aliases

Limitations (PHP 5.3.x)
No LDAP paging support, requires alphabet walk workaround
Very large LDAP DBs with >1000 users in same letter of alphabet will get truncated
These limitations won't apply with eFa v4 since it will use a much newer PHP library.
Version 3.0.2.6 released! Update now to keep your eFa secure!

DrScarpetta
Posts: 8
Joined: 11 May 2017 14:35

Re: Automatically import ALL AD users in version 3.0.2.3

Post by DrScarpetta » 16 May 2018 20:12

I ran this on a fresh install of 3.0.2.6 and received a lot of

PHP Notice: Undefined index: REMOTE_ADDR in /var/www/html/mailscanner/functions.php on line 3809


errors.

Any idea?

Thanks!

Post Reply