Michael.mast at 18:01, 5 February 2020

2020-02-05T18:01:42Z

Michael.mast: /* LDAP */

2020-01-30T13:21:47Z

LDAP

Michael.mast: Created page with "==Purpose== To dip my toes into Python.

My intern is syncing a local MySQL database with an external payroll API. Great, but now I need this information synced with A..."

2020-01-28T20:41:20Z

Created page with "==Purpose== To dip my toes into Python.<br><br> My intern is syncing a local MySQL database with an external payroll API. Great, but now I need this information synced with A..."

New page

==Purpose==
To dip my toes into Python.<br><br>

My intern is syncing a local MySQL database with an external payroll API. Great, but now I need this information synced with AD.

==LDAP==
<ref>https://ldap3.readthedocs.io/tutorial_intro.html</ref>
Running this on Amazon Linux 2
<pre>
sudo pip3 install ldap3

</pre>

==MySQL==
<pre>
sudo pip3 install mysql-connector-python
</pre>

← Older revision		Revision as of 18:01, 5 February 2020
Line 3:		Line 3:

	My intern is syncing a local MySQL database with an external payroll API. Great, but now I need this information synced with AD.		My intern is syncing a local MySQL database with an external payroll API. Great, but now I need this information synced with AD.
		+
		+	==Full Script==
		+
		+	<pre>
		+	##Import things
		+	from ldap3 import Server, Connection, ALL, NTLM, MODIFY_REPLACE
		+	import mysql.connector
		+	from ldap_filter import Filter
		+	##LDAP Setup
		+	ldap_server = Server('<ldap server>')
		+	ldap_conn = Connection(
		+	ldap_server,
		+	user="tld\\<adminaccount>",
		+	password='<somepassword>',
		+	authentication=NTLM
		+	)
		+
		+	##MySQL Setup
		+	mysql_conn = mysql.connector.connect(
		+	host = "<somehostname>",
		+	user = "<db user name>",
		+	passwd = "<somepassword>",
		+	database = "<database name>"
		+	)
		+	##Value Mapping
		+	values_dict = {
		+	"First_Name": "givenname",
		+	"Middle_Name": "middlename",
		+	"Last_Name": "sn",
		+	"Preferred_Name": "extensionattribute2",
		+	"Work_Location": "physicaldeliveryofficename",
		+	"Work_City": "l",
		+	"Work_State": "st",
		+	"Work_Postal": "postalcode",
		+	"Work_Email": "mail"
		+	}
		+
		+	##Make connections and compare values.
		+	cursor = mysql_conn.cursor()
		+	selecttest = "select * from Employee_Info"
		+	try:
		+	ldap_conn.bind()
		+	except:
		+	print("Unable to bind to LDAP")
		+	exit()
		+	try:
		+	cursor.execute(selecttest)
		+	except:
		+	print("Unable to select from database")
		+	exit()
		+	else:
		+	dblist = cursor.fetchall()
		+	for i in dblist:
		+	#print(i)
		+	#print(i[1])
		+	Employee_ID = i[1]
		+	First_Name = i[2]
		+	Middle_Name = i[3]
		+	Last_Name = i[4]
		+	Preferred_Name = i[13]
		+	Work_Address1 = i[15]
		+	Work_Address2 = i[16]
		+	Work_City = i[17]
		+	Work_State = i[18]
		+	Work_Postal = i[19]
		+	Job_Title = i[20]
		+	Position_Code = i[21]
		+	Work_Email = i[22]
		+	ldap_conn.search('DC=domain,DC=tld', '(employeeid={})'.format(Employee_ID), attributes = ["givenname","middlename","sn","extensionattribute2","physicaldeliveryofficename","streetaddress","l","st","postalcode","title","mail"])
		+	#print(ldap_conn.entries)
		+	try:
		+	dn = ldap_conn.entries[0].entry_dn
		+	except:
		+	''
		+	for x in values_dict:
		+	value2 = values_dict[x]
		+	try:
		+	result1 = getattr(ldap_conn.entries[0], value2)
		+	#print(result1)
		+	except Exception as error:
		+	''
		+	else:
		+	if Work_Address2:
		+	sql_address='{0}\n{1}'.format(Work_Address1, Work_Address2)
		+	else:
		+	sql_address=Work_Address1
		+	ldap_address=(ldap_conn.entries[0].streetaddress)
		+	if sql_address != ldap_address:
		+	print('Updating Address for ',First_Name, Last_Name)
		+	#print('Comparing\n',sql_address,'\n\n',ldap_address)
		+	ldap_conn.modify(dn, {'streetaddress' : [(MODIFY_REPLACE, [sql_address])] })
		+	ldap1 = str(result1)
		+	ldap2 = ldap1.lower()
		+	if '[' not in ldap2:
		+	try:
		+	sql1 = eval(x)
		+	sql1 = str(sql1)
		+	sql2 = sql1.lower()
		+	if not sql2:
		+	raise Exception('sql2 is empty')
		+	except Exception as error:
		+	''
		+	else:
		+	if ldap2 != sql2:
		+	print(First_Name, Last_Name)
		+	#print('SQL', sql1, 'does not match LDAP', ldap1)
		+	sql3 = sql2.capitalize()
		+	print(sql3, 'replacing', ldap1)
		+	ldap_conn.modify(dn, {value2 : [(MODIFY_REPLACE, [sql3])] })
		+
		+	ldap_conn.unbind()
		+
		+	</pre>

	==LDAP==		==LDAP==

@@ Line 8: / Line 8: @@
 Running this on Amazon Linux 2
 <pre>
-sudo pip3 install ldap3
+sudo pip3 install ldap3 ldap-filter
 </pre>

Python Sync AD with MySQL DB - Revision history

Michael.mast at 18:01, 5 February 2020

Michael.mast: /* LDAP */

Michael.mast: Created page with "==Purpose== To dip my toes into Python. My intern is syncing a local MySQL database with an external payroll API. Great, but now I need this information synced with A..."

Michael.mast: Created page with "==Purpose== To dip my toes into Python.

My intern is syncing a local MySQL database with an external payroll API. Great, but now I need this information synced with A..."