Add email address to macOS alerts DB to enable email alerts

enable_email_alerts.py

#!/usr/bin/python

import sqlite3
import sys
import time
import os
import csv

# Variables and paths
DB_PATH = '/Library/Server/Alerts/alertData.db'


def timestamp():
    return time.strftime("%a %b %d %H:%M:%S")


def log(message):
    print '%s %s' % (timestamp(), message)


def create_db():
    log(('- Creating %s ...') % (DB_PATH))
    try: 
        open_database()
        c.execute("CREATE TABLE ZADALERT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCREATIONDATE TIMESTAMP, ZREADDATE TIMESTAMP, ZRESOLVEDDATE TIMESTAMP, ZSENTDATE TIMESTAMP, ZBUNDLE VARCHAR, ZIDENTIFIER VARCHAR, ZTYPE VARCHAR, ZATTRIBUTES BLOB )")
        c.execute("CREATE TABLE ZADALERTBUNDLE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZENABLED INTEGER, ZMAILENABLED INTEGER, ZPUSHENABLED INTEGER, ZBUNDLE VARCHAR, ZNAME VARCHAR )")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(1,2,2,1,0,0,'CertificateAlerts','Certificate')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(2,2,2,1,1,1,'Caching','Caching')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(3,2,2,1,0,0,'CalendarContactsAlerts','CalendarAndContacts')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(4,2,2,1,0,0,'Firewall','Firewall')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(5,2,2,1,0,0,'Mail','Mail')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(6,2,2,1,0,0,'TimeMachine','Time Machine')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(7,2,1,1,1,1,'Common','Common')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(8,2,2,1,0,0,'SoftwareUpdate','Software Update')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(9,2,2,1,0,0,'NetworkConfiguration','Network Configuration')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(10,2,2,1,0,0,'Xsan','Xsan')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(11,2,2,1,0,0,'XcodeServer','Xcode Server')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(12,2,2,1,0,0,'ProfileManager','Profile Manager')")
        c.execute("INSERT INTO ZADALERTBUNDLE VALUES(13,2,2,1,0,0,'Disk','Disk')")
        c.execute("CREATE TABLE ZADATTRIBUTE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZNAME VARCHAR, ZVALUE VARCHAR )")
        c.execute("INSERT INTO ZADATTRIBUTE VALUES(1,3,12,'pushEnabled','0')")
        c.execute("CREATE TABLE ZADMAILRECIPIENT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZENABLED INTEGER, ZADDRESS VARCHAR, ZLOCALIZATION VARCHAR )")
        c.execute("CREATE TABLE ZADPUSHRECIPIENT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZENABLED INTEGER, ZGUID VARCHAR, ZLOCALIZATION VARCHAR, ZNAME VARCHAR, ZTOKEN BLOB )")
        c.execute("CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER)")
        c.execute("INSERT INTO Z_PRIMARYKEY VALUES(1,'ADAlert',0,30)")
        c.execute("INSERT INTO Z_PRIMARYKEY VALUES(2,'ADAlertBundle',0,13)")
        c.execute("INSERT INTO Z_PRIMARYKEY VALUES(3,'ADAttribute',0,1)")
        c.execute("INSERT INTO Z_PRIMARYKEY VALUES(4,'ADMailRecipient',0,2)")
        c.execute("INSERT INTO Z_PRIMARYKEY VALUES(5,'ADPushRecipient',0,0)")
        c.execute("CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), Z_PLIST BLOB)")
        c.execute("CREATE TABLE Z_MODELCACHE (Z_CONTENT BLOB)")
    except Exception as err:
        log(('[ERROR] There was an error inserting tables into the database. Error code: %s') % (err))
        sys.exit(1)
    write_changes()
    close_database()


def open_database():
    global conn
    global c
    try:
        log(('- Opening Alert Database %s ...') % (DB_PATH))
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
    except Exception as err: 
        log(('[ERROR] Unable to open %s  Error code %s') % (DB_PATH, err))
        sys.exit(1)
    log('[OK] - DB opened.')


def close_database():
    try: 
        log('- Closing Database ...')
        conn.close()
    except:
        log('[ERROR] Unable to close Database.')
        sys.exit(1)
    log('[OK] - DB closed.')
    log(' ')


def write_changes():
    try:
        log('- Writing changes to DB')
        conn.commit()
    except Exception as err:
        log(('[ERROR] There was an error writing changes to DB. Error code: %s') % (err))
    log('[OK] - Changes written successfully.')
    log(' ')


def insert_notification_email():
    # COLUMNS = (Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZENABLED INTEGER, ZADDRESS VARCHAR, ZLOCALIZATION VARCHAR)
    open_database()
    with open('/Users/Shared/alert_email.csv') as raw_email_list:
        ALERT_EMAIL = csv.reader(raw_email_list, skipinitialspace=True)
        for row in ALERT_EMAIL:
            num_cols = len(row)
            log(('- We have %s email address(es) to add:') % (num_cols))
            pk_idx = num_cols + 1
            item = num_cols - 1
            while (item != -1):
                email_address = row[item]
                log((' - Email address number: %s is: %s') % (item,email_address))
                log((' - Inserting email address: %s into Alert Notification DB with a Z_PK index of: %s ...') % (email_address,pk_idx))
                log(' ')
                pk_idx = pk_idx -1
                item = item - 1
                c.execute("INSERT or REPLACE INTO ZADMAILRECIPIENT VALUES('%s','4','1','1','%s','en')" % (pk_idx,email_address))
    write_changes()
    close_database()
    sys.exit(0)


## Start -
log(('- Checking for presence of %s ...') % (DB_PATH))
if os.path.isfile(DB_PATH):
    log(('[OK] %s exists!') % (DB_PATH))
    log(' ')
    insert_notification_email()
else:
    log(('[WARN] %s does NOT exist!') % (DB_PATH))
    log(' ')
    create_db()
    insert_notification_email()

Hi,

Can you please give me the .csv format sample file.

Thank You
Arun