check_certs.py

#!/usr/bin/python
import MySQLdb
import ssl
import socket
from datetime import datetime
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# MySQLデータベース接続設定
db_config = {
    'user': 'zabbix',
    'passwd': 'PASSWORD_HERE',
    'host': 'localhost',
    'db': 'zabbix'
}

# メール送信設定
email_sender = 'zabbix@example.com'
email_receiver = 'you@example.com'
smtp_server = 'localhost'
smtp_port = 25

def get_ssl_expiry_days(hostname):
    context = ssl.create_default_context()
    with socket.create_connection((hostname, 443)) as sock:
        with context.wrap_socket(sock, server_hostname=hostname) as ssock:
            cert = ssock.getpeercert()
    expiry_date = datetime.strptime(cert['notAfter'], '%b %d %H:%M:%S %Y %Z')
    remaining_days = (expiry_date - datetime.now()).days
    return remaining_days

def send_email(subject, message):
    msg = MIMEMultipart()
    msg['From'] = email_sender
    msg['To'] = email_receiver
    msg['Subject'] = subject
    msg.attach(MIMEText(message, 'plain'))
    server = smtplib.SMTP(smtp_server, smtp_port)
    #server.starttls()
    #server.login(email_sender, email_password)
    text = msg.as_string()
    server.sendmail(email_sender, email_receiver, text)
    server.quit()

def main():
    # MySQLデータベースからFQDNの取得
    connection = MySQLdb.connect(**db_config)
    cursor = connection.cursor()
    query = ("select distinct SUBSTRING_INDEX(SUBSTRING_INDEX(url, '//', -1), '/', 1) "
             "from httpstep where url like 'https://%';")
    cursor.execute(query)

    for fqdn in cursor.fetchall():
        try:
            days_left = get_ssl_expiry_days(fqdn[0])
            print("%s: days_left = %d" % (fqdn[0], days_left))
            if days_left < 21:
                message = f"{fqdn[0]} の証明書の有効期限は、あと {days_left} 日です。"
                print(message)
                send_email("SSL証明書期限警告", message)
        except Exception as e:
            print(f"Error processing {fqdn[0]}: {e}")

    cursor.close()
    connection.close()

if __name__ == "__main__":
    main()