Automatically import ALL AD users in version 3.0.2.3

General eFa 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: 2659
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 eFa 4.0.0 RC1 now available in testing repo. Come join us in advancing eFa!

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: 2659
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 eFa 4.0.0 RC1 now available in testing repo. Come join us in advancing eFa!

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: 2659
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 eFa 4.0.0 RC1 now available in testing repo. Come join us in advancing eFa!

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: 2659
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 eFa 4.0.0 RC1 now available in testing repo. Come join us in advancing eFa!

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: 2659
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 eFa 4.0.0 RC1 now available in testing repo. Come join us in advancing eFa!

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