Hi
Ok I can post all the code with the users and passwords removed, i got the impression from the reply that employ someone as you dont help with business questions
the Current Script
#! /usr/bin/python3
# eSecuure Reponse Ticketing Backend
import hashlib
import email
import mimetypes
import os
import poplib
import re
import smtplib
import time
import sys
import base64
import imaplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from time import strftime
import datetime
now = datetime.datetime.now()
import pymssql
#from pymssql import _mssql
from configobj import ConfigObj
from email.utils import formatdate
from imaplib import IMAP4_SSL
from hashlib import sha256
ini = ConfigObj("E:\Python39\checkMail-imap.ini")
# setup config vars
mailini = ini["Mail Server"]
mailPOPServer = mailini['mailPOPServer']
mailSMTPServer = mailini['mailSMTPServer']
mailIMAPServer = mailini['mailIMAPServer']
mailUser = mailini['mailUser']
mailPass = mailini['mailPass']
global mailFromAddress
mailFromAddress = mailini['mailFromAddress']
dbini = ini['Database Server']
dbServer = dbini['dbServer']
dbUser = dbini['dbUser']
dbPass = dbini['dbPass']
dbName = dbini['responseDB']
portaldbServer = dbini['portaldbServer']
portaldbUser = dbini['portaldbUser']
portaldbPass = dbini['portaldbPass']
portaldbName = dbini['portaldbName']
dbPort = ":1433"
fileini = ini['File Locations']
global fileStore
fileStore = fileini['saveFiles']
mimeini = ini['Mime Types']
allowedMime = mimeini['permittedFiles']
t1 = time.time()
# quick test to see what os we are running on
if os.name == "posix":
dbServer = dbServer + dbPort
con = pymssql.connect(host=dbServer, user=dbUser, password=dbPass, database=dbName)
cur = con.cursor()
con2 = pymssql.connect(host=portaldbServer, user=portaldbUser, password=portaldbPass, database=portaldbName)
cur2 = con2.cursor()
imapi = imaplib.IMAP4_SSL(mailIMAPServer)
imapi_user = 'xxxxxxxxxxxxxx'
imapi_pass = 'xxxxxxxxx'
## login to server
print("Logging into mailbox...")
resp_code, response = imapi.login(imapi_user, imapi_pass)
print("Response Code : {}".format(resp_code))
print("Response : {}\n".format(response[0].decode()))
### Completed Config ... Now skip to the end where we do stuff
def sumfile(fobj):
'''Returns an md5 hash for an object with read() method.'''
m = hashlib.new()
while True:
d = fobj.read(4096)
if not d:
break
m.update(d)
return m.hexdigest()
def hashlibsum(fname):
'''Returns an md5 hash for file fname, or stdin if fname is "-".'''
if fname == '-':
ret = sumfile(sys.stdin)
else:
try:
f = file(fname, 'rb')
except:
return 'Failed to open file'
ret = sumfile(f)
f.close()
return ret
def storeFile(part, type, jobNumber, inboxid):
# stores the files that match our allowed mime types list in the folder set in the .ini file
# starts out by grabbing the file and matching the declared mime type to the file extension's mime type
# if it matches then it checks against a list of permitted mime types before:
# storing the file and inserting a record into the database
targetDirName = strftime("%Y%m%d")
createTargetDir = os.path.join(fileStore, targetDirName)
if not os.path.exists(createTargetDir):
os.makedirs(createTargetDir)
typ = part.get_content_type()
maintype = part.get_content_maintype()
if (maintype != "text" and maintype != "multipart" and maintype != "message"):
# we have an attachment!
fileName = part.get_filename()
print
"Filename:"
print
fileName
if fileName == None:
fileName = "Unknown.bad"
fileName = fileName.replace('\\', '')
try:
actualExt = fileName.rsplit(".", 1)
except (AttributeError):
actualExt = ["none", "bad"]
guessExt = mimetypes.guess_extension(part.get_content_type())
print
guessExt
if (guessExt != None):
guessExt = guessExt.replace('.', '')
try:
extFound = actualExt[1] in allowedMime
except (IndexError):
extFound = guessExt
print
guessExt
allowedFile = 0
if (extFound == True): # message is ok - store it
allowedFile = 1
print
"inboxid: %s end\n" % (inboxid)
inboxid = '%d' % (inboxid)
filePath = "%s\\" % (createTargetDir)
try:
fp = open(filePath + fileName + "_" + inboxid, "wb")
except IOError:
try:
print
fileName
fileNameParts = fileName.split("?")
fileName = fileNameParts[3]
fp = open(filePath + fileName + "_" + inboxid, "wb")
except (AttributeError):
print
"Attachment Error"
fileName = "unknown.bad"
fp = open(filePath + fileName + "_" + inboxid, "wb")
fp.write(part.get_payload(decode=1))
fp.close()
fileName = (filePath + fileName + "_" + inboxid)
fileHash = hashlibsum(fileName)
cur.execute("INSERT INTO attachments VALUES ('%s','%s','%s','%s','%s',%d,'%s');" % (
inboxid, jobNumber, filePath, fileName, typ, allowedFile, fileHash))
con.commit()
print
"Attachment %s stored" % (fileName)
return
def sendEmail(emailTo, emailSubj, emailMessage):
emailMsg = MIMEMultipart()
emailFrom = mailFromAddress
emailMsg['From'] = mailFromAddress
emailMsg['To'] = emailTo
emailMsg['Date'] = formatdate(localtime=True)
emailMsg['Subject'] = emailSubj
emailMsg.attach(MIMEText(emailMessage))
smtp = smtplib.SMTP(mailSMTPServer)
if emailTo == None:
return
else:
smtpresult = smtp.sendmail(emailFrom, emailTo, emailMsg.as_string())
smtp.close()
print
"Email sent to %s" % (emailTo)
return
return
def createTicket(fromAddress, msgSubj, msgBody, mailParseable):
# creates a new email
msgType = "mail"
msgNew = 1
jobNumber = ""
print
len(msgBody)
dievar = 0
# if len(msgBody) > 15000:
# msgBody = msgBody[0:15000]
cur.execute("INSERT INTO inbox VALUES (%s,%s,%s,%s,getDate(),%s,%d,%d,%d);",
(jobNumber, fromAddress, msgSubj, msgBody, msgType, msgNew, 0, 0))
inboxid = cur.lastrowid
# print "lastrow (crticket): %d end\n" % (inboxid)
con.commit()
# grab the ID inserted so we can log against the inbox record ... which sucks
# query = "SELECT @@IDENTITY As 'Identity'"
# cur.execute(query)
# con.commit()
# inboxid = (cur.fetchone())[0]
print
"Message added to inbox"
return inboxid
def addTicket(fromAddress, msgSubj, msgBody, jobNumber, mailParseable):
# adds an email to a job
msgType = "mail"
msgNew = 3
# need to check that job number exists and if not then BREAK!!!!
query = "SELECT * FROM jobs WHERE id = %s" % (jobNumber)
# print query
cur.execute(query)
cur.fetchall()
print
"rowcount = %d aargh" % (cur.rowcount)
if cur.rowcount == 0:
con.commit()
print
"Unknown - creating new message"
inboxid = createTicket(fromAddress, msgSubj, msgBody, mailParseable)
return inboxid
else:
con.commit()
cur.execute("INSERT INTO inbox VALUES (%s,%s,%s,%s,getDate(),%s,%d,%d,%d);",
(jobNumber, fromAddress, msgSubj, msgBody, msgType, msgNew, 2, 0))
# print "INSERT INTO inbox VALUES (%s,%s,%s,%s,getDate(),%s,%d,%d,%d);",(jobNumber,fromAddress,msgSubj,msgBody,msgType,msgNew,2,0)
inboxid = cur.lastrowid
# print "lastrow (addticket): %d end\n" % (inboxid)
con.commit()
# grab the ID inserted so we can log against the inbox record ... which sucks
# query = "SELECT @@IDENTITY As 'Identity'"
# cur.execute(query)
# con.commit()
# inboxid = (cur.fetchone())[0]
# ok so the message is logged against the job - now I need to send a mail to the enginner in charge
# sendEmail(emailTo,emailFrom,emailSubj,emailMessage)
query = "SELECT assigned_to FROM jobs WHERE ID='%s'" % (jobNumber)
print
query
cur.execute(query)
# con.commit()
engID = (cur.fetchone())[0]
query = "SELECT email FROM responseusers WHERE ID = %d;" % (engID)
cur2.execute(query)
# con2.commit()
rows = cur2.fetchall()
engEmail = None
for row in rows:
engEmail = row[0]
if engEmail != None:
break
print
engEmail
emailSubj = "An update to job [e%s] has been received" % (jobNumber)
emailMessage = "The call log system has been updated with a new client email\nMessage is:\n%s\n\nGo to http://esecure.evolve-online.com/response/jobdetails.php?id=%s to view this message" % (
msgBody, jobNumber)
if engEmail != None:
sendEmail(engEmail, emailSubj, emailMessage)
print
"Message added to job %s" % (jobNumber)
query = "UPDATE jobs SET state = 1 WHERE ID = %s;" % (jobNumber)
cur.execute(query)
con.commit()
return inboxid
def getMail():
resp_code, mail_count = imapi.select(mailbox="INBOX", readonly=False)
result, data = imapi.uid('search', None, "SEEN")
uidList = data[0].split()
resp_code, mails = imapi.search(None, 'UNSEEN')
mail_ids = mails[0].decode().split()
print("Total Mail IDs : {}\n".format(len(mail_ids)))
for mail_id in mail_ids[:2]:
resp_code, mail_data = imapi.fetch(mail_id, '(RFC822)') ## Fetch mail data.
#if the value mail_data is blank then jump to the end
try:
msg = email.message_from_bytes(mail_data[0][1]) ## Construct Message from mail data
except (UnboundLocalError):
#print("No Data to Contsruct Message")
return
#return cleanup()
#print ("Message %s\n%s\n" % (mail_ids, data[0][1]))
numMessages = len(mail_count) # sets the number of messages to play with
#print ("Found %s new messages" % (numMessages))
# print numMessages
# let's build us some regexes
re_from = re.compile(r"^(From)")
re_to = re.compile(r"^(To)")
re_subj = re.compile(r"^(Subject)")
re_jobNumber = re.compile(r"[\[]+E+[\d{6}]+[\]]",
re.IGNORECASE) # this one is eeeeevil but grabs the job number from the subject
re_fromAddress = re.compile(r"\W[A-Z0-9._%-\'\+]+@[A-Z0-9.-]+\.[A-Z]{2,6}\W",
re.IGNORECASE) # tries to get the pure email address from the From header
# start to loop through each message returned
if numMessages > 0:
print
("Processing emails")
for msg in mails:
print
("-" * 40)
#msgNum = int(msg.split(" ")[0])
#msgSize = int(msg.split(" ")[1])
#mailRaw = string.join(pop.retr(msgNum)[1], "\n")
mailParseable = email.message_from_bytes(mail_data[0][1])
msgFrom = mailParseable.__getitem__('From')
msgRecv = mailParseable.__getitem__('Received')
msgSubj = mailParseable.__getitem__('Subject')
if msgSubj == None:
msgSubj = "No Subject"
msgSubj = msgSubj[0:254]
msgTo = mailParseable.__getitem__('To')
#print("Message: %d, Size: %d bytes" % (msgNum, msgSize))
print("From: %s" % msgFrom)
print("Subj: %s" % msgSubj)
msgBody = ""
if mailParseable.is_multipart():
print ("Mail is Multipart")
for part in mailParseable.walk():
typ = part.get_content_type()
if typ and typ.lower() == "text/plain":
# Found the first text/plain part
msgBody = part.get_payload()
break
else:
msgBody = mailParseable.get_payload()
# jobNumber
tmp_jobNumber = re_jobNumber.search(msgSubj) # attempts to match the job number from the subject
# pass through a quick try block as this will throw an exception if the match does't work
# it took me the best part of an hour to work that out :-)
try:
jobNumber = tmp_jobNumber.group() # uses the group operator to extract the contents of the regex result
jobNumber = jobNumber.replace('[', '')
jobNumber = jobNumber.replace(']', '')
jobNumber = jobNumber.replace('e', '')
jobNumber = jobNumber.replace('E', '')
isNew = 0 # YES!!! You have got it right and this is a reply to an existing job ticket
except (AttributeError): # only handles AttributeErrors - anything else will blow up
isNew = 1 # DOH! Computer says no ... no match on the regex so we're dealing with a new job
jobNumber = ""
# INSERT CLOSED JOB DETECTION HERE
if isNew == 0:
query = "SELECT engclose FROM jobs WHERE ID = %s;" % (jobNumber)
cur.execute(query)
con.commit()
closed = 0
try:
if cur.rowcount > 0:
closed = (cur.fetchone())[0]
except TypeError:
closed = 1
if closed == 1:
isNew = 1
# let's check the from address against our list of permitted senders
# first I need to get the address properly
try:
tmp_fromAddress = re_fromAddress.search(msgFrom)
fromAddress = tmp_fromAddress.group()
fromAddress = fromAddress.replace('<', '')
fromAddress = fromAddress.replace('>', '')
except (AttributeError):
fromAddress = msgFrom
fromAddress = fromAddress.lower()
# got it - now I can build the query string and test if it exists in the DB
insertStatus = False
inboxid = 0
if isNew == 0:
inboxid = addTicket(fromAddress, msgSubj, msgBody, jobNumber, mailParseable)
elif isNew == 1:
print("New message")
inboxid = createTicket(fromAddress, msgSubj, msgBody, mailParseable)
else:
print
"ERROR. is new value not set"
return
if mailParseable.is_multipart():
print
"Processing Attachments"
for part in mailParseable.walk():
typ = part.get_content_type()
storeFile(part, typ, jobNumber, inboxid)
print ("Current date and time : ")
print (now.strftime("%Y-%m-%d %H:%M:%S"))
print("-" * 40)
return
def cleanup():
resp_code, mails = imapi.search(None, "SEEN")
mail_ids = mails[0].decode().split()
print("Total Mail IDs : {}\n".format(len(mail_ids)))
print("Deleting Mails...")
for mail_id in mail_ids[:2]:
resp_code, mail_data = imapi.fetch(mail_id, '(RFC822)') ## Fetch mail data.
message = email.message_from_bytes(mail_data[0][1]) ## Construct Message from mail data
print("Mail ID : {}, Date : {}, Subject : {}".format(mail_id, message.get("Date"), message.get("Subject")))
resp_code, response = imapi.copy(mail_id, 'Deleted Items')
resp_code, response = imapi.store(mail_id, '+FLAGS', '\\Deleted')
print("Response Code : {}".format(resp_code))
print("Response : {}\n".format(response[0].decode()))
resp_code, response = imapi.expunge()
print("Response Code : {}".format(resp_code))
print("Response : {}\n".format(response[0].decode()))
return
getMail() # calls the inital getMail function probably needs wrapping in a better constructor
elapsed = time.time() - t1
print ("Run completed in %d seconds" % (elapsed))
print ("-" * 40)
cur.execute("UPDATE mailGrabberStatus SET Status=1,LastUpdate=GETDATE();")
con.commit()
con.close()
con2.close()
So I believe the part that needs fixing is in the def getmail() section
this is the part I have modified to get the mail from Amazon Workmail,
you can tell from some of the comments in the code, the original guy was a bit quirky
Thanks for your Help
P.S.
this is the log, the script runs every 3 mins, this is no new messages
Output:
----------------------------------------
Logging into mailbox...
Response Code : OK
Response : Logged in
Total Mail IDs : 0
Run completed in 0 seconds
----------------------------------------
Logging into mailbox...
Response Code : OK
Response : Logged in
Total Mail IDs : 0
Run completed in 0 seconds
----------------------------------------
this is with a new message
----------------------------------------
Logging into mailbox...
Response Code : OK
Response : Logged in
Total Mail IDs : 1
From: Forcepoint <[email protected]>
Subj: Webinar: Best practices for deploying DLP for the first time
Mail is Multipart
New message
Current date and time :
2022-01-14 15:10:47
----------------------------------------
Run completed in 1 seconds
----------------------------------------