317 lines
9.9 KiB
Python
317 lines
9.9 KiB
Python
|
import os
|
||
|
|
import smtplib
|
||
|
|
import logging
|
||
|
|
from datetime import datetime, timezone, timedelta
|
||
|
|
from email.mime.text import MIMEText
|
||
|
|
from email.mime.multipart import MIMEMultipart
|
||
|
|
|
||
|
|
import psycopg2
|
||
|
|
|
||
|
|
from app.core.config import (
|
||
|
|
DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD,
|
||
|
|
MAIL_HOST, MAIL_PORT, MAIL_FROM,
|
||
|
|
WATCHDOG_RECIPIENT_EMAILS, WATCHDOG_CC_EMAILS,
|
||
|
|
)
|
||
|
|
|
||
|
|
STALE_THRESHOLD_H = 1
|
||
|
|
COOLDOWN_H = 2
|
||
|
|
COOLDOWN_FILE = '/tmp/etl_alert_sent'
|
||
|
|
|
||
|
|
logging.basicConfig(level=logging.INFO, format='%(asctime)s %(levelname)s: %(message)s')
|
||
|
|
log = logging.getLogger(__name__)
|
||
|
|
|
||
|
|
|
||
|
|
def get_last_run():
|
||
|
|
conn = psycopg2.connect(
|
||
|
|
host=DB_HOST, port=DB_PORT, dbname=DB_NAME,
|
||
|
|
user=DB_USER, password=DB_PASSWORD, connect_timeout=5,
|
||
|
|
)
|
||
|
|
try:
|
||
|
|
cur = conn.cursor()
|
||
|
|
cur.execute("SELECT source, last_run_at FROM watermarks ORDER BY last_run_at DESC LIMIT 1")
|
||
|
|
return cur.fetchone()
|
||
|
|
finally:
|
||
|
|
conn.close()
|
||
|
|
|
||
|
|
|
||
|
|
def in_cooldown():
|
||
|
|
if not os.path.exists(COOLDOWN_FILE):
|
||
|
|
return False
|
||
|
|
mtime = datetime.fromtimestamp(os.path.getmtime(COOLDOWN_FILE), tz=timezone.utc)
|
||
|
|
return (datetime.now(timezone.utc) - mtime) < timedelta(hours=COOLDOWN_H)
|
||
|
|
|
||
|
|
|
||
|
|
def mark_cooldown():
|
||
|
|
with open(COOLDOWN_FILE, 'w') as f:
|
||
|
|
f.write(datetime.now(timezone.utc).isoformat())
|
||
|
|
|
||
|
|
|
||
|
|
def clear_cooldown():
|
||
|
|
if os.path.exists(COOLDOWN_FILE):
|
||
|
|
os.remove(COOLDOWN_FILE)
|
||
|
|
|
||
|
|
|
||
|
|
def build_html(source, last_run_at, horas, mins):
|
||
|
|
last_run_brt = last_run_at - timedelta(hours=3)
|
||
|
|
last_run_str = last_run_brt.strftime('%d/%m %H:%M BRT')
|
||
|
|
tempo_str = f"{horas}h{mins:02d}min"
|
||
|
|
|
||
|
|
return f"""<!DOCTYPE html>
|
||
|
|
<html lang="pt-BR">
|
||
|
|
<head>
|
||
|
|
<meta charset="utf-8">
|
||
|
|
<meta name="viewport" content="width=device-width, initial-scale=1">
|
||
|
|
<style>
|
||
|
|
body {{
|
||
|
|
margin: 0; padding: 32px 16px;
|
||
|
|
background: #E8EDE9;
|
||
|
|
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Helvetica, Arial, sans-serif;
|
||
|
|
}}
|
||
|
|
.email {{
|
||
|
|
max-width: 580px; margin: 0 auto;
|
||
|
|
background: #fff; border-radius: 4px; overflow: hidden;
|
||
|
|
box-shadow: 0 2px 16px rgba(0,66,30,.12);
|
||
|
|
}}
|
||
|
|
.hd {{
|
||
|
|
background: #00843D; padding: 28px 36px 24px;
|
||
|
|
display: flex; align-items: center; justify-content: space-between;
|
||
|
|
}}
|
||
|
|
.hd-eyebrow {{
|
||
|
|
font-size: 10px; letter-spacing: .18em; text-transform: uppercase;
|
||
|
|
color: rgba(255,255,255,.65); font-weight: 600; display: block; margin-bottom: 4px;
|
||
|
|
}}
|
||
|
|
.hd-name {{
|
||
|
|
font-size: 20px; font-weight: 800; color: #fff; letter-spacing: .04em;
|
||
|
|
}}
|
||
|
|
.hd-icon {{ font-size: 38px; line-height: 1; }}
|
||
|
|
.banner {{
|
||
|
|
background: #FFF8E1; border-top: 3px solid #F59E0B;
|
||
|
|
border-bottom: 1px solid #FDE68A; padding: 14px 36px;
|
||
|
|
font-size: 12px; font-weight: 700; letter-spacing: .06em;
|
||
|
|
text-transform: uppercase; color: #92400E;
|
||
|
|
}}
|
||
|
|
.body {{ padding: 32px 36px 0; }}
|
||
|
|
.headline {{
|
||
|
|
font-size: 22px; font-weight: 800; color: #0D2118;
|
||
|
|
line-height: 1.25; margin: 0 0 12px;
|
||
|
|
}}
|
||
|
|
.headline span {{ color: #00843D; }}
|
||
|
|
.subline {{
|
||
|
|
font-size: 14px; line-height: 1.65; color: #3D5C48;
|
||
|
|
margin: 0 0 28px;
|
||
|
|
}}
|
||
|
|
.stats {{
|
||
|
|
display: table; width: 100%; border-collapse: collapse;
|
||
|
|
border: 1px solid #D4E6DA; border-radius: 6px;
|
||
|
|
overflow: hidden; margin-bottom: 28px;
|
||
|
|
}}
|
||
|
|
.stat {{
|
||
|
|
display: table-cell; background: #fff;
|
||
|
|
padding: 14px 16px; border-right: 1px solid #D4E6DA;
|
||
|
|
width: 33.33%;
|
||
|
|
}}
|
||
|
|
.stat:last-child {{ border-right: none; }}
|
||
|
|
.stat-label {{
|
||
|
|
font-size: 10px; letter-spacing: .1em; text-transform: uppercase;
|
||
|
|
color: #6B8C7A; font-weight: 600; display: block; margin-bottom: 3px;
|
||
|
|
}}
|
||
|
|
.stat-value {{ font-size: 15px; font-weight: 700; color: #0D2118; }}
|
||
|
|
.stat-value.bad {{ color: #C0392B; }}
|
||
|
|
.cmd-block {{
|
||
|
|
background: #F2F7F4; border: 1px solid #C8DDD1;
|
||
|
|
border-radius: 6px; padding: 16px 20px; margin-bottom: 28px;
|
||
|
|
}}
|
||
|
|
.cmd-label {{
|
||
|
|
font-size: 10px; letter-spacing: .1em; text-transform: uppercase;
|
||
|
|
color: #6B8C7A; font-weight: 600; margin-bottom: 10px;
|
||
|
|
}}
|
||
|
|
.cmd {{
|
||
|
|
font-family: 'SF Mono', 'Fira Code', Consolas, monospace;
|
||
|
|
font-size: 12px; color: #00843D; line-height: 1.9;
|
||
|
|
}}
|
||
|
|
.prompt {{ color: #A0B8A8; }}
|
||
|
|
.note {{
|
||
|
|
font-size: 13px; color: #5A7A63; line-height: 1.6;
|
||
|
|
font-style: italic; padding-bottom: 32px;
|
||
|
|
}}
|
||
|
|
.footer {{
|
||
|
|
background: #F2F7F4; border-top: 1px solid #D4E6DA;
|
||
|
|
padding: 18px 36px; display: flex;
|
||
|
|
align-items: center; justify-content: space-between;
|
||
|
|
}}
|
||
|
|
.footer-text {{ font-size: 11px; color: #8AAD98; line-height: 1.5; }}
|
||
|
|
.badge {{
|
||
|
|
font-size: 10px; letter-spacing: .08em; text-transform: uppercase;
|
||
|
|
color: #fff; background: #00843D; padding: 4px 8px;
|
||
|
|
border-radius: 3px; font-weight: 700;
|
||
|
|
}}
|
||
|
|
</style>
|
||
|
|
</head>
|
||
|
|
<body>
|
||
|
|
<div class="email">
|
||
|
|
<div class="hd">
|
||
|
|
<div>
|
||
|
|
<span class="hd-eyebrow">Monitoramento</span>
|
||
|
|
<span class="hd-name">Heineken House</span>
|
||
|
|
</div>
|
||
|
|
<span class="hd-icon">🍺</span>
|
||
|
|
</div>
|
||
|
|
|
||
|
|
<div class="banner">⚠ Pipeline parado — ação necessária</div>
|
||
|
|
|
||
|
|
<div class="body">
|
||
|
|
<h1 class="headline">
|
||
|
|
Pausa na breja e nos drinks.<br>
|
||
|
|
<span>O ETL caiu.</span>
|
||
|
|
</h1>
|
||
|
|
<p class="subline">
|
||
|
|
O pipeline de dados do WiFi da Heineken House está parado há mais de
|
||
|
|
{STALE_THRESHOLD_H}h. Os dados do dashboard podem estar desatualizados.
|
||
|
|
Dê uma olhada quando puder.
|
||
|
|
</p>
|
||
|
|
|
||
|
|
<table class="stats">
|
||
|
|
<tr>
|
||
|
|
<td class="stat">
|
||
|
|
<span class="stat-label">Última fonte</span>
|
||
|
|
<span class="stat-value">{source}</span>
|
||
|
|
</td>
|
||
|
|
<td class="stat">
|
||
|
|
<span class="stat-label">Última execução</span>
|
||
|
|
<span class="stat-value">{last_run_str}</span>
|
||
|
|
</td>
|
||
|
|
<td class="stat">
|
||
|
|
<span class="stat-label">Tempo parado</span>
|
||
|
|
<span class="stat-value bad">{tempo_str}</span>
|
||
|
|
</td>
|
||
|
|
</tr>
|
||
|
|
</table>
|
||
|
|
|
||
|
|
<div class="cmd-block">
|
||
|
|
<div class="cmd-label">Para verificar no servidor</div>
|
||
|
|
<div class="cmd">
|
||
|
|
<span class="prompt">$ </span>docker exec wifi_etl_worker tail -50 /var/log/wifi-etl.log<br>
|
||
|
|
<span class="prompt">$ </span>docker logs wifi_etl_worker
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
|
||
|
|
<p class="note">
|
||
|
|
Este alerta não será repetido nas próximas {COOLDOWN_H}h. Se o problema
|
||
|
|
persistir, um novo aviso será enviado automaticamente.
|
||
|
|
</p>
|
||
|
|
</div>
|
||
|
|
|
||
|
|
<div class="footer">
|
||
|
|
<span class="footer-text">
|
||
|
|
Disparado automaticamente pelo watchdog ETL<br>
|
||
|
|
Heineken House · WiFi Analytics
|
||
|
|
</span>
|
||
|
|
<span class="badge">Goleiro</span>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>"""
|
||
|
|
|
||
|
|
|
||
|
|
def build_plain(source, last_run_at, horas, mins):
|
||
|
|
last_run_brt = last_run_at - timedelta(hours=3)
|
||
|
|
return (
|
||
|
|
f"HEINEKEN HOUSE — ETL PARADO\n"
|
||
|
|
f"{'=' * 40}\n\n"
|
||
|
|
f"Pausa na breja e nos drinks. O ETL caiu.\n\n"
|
||
|
|
f"Última fonte : {source}\n"
|
||
|
|
f"Última execução : {last_run_brt.strftime('%d/%m/%Y %H:%M BRT')}\n"
|
||
|
|
f"Tempo parado : {horas}h{mins:02d}min\n\n"
|
||
|
|
f"Para verificar:\n"
|
||
|
|
f" docker exec wifi_etl_worker tail -50 /var/log/wifi-etl.log\n"
|
||
|
|
f" docker logs wifi_etl_worker\n\n"
|
||
|
|
f"Este alerta não será repetido nas próximas {COOLDOWN_H}h.\n"
|
||
|
|
)
|
||
|
|
|
||
|
|
|
||
|
|
def send_alert(subject, source, last_run_at, horas, mins):
|
||
|
|
recipients = [e.strip() for e in WATCHDOG_RECIPIENT_EMAILS.split(',') if e.strip()]
|
||
|
|
cc = [e.strip() for e in WATCHDOG_CC_EMAILS.split(',') if e.strip()]
|
||
|
|
|
||
|
|
msg = MIMEMultipart('alternative')
|
||
|
|
msg['From'] = MAIL_FROM
|
||
|
|
msg['To'] = ', '.join(recipients)
|
||
|
|
msg['Subject'] = subject
|
||
|
|
if cc:
|
||
|
|
msg['Cc'] = ', '.join(cc)
|
||
|
|
|
||
|
|
msg.attach(MIMEText(build_plain(source, last_run_at, horas, mins), 'plain', 'utf-8'))
|
||
|
|
msg.attach(MIMEText(build_html(source, last_run_at, horas, mins), 'html', 'utf-8'))
|
||
|
|
|
||
|
|
with smtplib.SMTP(MAIL_HOST, MAIL_PORT, timeout=10) as smtp:
|
||
|
|
smtp.sendmail(MAIL_FROM, recipients + cc, msg.as_string())
|
||
|
|
|
||
|
|
log.info(f"Alerta enviado → {recipients + cc}")
|
||
|
|
|
||
|
|
|
||
|
|
def send_db_error_alert(error):
|
||
|
|
recipients = [e.strip() for e in WATCHDOG_RECIPIENT_EMAILS.split(',') if e.strip()]
|
||
|
|
cc = [e.strip() for e in WATCHDOG_CC_EMAILS.split(',') if e.strip()]
|
||
|
|
|
||
|
|
subject = "[ETL ALERTA] Falha ao conectar no banco de dados"
|
||
|
|
body = (
|
||
|
|
f"HEINEKEN HOUSE — ETL ALERTA\n\n"
|
||
|
|
f"O watchdog não conseguiu conectar no banco de dados.\n\n"
|
||
|
|
f"Erro: {error}\n\n"
|
||
|
|
f"Para verificar:\n"
|
||
|
|
f" docker logs wifi_etl_worker\n"
|
||
|
|
f" docker exec wifi_etl_worker tail -50 /var/log/wifi-etl.log\n"
|
||
|
|
)
|
||
|
|
|
||
|
|
msg = MIMEText(body, 'plain', 'utf-8')
|
||
|
|
msg['From'] = MAIL_FROM
|
||
|
|
msg['To'] = ', '.join(recipients)
|
||
|
|
msg['Subject'] = subject
|
||
|
|
if cc:
|
||
|
|
msg['Cc'] = ', '.join(cc)
|
||
|
|
|
||
|
|
with smtplib.SMTP(MAIL_HOST, MAIL_PORT, timeout=10) as smtp:
|
||
|
|
smtp.sendmail(MAIL_FROM, recipients + cc, msg.as_string())
|
||
|
|
|
||
|
|
|
||
|
|
def main():
|
||
|
|
log.info("=== WATCHDOG START ===")
|
||
|
|
|
||
|
|
try:
|
||
|
|
row = get_last_run()
|
||
|
|
except Exception as e:
|
||
|
|
log.error(f"Erro ao conectar no banco: {e}")
|
||
|
|
if not in_cooldown():
|
||
|
|
send_db_error_alert(e)
|
||
|
|
mark_cooldown()
|
||
|
|
return
|
||
|
|
|
||
|
|
if not row:
|
||
|
|
log.warning("Nenhum watermark encontrado — ETL nunca rodou?")
|
||
|
|
return
|
||
|
|
|
||
|
|
source, last_run_at = row
|
||
|
|
age = datetime.now(timezone.utc) - last_run_at
|
||
|
|
horas = int(age.total_seconds() // 3600)
|
||
|
|
mins = int((age.total_seconds() % 3600) // 60)
|
||
|
|
|
||
|
|
log.info(f"Último ETL: {source} em {last_run_at} ({horas}h{mins:02d}min atrás)")
|
||
|
|
|
||
|
|
if age > timedelta(hours=STALE_THRESHOLD_H):
|
||
|
|
if in_cooldown():
|
||
|
|
log.info("ETL parado mas em cooldown — alerta já enviado recentemente")
|
||
|
|
return
|
||
|
|
|
||
|
|
subject = f"[ETL ALERTA] Heineken House — pipeline parado há {horas}h{mins:02d}min"
|
||
|
|
send_alert(subject, source, last_run_at, horas, mins)
|
||
|
|
mark_cooldown()
|
||
|
|
else:
|
||
|
|
log.info("ETL saudável")
|
||
|
|
clear_cooldown()
|
||
|
|
|
||
|
|
log.info("=== WATCHDOG DONE ===")
|
||
|
|
|
||
|
|
|
||
|
|
if __name__ == '__main__':
|
||
|
|
main()
|