#!/usr/bin/python
#
# **********
# mappingsecsize.py
# **********
#
# find additions, deletions and removals since yesterday.
#
# Added the maxsize and secure email to the ouput.
#
# sizeupdate contains any new or updated email size.
# secmailupdate contains any new or updated secure mail.
# toadd, todel and alllive will not change from mapping.py output.
# New update files for maxsize and secmail so changes can be propragated to the external database.
#
# This is a custom output using format :-
# in externalemail internalemail
# out internalemail externalemail
#
# The script queries the database looking for deleted (hidden) entries and creates a
# list (delin and delout) which can be applied to the external database to remove these
# entries. If the MM database has had entries removed (permenantly removed) then it is not possible to query
# the database, therefore these are written to a log file and processed separately (toremove).
#
# New entries (created in last 24 hours and not hidden) are added to addin and addout files.
#
# Update the output as required.
#
# To add to cron use crontab -e then add something similar to (don't copy the #)
# which will run every day at 01:15
#
# 15 1 * * * /home/user1/bin/mapping.py >> /home/user1/logs/mapping.log 2>&1
#
# Database variables MUST be configured. Search for CHANGE_ME
import sys
import MySQLdb
import time
import datetime
import os
import getpass
deleteset = 0
today = datetime.date.today()
todaydate = today.strftime("%Y%m%d")
#toremovedate = today.strftime("%Y-%m-%d")
print "The date is ", todaydate
# myFile = '/work/output1'
# myFile += str(today)
# myFile += ".txt"
# Output file names
# note set to /var/www/html/mapping, Ubuntu uses /var/www/mapping.
# entries added in last 24 hours and not hidden
# CHANGE_ME
#New entries to external database
addout = '/home/user1/logs/addout.%s' % todaydate
addin = '/home/user1/logs/addin.%s' % todaydate
#Only changes to maxsize listed here, including new mappings
sizeupdate = '/home/user1/logs/sizeupdate.%s' % todaydate
#Only change to secmail listed here, including
secmailupdate = '/home/user1/logs/secmailupdate.%s' % todaydate
# delete flag set in database.
# entries to be deleted from external database
delin = '/home/user1/logs/delin.%s' % todaydate
delout = '/home/user1/logs/delout.%s' % todaydate
# full dump to file
alllive = '/home/user1/logs/alllive.%s' % todaydate
# Entries removed from the database and logged here.
# Redhat/Fedora
# CHANGE_ME
toremove = '/var/www/html/mapping/logs/toremove'
oldremove = '/var/www/html/mapping/logs/toremove.%s' % todaydate
#Ubuntu
# CHANGE_ME
#toremove = '/var/www/mapping/logs/toremove'
#oldremove = '/var/www/mapping/logs/toremove.%s' % todaydate
## Prompt for db user name and password. Comment out if no prompts required.
#prompt="Enter Database name e.g. mapping:"
#dbname = raw_input(prompt)
#prompt="Enter Database User name e.g. root:"
#dbusername = raw_input(prompt)
#prompt="Enter Database User Password:"
#dbpass = getpass.getpass(prompt)
## End of prompting
## connect to the MySQL server
# CHANGE_ME - default is to prompt for dbname, user and password
# update the variables below for host,user,passwd and db
try:
# conn = MySQLdb.connect (host = "localhost",
# user = "%s" % dbusername,
# passwd = "%s" % dbpass,
# db = "%s" % dbname)
## For automatic running without prompt comment above and uncomment below
# CHANGE_ME
conn = MySQLdb.connect (host = "localhost",
user = "CHANGE_ME",
passwd = "CHANGE_ME",
db = "mapping")
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
try:
cursor = conn.cursor ()
todelin=open(delin, 'w')
todelout=open(delout, 'w')
toaddin=open(addin, 'w')
tosizeupdate=open(sizeupdate, 'w')
tosecmailupdate=open(secmailupdate, 'w')
toaddout=open(addout, 'w')
toalllive=open(alllive, 'w')
remfile=open(toremove, 'r')
delcount = 0
addcount = 0
# create a dictionary cursor so that column values
# can be accessed by name rather than by position
print "Email address changes since yesterday, with maxsize and secmail\n"
for line in remfile:
result_rem = line.split(' ')
remline1=result_rem[0].replace('\n','')
remline2=result_rem[1].replace('\n','')
print "Removed mapping, add to todel files %s %s\n" % (remline1, remline2)
todelin.write("in %s %s\n" % (remline2, remline1))
todelout.write("out %s %s\n" % (remline1, remline2))
todelout.write("out %s %s\n" % (remline2, remline2))
remfile.close ()
os.system("cp %s %s" % (toremove, oldremove))
if os.path.isfile (oldremove): print "Remove %s file copied to %s ok." % (toremove, oldremove)
else: print "ERROR: unable to copy remove file %s to %s " % (toremove, oldremove)
os.system("cat /dev/null > %s" % (toremove))
#if remfile.isempty: print "remove file blanked ok"
#else: print "ERROR: remove file not empty"
cursor.close ()
cursor = conn.cursor (MySQLdb.cursors.DictCursor)
# edit = 0 & deleteOld = 1 means the email addresses has changed and the old email address needs deleting (the entry will not be hidden in this case).
# If email address reverted back to the same in the database e.g. user made a mistake in changing it, edit will equal '1' and deleteOld='0'.
# Last 24 hours, both, in and out.
#cursor.execute ("SELECT id, type, line1, line2, oldline1, oldline2, lastUpdate, deleteOld, maxsize, mschange, secmail, smchange FROM address AS address WHERE address.lastUpdate > NOW() - INTERVAL 24 HOUR;")
# For today's date only e.g. run at 11pm 23rd, only the 23rd changes will be output.
cursor.execute ("SELECT id, type, line1, line2, oldline1, oldline2, lastUpdate, deleteOld, maxsize, mschange, secmail, smchange FROM address AS address WHERE TO_DAYS(NOW()) - TO_DAYS(address.lastUpdate) < 1;")
# Yesterday only (midnight to midnight)
#cursor.execute ("SELECT id, type, line1, line2, oldline1, oldline2, lastUpdate, deleteOld, maxsize, mschange, secmail, smchange FROM address AS address WHERE TO_DAYS(NOW()) - TO_DAYS(address.lastUpdate) = 1;")
# Last 24 hours, both only
#cursor.execute ("SELECT id, type, line1, line2, oldline1, oldline2, lastUpdate, deleteOld FROM address AS address WHERE address.lastUpdate > NOW() - INTERVAL 24 HOUR AND type='both';")
# Since 4am, both only
#cursor.execute ("SELECT id, type, line1, line2, oldline1, oldline2, lastUpdate, deleteOld FROM address AS address WHERE address.lastUpdate > CONCAT(DATE(CURDATE()),' 04:00:00') AND type='both';")
result_new = cursor.fetchall ()
for newrow in result_new:
# Check if in the deleted group as will need to be flagged for deletion in external database
#print "id %s, firstname %s, secondname %s, lastUpdate %s" % (row["id"], row["firstname"], row["lastname"], row["lastUpdate"])
cursor.execute ("SELECT id, firstname, lastname, edit, hidden, lastUpdate FROM contact AS contact WHERE id=%s LIMIT 1;", newrow['id'])
result_set = cursor.fetchall ()
for row in result_set:
# new loop with new id so ensure deleteset flag is zero.
if row["hidden"] == 1:
deleteset = 1
#print "deleteset 1"
else:
deleteset = 0
#print "deleteset 0"
# deleteOld flag means delete the old email address and add the new, then reset the flag and copy the new to old field
if newrow["deleteOld"] == 1:
oldline1 = newrow["oldline1"].replace("\\","")
oldline2 = newrow["oldline2"].replace("\\","")
# the old address must be deleted first and the changes added as a new entry
if newrow["type"] != 'out':
todelin.write("in %s %s\n" % (oldline2, oldline1))
if newrow["type"] != 'in':
todelout.write("out %s %s\n" % (oldline1, oldline2))
todelout.write("out %s %s\n" % (oldline2, oldline2))
# update database to show this had been deleted
cursor.execute ("UPDATE address SET oldline1 = line1, oldline2 = line2, deleteOld = '0' WHERE id=%s LIMIT 1;", newrow['id'])
delcount = delcount + 1
# end of newrow["deleteOld"] == 1:
# Add new entry
if deleteset == 0:
# Not in delete group
#toadd.write("add,%s,%s,%s,%s\n" % (row["firstname"], row["lastname"], newrow["line2"], newrow["line1"]))
line1 = newrow["line1"].replace("\\","")
line2 = newrow["line2"].replace("\\","")
# address type needs to be checked so
# both - written as both in and out format
# in - in format only
# out - out format only
if newrow["type"] != 'out':
toaddin.write("in %s %s\n" % (line2, line1))
if newrow["type"] != 'in':
toaddout.write("out %s %s\n" % (line2, line1))
toaddout.write("out %s %s\n" % (line2, line2))
addcount = addcount + 1
# Delete hidden entry
if deleteset == 1:
# delete flag set to add to del files
#todel.write("del,%s,%s,%s,%s\n" % (row["firstname"], row["lastname"], newrow["line2"], newrow["line1"]))
line1 = newrow["line1"].replace("\\","")
line2 = newrow["line2"].replace("\\","")
# address type needs to be checked so
# both - written as both in and out format
# in - in format only
# out - out format only
if newrow["type"] != 'out':
todelin.write("in %s %s\n" % (line2, line1))
if newrow["type"] != 'in':
todelout.write("out %s %s\n" % (line1, line2))
todelout.write("out %s %s\n" % (line2, line2))
delcount = delcount + 1
deleteset = 0
#print "done deleteset 1"
# end if deleteset == 1:
toaddin.close()
toaddout.close()
todelin.close()
todelout.close()
#
# process all in live entries
#
cursor.close ()
cursor = conn.cursor (MySQLdb.cursors.DictCursor)
cursor.execute ("SELECT id, type, line1, line2 FROM address AS address;")
result_new = cursor.fetchall ()
for newrow in result_new:
# Check if in the deleted group as will need to be flagged for deletion in ldap
#print "id %s, firstname %s, secondname %s, lastUpdate %s" % (row["id"], row["firstname"], row["lastname"], row["lastUpdate"])
cursor.execute ("SELECT id,hidden FROM contact AS contact WHERE id=%s AND hidden != 1 LIMIT 1;", newrow['id'])
result_set = cursor.fetchall ()
for row in result_set:
line1 = newrow["line1"].replace("\\","")
line2 = newrow["line2"].replace("\\","")
# write type both and in
if newrow["type"] != 'out':
toalllive.write("in %s %s\n" % (line2, line1))
# process all live out entries
cursor.close ()
cursor = conn.cursor (MySQLdb.cursors.DictCursor)
cursor.execute ("SELECT id, type, line1, line2 FROM address AS address;")
result_new = cursor.fetchall ()
for newrow in result_new:
# Check if in the deleted group as will need to be flagged for deletion in ldap
#print "id %s, firstname %s, secondname %s, lastUpdate %s" % (row["id"], row["firstname"], row["lastname"], row["lastUpdate"])
cursor.execute ("SELECT id,hidden FROM contact AS contact WHERE id=%s AND hidden != 1 LIMIT 1;", newrow['id'])
result_set = cursor.fetchall ()
for row in result_set:
line1 = newrow["line1"].replace("\\","")
line2 = newrow["line2"].replace("\\","")
# write type both and out
if newrow["type"] != 'in':
toalllive.write("out %s %s\n" % (line1, line2))
if line1 != line2:
toalllive.write("out %s %s\n" % (line2, line2))
cursor.close ()
toalllive.close()
# Ensure variables initialised in case no changes found
mscount = 0
smcount = 0
# Output all the mail size and secure email changes, including new mappings.
cursor = conn.cursor (MySQLdb.cursors.DictCursor)
# Only check for changes to secmail and size.
# NOTE: if want to use lastupdate, use contact.lastUpdate as address.lastUpdate is only changed when address changes.
cursor.execute ("SELECT address.id, type, line1, line2, maxsize, mschange, secmail, smchange FROM address AS address WHERE mschange=1 OR smchange=1;")
result_new = cursor.fetchall ()
for newrow in result_new:
# Check if in the deleted group as will need to be flagged for deletion in ldap
#print "id %s, firstname %s, secondname %s, lastUpdate %s" % (row["id"], row["firstname"], row["lastname"], row["lastUpdate"])
cursor.execute ("SELECT id,hidden FROM contact AS contact WHERE id=%s AND hidden != 1 LIMIT 1;", newrow['id'])
result_set = cursor.fetchall ()
for row in result_set:
line1 = newrow["line1"].replace("\\","")
line2 = newrow["line2"].replace("\\","")
maxsize = newrow["maxsize"]
secmail = newrow["secmail"]
mschange = newrow["mschange"]
smchange = newrow["smchange"]
# Max size change flagged
if mschange == 1:
tosizeupdate.write("%s %s %s\n" % (line1, line2, maxsize))
mscount = mscount + 1
# update database to show this had been exported/processed
cursor.execute ("UPDATE address SET mschange = '0' WHERE id=%s LIMIT 1;", newrow['id'])
# Secure mail change flagged
if smchange == 1:
tosecmailupdate.write("%s %s %s\n" % (line1, line2, secmail))
smcount = smcount + 1
# update database to show this had been exported/processed
cursor.execute ("UPDATE address SET smchange = '0' WHERE id=%s LIMIT 1;", newrow['id'])
cursor.close ()
tosizeupdate.close ()
tosecmailupdate.close ()
# At end of search provide summary of deletions and additions
print "Number deleted: %d Number added: %d" % (delcount,addcount)
print "Number Max size changes: %d Number Secure mail changes: %d" % (mscount,smcount)
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
# close mysql connection
conn.commit ()
conn.close ()
# End of script
