163 lines
5.9 KiB
Python
163 lines
5.9 KiB
Python
#!/usr/bin/python3
|
|
"""
|
|
Licensed under the Apache License, Version 2.0 (the "License");
|
|
you may not use this file except in compliance with the License.
|
|
You may obtain a copy of the License at
|
|
|
|
http://www.apache.org/licenses/LICENSE-2.0
|
|
|
|
Unless required by applicable law or agreed to in writing, software
|
|
distributed under the License is distributed on an "AS IS" BASIS,
|
|
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
|
See the License for the specific language governing permissions and
|
|
limitations under the License.
|
|
"""
|
|
|
|
from config.Reader import reader
|
|
from sfconnect.Connecter import connecter
|
|
from sql.Queries import sfquery
|
|
from function.Writer import csvwrite
|
|
import os, datetime, socket
|
|
import base64
|
|
import smtplib
|
|
from email.mime.multipart import MIMEMultipart
|
|
from email.mime.text import MIMEText
|
|
|
|
|
|
def emailsupport(messagetype, messagefile):
|
|
"""Send to Email"""
|
|
me = options["sender"]
|
|
# you = ['HTTP_509@LibertyMutual.com', 'PMEDW_Prod_Support@LibertyMutual.com']
|
|
you = options["receivers"].split()
|
|
|
|
# Open a plain text file for reading. For this example, assume that
|
|
# the text file contains only ASCII characters.
|
|
message_part = open(messagefile, "r")
|
|
part2 = MIMEText(message_part.read(), "html")
|
|
# Create a text/plain message
|
|
message_part.close()
|
|
|
|
msg = MIMEMultipart("alternative")
|
|
|
|
msg["Subject"] = messagetype
|
|
|
|
msg["From"] = me
|
|
msg["To"] = ",".join(you)
|
|
|
|
msg.attach(part2)
|
|
|
|
# Send the message via our own SMTP server, but don't include the envelope header.
|
|
sendmail(me, you, msg.as_string())
|
|
|
|
|
|
# Set header automation statement for email
|
|
def openemail(emailfile):
|
|
emailfile.write(
|
|
"<p>THIS IS AN AUTOMATED NOTIFICATION. PLEASE DO NOT RESPOND TO THIS E-MAIL.</p>"
|
|
)
|
|
emailfile.write("<html>\n")
|
|
emailfile.write("<table border='1'>\n")
|
|
|
|
|
|
# Set footer automation statement for email
|
|
def closeemail(emailfile):
|
|
emailfile.write("</table>\n")
|
|
emailfile.write(
|
|
"<p>THIS IS AN AUTOMATED NOTIFICATION. PLEASE DO NOT RESPOND TO THIS E-MAIL.</p>"
|
|
)
|
|
emailfile.write("\t</body>")
|
|
emailfile.write("</html>\n")
|
|
|
|
|
|
def sendmail(me, you, mailmsg):
|
|
# Send the message via our own SMTP server, but don't include the envelope header.
|
|
try:
|
|
s = smtplib.SMTP(options["mailhost"])
|
|
s.sendmail(me, you, mailmsg)
|
|
s.quit()
|
|
except smtplib.SMTPException:
|
|
print(
|
|
"Email was drafted but I was unable to send it. Unable to connect to mailserver"
|
|
)
|
|
return 1
|
|
else:
|
|
return 0
|
|
|
|
|
|
def get_options():
|
|
creader = reader()
|
|
options = creader.retrieve("./settings/sync_settings.ini")
|
|
return options
|
|
|
|
|
|
def sfconnect(sfparams):
|
|
connect = connecter()
|
|
conn = connect.connect(sfparams)
|
|
return conn
|
|
|
|
|
|
def get_sql(options):
|
|
sfretrieve = sfquery()
|
|
queries = sfretrieve.retrieve(options)
|
|
return queries
|
|
|
|
|
|
if __name__ == "__main__":
|
|
options = get_options()
|
|
sfqueries = get_sql(options)
|
|
nowstamp = datetime.datetime.now()
|
|
script_name = os.path.basename(__file__)
|
|
|
|
for login in options["logins"]:
|
|
(database, schema, conn) = sfconnect(options["logins"][login])
|
|
cur = conn.cursor()
|
|
checkquery = options["check_suspended"]
|
|
cur.execute(checkquery)
|
|
|
|
rows = 0
|
|
|
|
cur.get_results_from_sfqid(cur.sfqid)
|
|
results = cur.fetchall()
|
|
conn.close()
|
|
if len(results) == 0:
|
|
print("No suspended tables found.")
|
|
else:
|
|
timestamp = nowstamp.strftime("%m/%d/%Y, %H:%M:%S")
|
|
html_report = open("email_template.html", "r")
|
|
suspended_list = []
|
|
suspended = open("suspended.html", "w")
|
|
openemail(suspended)
|
|
for line in html_report:
|
|
suspended.write(line.replace("DateNow", timestamp))
|
|
suspended.write(
|
|
' <table class="basic_lines" summary="Table Listing">\n'
|
|
)
|
|
suspended.write(
|
|
f'<tr><th colspan="8">Qlik Task(s) found with suspended tables:</th></tr>\n'
|
|
)
|
|
suspended.write(
|
|
f'\t\t\t\t<tr><th class="odd_row_data">ENVIRONMENT</th><th class="odd_row_data">SERVER NAME</th><th class="odd_row_data" colspan="2">TASK NAME</th><th class="odd_row_data">OWNER</th><th class="odd_row_data">TABLE NAME</th><th class="odd_row_data">SUSPEND REASON</th><th class="odd_row_data">SUSPEND TIMESTAMP</th></tr>\n'
|
|
)
|
|
print(f"{len(results)} suspended tables detected.")
|
|
for idx, sfobject in enumerate(results):
|
|
numcheck = idx + 2
|
|
if numcheck % 2 == 0:
|
|
suspended.write(
|
|
f'\t\t\t\t<tr><td class="even_row_data">Environment</td><td class="even_row_data">{results[idx][0]}</td><td class="even_row_data block" colspan="2">{results[idx][1]}</td><td class="even_row_data">{results[idx][2]}</td><td class="even_row_data">{results[idx][3]}</td><td class="even_row_data">{results[idx][4]}</td><td class="even_row_data block" >{results[idx][5]}</td></tr>\n'
|
|
)
|
|
else:
|
|
suspended.write(
|
|
f'\t\t\t\t<tr><td class="odd_row_data">Environment</td><td class="odd_row_data">{results[idx][0]}</td><td class="odd_row_data block" colspan="2">{results[idx][1]}</td><td class="odd_row_data">{results[idx][2]}</td><td class="odd_row_data">{results[idx][3]}</td><td class="odd_row_data">{results[idx][4]}</td><td class="odd_row_data block">{results[idx][5]}</td></tr>\n'
|
|
)
|
|
suspended.write(" </table>\n")
|
|
closeemail(suspended)
|
|
suspended.close()
|
|
emailsupport(
|
|
"SUSPENDED TABLES DETECTED IN QLIK REPLICATE", "suspended.html"
|
|
)
|
|
nowstamp = datetime.datetime.now()
|
|
os.remove("suspended.html")
|
|
print(f"Endtime: {nowstamp}")
|
|
|
|
exit()
|