How to sync Active Directory account attributes with a table in MySQL?

Question

MySQL

Good day everyone!
There was a need to export AD account attribute data to a separate table in the MySQL database. Unfortunately, I haven’t found anything more convenient than a PowerShell script yet. Tell me, is there any special software for fast and convenient data synchronization, or are scripts the most convenient?

Answer 1

Good afternoon, I once wrote something like this for unloading e-mail addresses from AD, maybe it will help.
Script:

#!/usr/bin/python
__author__ = 'tiv'

import os
import ldap
import MySQLdb
from ConfigParser import ConfigParser
from subprocess import call

mysql = ['localhost',  # host
         'postfix',  # user
         'postfix',  # password
         'postfix']  # schema


def main():
    try:
        emails = []
        domains = []
        connection = []
        cp = ConfigParser()
        cp.read('/etc/postfix/postfix-mysql.cf')
        for i in cp._sections:
            connection = [cp.get(i, 'dc'), cp.get(i, 'user'), cp.get(i, 'pass'), cp.get(i, 'dn'), cp.get(i, 'host')]
            print('Processing LDAP server ' + connection[0] + ':')
            basedn = connection[3]
            nexthop = connection[4]
            lc = ldapconnection(connection)
            ls = ldapsearch(lc, basedn)
            rl = resultlist(ls)
            emails.extend(rl[0])
            for domain in rl[1]:
                domains.append([domain, nexthop])
            print('Processing of LDAP server ' + connection[0] + ' completed.')
        createdb(emails, domains, mysql)
    except:
        print('Error processing of LDAP server ' + connection[0] + '!')
        pass
    try:
        print(' Running postmap and reload Postfix...')
        postfixreload()
        print(' Running postmap and reload Postfix successfully!')
        print('Operation completed successfully!')
    except:
        print('Error running postmap and reload Postfix!')
        pass


def postfixreload():
        # The top argument for walk
        topdir = '/etc/postfix'
        # The extension to search for
        exten = '_hash'
        for dirpath, dirnames, files in os.walk(topdir):
            for name in files:
                if name.lower().endswith(exten):
                    call(['postmap', 'hash:' + os.path.join(dirpath, name)])
        call(['postfix', 'reload'])



def ldapconnection(ldapserver):
    try:
        print(' Trying to connect to LDAP server ' + ldapserver[0] + '...')
        ldapconnection = ldap.initialize('ldap://' + ldapserver[0])
        ldapconnection.simple_bind_s(ldapserver[1], ldapserver[2])
        ldapconnection.protocol_version = ldap.VERSION3
        ldapconnection.set_option(ldap.OPT_REFERRALS, 0)
        print(' Connection to LDAP server ' + ldapserver[0] + ' succesfull.')
    except:
        print('Error connecting to LDAP server ' + ldapserver[0] + '!')
        pass
    return ldapconnection


def ldapsearch(ldapconnection, basedn):
    try:
        print(' Sending LDAP query request...')
        scope = ldap.SCOPE_SUBTREE
        filter = '(&(proxyAddresses=smtp:*)(!(objectClass=contact)))'
        attributes = ['proxyAddresses']
        searchresults = ldapconnection.search_s(basedn, scope, filter, attributes)
        print(' LDAP query request results received.')
    except:
        print('Error sending LDAP query request!')
        pass
    return searchresults


def resultlist(searchresults):
    try:
        print(' Processing LDAP query results...')
        emails = []
        domains = []
        for i in range(len(searchresults)):
            try:
                for j in range(len(searchresults[i][1]['proxyAddresses'])):
                    r = searchresults[i][1]['proxyAddresses'][j].lower()
                    if 'smtp:' in r:
                        email = r[5:]
                        emails.append(email)
                        domain = email.split("@")[1]
                        domains.append(domain)
            except:
                pass
        print(' LDAP query results processed.')
    except:
        print('Error processing LDAP query results!')
        pass
    return removedublicates(emails), removedublicates(domains)


def createdb(emails, domains, mysql):
    try:
        print('Connecting to DB ' + mysql[3] + '...')
        try:
            db = MySQLdb.connect(host=mysql[0], user=mysql[1], passwd=mysql[2])
            cursor = db.cursor()
            sql = 'CREATE SCHEMA IF NOT EXISTS ' + mysql[3]
            cursor.execute(sql)
            db.commit()
        except:
            pass
        try:
            db = MySQLdb.connect(host=mysql[0], user=mysql[1], passwd=mysql[2], db=mysql[3])
            cursor = db.cursor()
        except:
            print('Error connecting to DB ' + mysql[3] + '!')
        print(' Check schemas and tables...')
        sql = ['CREATE TABLE IF NOT EXISTS ' + mysql[3] + '.relay_users (id INT NOT NULL, email LONGTEXT NULL, PRIMARY KEY (id))',
               'CREATE TABLE IF NOT EXISTS ' + mysql[3] + '.relay_domains (id INT NOT NULL, name LONGTEXT NULL, nexthop LONGTEXT NULL, PRIMARY KEY (id))',
               'TRUNCATE ' + mysql[3] + '.relay_users',
               'TRUNCATE ' + mysql[3] + '.relay_domains']
        for i in range(len(sql)):
            cursor.execute(sql[i])
            db.commit()
        print(' Inserting domains...')
        for i in range(len(domains)):
            sql = 'INSERT INTO postfix.relay_domains (id, name, nexthop)' \
                  'VALUES ("' + str(i) + '", "' + domains[i][0] + '", "smtp:[' + domains[i][1] + ']")'
            cursor.execute(sql)
            db.commit()
        print(' Inserting emails...')
        for i in range(len(emails)):
            sql = 'INSERT INTO postfix.relay_users (id, email)' \
                  'VALUES ("' + str(i) + '", "' + emails[i] + '")'
            cursor.execute(sql)
            db.commit()
        db.close()
        print('Connection to DB ' + mysql[3] + ' closed.')
    except:
        print('Error while operating with DB ' + mysql[3] + '!')
        pass


def removedublicates(input):
    seen = set()
    seen_add = seen.add
    return [x for x in input if not (x in seen or seen_add(x))]


if __name__ == '__main__':
    main()

Config file for it ('/etc/postfix/postfix-mysql.cf' in script):

[Example Company]
dc = dc.example.com
dn = dc=example,dc=local
user = EXAMPLE\user
pass = password
host = smtp.example.com