Automating macOS Server alerts

Update: 14-11-2016

So I thought I better add support for adding multiple email addresses.

Each email address that gets imported needs an updated index number to go into the Z_PK column. So the script will now import multiple email addresses and assign a Z_PK index number for each email address.

The script now will read in a CSV file instead of taking stdin for input. You will need to specify the location of the CSV file in the script, or modify as needed.

Update: 17-10-2016

So after a bit of further testing, it appears that the alertData.db does not get created automatically at installation of Server.app and requires the Alert tab be selected in Server.app for the db to be created. This presented a problem for me as I am automating the deployment of these macOS server machines and I want to include the alert email settings with zero touch. So some more digging around with the alertData.db and I was able to find the tables and values needed to create a bare database that enables the caching service to be enabled for alerts. The updated script now will create the alertData.db if it does not exist, enable email alerts for caching server (no other services are enabled) and then sets the notification recipients email address.

If you wish to enable notifications for extra services such as Mail, you should add a ‘1’ to the ZMAILENABLED and ZPUSHENABLED columns for the relevant service in the script where it inserts these values to the relevant column.

For example:
The table (ZADALERTBUNDLE) contains the following column names and types:

(Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZENABLED INTEGER, ZMAILENABLED INTEGER, ZPUSHENABLED INTEGER, ZBUNDLE VARCHAR, ZNAME VARCHAR)

The columns we are interested in are ZMAILENABLED and ZPUSHENABLED. These columns will accept a data type of: INTEGER. In actual fact, this is really a boolean with either a 0 (False) or a 1 (True) value assigned.

Here is an example of what having the caching service enabled for mail and push notifications looks like in our alertData.db.
You can also see that the Mail service has mail and push notifications disabled

Z_PK        Z_ENT       Z_OPT       ZENABLED    ZMAILENABLED  ZPUSHENABLED  ZBUNDLE            ZNAME      
----------  ----------  ----------  ----------  ------------  ------------  -----------------  -----------
2           2           2           1           1             1             Caching            Caching    
5           2           2           1           0             0             Mail               Mail       

Note the two ‘0’s in the ZMAILENABLED and ZPUSHENABLED columns mentioned earlier, setting these to 1 means the service will have push and email alerts enabled for it.

Now we know what controls the enabled/disabled checkboxes in the alerts section of server.app it is trivial to modify this directly in the alertsData.db.

Original post below:

In macOS Server it used to be possible to edit the alert settings with something like :

serveradmin settings info:notifications:certificateExpiration:who = john.smith@contoso.com

Unfortunately it appears that it is no longer possible to add an email address to the alert settings via the command line.

This presented an issue for me as I automate the deployment of many macOS servers where we want to have an email address entered in the alert settings so that user receives notifications from their server.

After a little bit of digging the location where this information is now saved was found in

/Library/Server/Alerts/alertData.db

Specifically it is stored in a TABLE called ZADMAILRECIPIENT in a COLUMN called ZADDRESS VARCHAR

So now we know that, all we have to do is work out a way of adding in our desired values to that table.

The quick and dirty of it is something like this:

sqlite /Library/Alerts/alertData.db "INSERT or REPLACE INTO ZADMAILRECIPIENT VALUES('2','4','1','1','john.smith@apple.com','en')"

But in my quest to force myself into using python, I wrote a quick python script that takes the email address as the first argument and then writes this into our db, which makes it easy to put into my deployment workflow.

To use it, simply run it like this:

./script.py

You will need to edit the location of your csv file. Currently this is set in line number 96 of the script.

The script is as below.


#!/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()

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s