Cloud Function for sending an e-mail alert on a window being open too long

open_window_alert.py

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from google.cloud import bigquery
from google.cloud import bigquery_storage


def open_window_alert(_):
    project_id = "YOUR_PROJECT_ID"
    from_email = "YOUR_EMAIL"
    from_password = "YOUR_PASSWORD"
    to_email = "YOUR_EMAIL"
    bq_predict_sql = f"""
        DECLARE now_time DATETIME;
        DECLARE lookback_time DATETIME;
        DECLARE monitor_window INT64;
        DECLARE lookback_time_monitor_window DATETIME;
        SET now_time = CURRENT_DATETIME();
        SET lookback_time = DATETIME_SUB(now_time, INTERVAL 2400 SECOND);
        SET monitor_window = 30; --The amount of time a window should be allowed to be open. Max value of 1800s.
        SET lookback_time_monitor_window = DATETIME_SUB(now_time, INTERVAL monitor_window SECOND);
        
        
        CREATE TEMP TABLE past_2400_temps AS SELECT * FROM
        (
          SELECT
            timestamp_utc,
            ANY_VALUE(IF(device_id = 2753085432003927, temp_f, null)) AS device_FrontOfLivingRoom,
            ANY_VALUE(IF(device_id = 2582277131565505, temp_f, null)) AS device_LivingRoomCycleWindow,
            ANY_VALUE(IF(device_id = 2707176363363894, temp_f, null)) AS device_LivingRoomKitchenCorner
          FROM {project_id}.sensordata.temperature t
          WHERE t.timestamp_utc BETWEEN lookback_time AND now_time
          GROUP BY timestamp_utc
        )
        WHERE device_FrontOfLivingRoom IS NOT NULL
          AND device_LivingRoomCycleWindow IS NOT NULL
          AND device_LivingRoomKitchenCorner IS NOT NULL;
        
        CREATE TEMP TABLE past_2400_temps_timediff AS SELECT * FROM (
        SELECT
          timestamp_utc,
          device_FrontOfLivingRoom,
          device_LivingRoomCycleWindow,
          device_LivingRoomKitchenCorner,
          ROUND(device_FrontOfLivingRoom_600s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_600s_diff,
          ROUND(device_FrontOfLivingRoom_300s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_300s_diff,
          ROUND(device_FrontOfLivingRoom_180s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_180s_diff,
          ROUND(device_FrontOfLivingRoom_60s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_60s_diff,
          ROUND(device_FrontOfLivingRoom_30s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_30s_diff,
          ROUND(device_FrontOfLivingRoom_15s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_15s_diff,
          ROUND(device_FrontOfLivingRoom_5s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_5s_diff,
          ROUND(device_LivingRoomCycleWindow_600s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_600s_diff,
          ROUND(device_LivingRoomCycleWindow_300s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_300s_diff,
          ROUND(device_LivingRoomCycleWindow_180s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_180s_diff,
          ROUND(device_LivingRoomCycleWindow_60s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_60s_diff,
          ROUND(device_LivingRoomCycleWindow_30s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_30s_diff,
          ROUND(device_LivingRoomCycleWindow_15s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_15s_diff,
          ROUND(device_LivingRoomCycleWindow_5s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_5s_diff,
          ROUND(device_LivingRoomKitchenCorner_600s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_600s_diff,
          ROUND(device_LivingRoomKitchenCorner_300s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_300s_diff,
          ROUND(device_LivingRoomKitchenCorner_180s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_180s_diff,
          ROUND(device_LivingRoomKitchenCorner_60s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_60s_diff,
          ROUND(device_LivingRoomKitchenCorner_30s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_30s_diff,
          ROUND(device_LivingRoomKitchenCorner_15s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_15s_diff,
          ROUND(device_LivingRoomKitchenCorner_5s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_5s_diff
        FROM (
          SELECT
            temp.timestamp_utc,
            temp.device_FrontOfLivingRoom,
            temp600.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_600s_ago,
            temp300.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_300s_ago,
            temp180.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_180s_ago,
            temp60.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_60s_ago,
            temp30.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_30s_ago,
            temp15.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_15s_ago,
            temp5.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_5s_ago,
            temp.device_LivingRoomCycleWindow,
            temp600.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_600s_ago,
            temp300.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_300s_ago,
            temp180.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_180s_ago,
            temp60.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_60s_ago,
            temp30.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_30s_ago,
            temp15.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_15s_ago,
            temp5.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_5s_ago,
            temp.device_LivingRoomKitchenCorner,
            temp600.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_600s_ago,
            temp300.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_300s_ago,
            temp180.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_180s_ago,
            temp60.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_60s_ago,
            temp30.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_30s_ago,
            temp15.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_15s_ago,
            temp5.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_5s_ago
          FROM past_2400_temps temp
          INNER JOIN past_2400_temps temp600 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 600 SECOND) = temp600.timestamp_utc
          INNER JOIN past_2400_temps temp300 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 300 SECOND) = temp300.timestamp_utc
          INNER JOIN past_2400_temps temp180 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 180 SECOND) = temp180.timestamp_utc
          INNER JOIN past_2400_temps temp60 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 60 SECOND) = temp60.timestamp_utc
          INNER JOIN past_2400_temps temp30 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 30 SECOND) = temp30.timestamp_utc
          INNER JOIN past_2400_temps temp15 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 15 SECOND) = temp15.timestamp_utc
          INNER JOIN past_2400_temps temp5 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 5 SECOND) = temp5.timestamp_utc
          WHERE temp.timestamp_utc BETWEEN lookback_time AND now_time
            AND temp.device_FrontOfLivingRoom IS NOT NULL
            AND temp.device_LivingRoomCycleWindow IS NOT NULL
            AND temp.device_LivingRoomKitchenCorner IS NOT NULL
        )
        WHERE timestamp_utc BETWEEN lookback_time_monitor_window AND now_time
        );
        
        SELECT
          predicted_object_code,
          COUNT(*) AS prediction_count
        FROM
          ML.PREDICT(MODEL {project_id}.sensordata.temperature_automl_model,
               (SELECT * EXCEPT (timestamp_utc) FROM past_2400_temps_timediff))
        GROUP BY predicted_object_code
        ORDER BY prediction_count DESC;
    """
    client = bigquery.Client()
    bqstorageclient = bigquery_storage.BigQueryReadClient()
    query_job = client.query(bq_predict_sql)
    results = query_job.result().to_dataframe(bqstorage_client=bqstorageclient)
    total_observations = results["prediction_count"].sum()
    sorted_results = results[results["predicted_object_code"] != 0].sort_values(by=["prediction_count"], ascending=False)
    window_number = sorted_results["predicted_object_code"].to_list()[0]
    window_count = sorted_results["prediction_count"].to_list()[0]
    if window_count / total_observations >= 0.95:
        # Send an e-mail when 95% of the past x seconds worth of predicted window states report that one particular window is open
        subject = f"Window {window_number} is open"
        email_body = f"Window {window_number} has been open for too long. Close it!"
        msg = MIMEMultipart()
        msg["Subject"] = subject
        msg["To"] = to_email
        msg["From"] = from_email
        msg.attach(MIMEText(email_body))
        with smtplib.SMTP('smtp.gmail.com', 587) as s:
            s.ehlo()
            s.starttls()
            s.login(from_email, from_password)
            s.send_message(msg)