Zetta: Email Alerts for Flat-File Log Imports

Zetta-LogLoadEmail.py

"""
Zetta Log Import Emailer

This script will email you whenever a new log is imported into Zetta
Written by Anthony Eden (https://mediarealm.com.au/)
"""

# You need to download this module: https://pypi.python.org/pypi/pypyodbc

# Email config options
EMAIL_TO = ""
EMAIL_FROM = ""
EMAIL_SERVER = ""

# Zetta database ODBC connection name:
SQLConnectionString = 'DSN=ZettaDb;'





import time
import datetime
import pypyodbc

# Email libraries
import smtplib
from email.mime.text import MIMEText

# Setup Zetta SQL DB Connection:
dbConn = pypyodbc.connect(SQLConnectionString)
cur = dbConn.cursor()


def listImportTimes():
    eventsQuery = '''
    SELECT
        DISTINCT InfoCreatedTime,
        Station.StationID,
        UserName,
        CpuDescription,
        xLogger_ActivityLog.TrackerAccountID,
        Station.Name AS StationName
        
    FROM [ZettaDB].[dbo].[xLogger_ActivityLog],
        [ZettaDB].[dbo].[xLogger_TrackerAccount],
        [ZettaDB].[dbo].[Station]
        
        WHERE ActivityLocationID = 103
        AND xLogger_ActivityLog.TrackerAccountID = xLogger_TrackerAccount.TrackerAccountId
        AND xLogger_ActivityLog.StationID = Station.StationID
        AND InfoCreatedTime >= DATEADD(MINUTE, -10, GETUTCDATE())
        ORDER BY InfoCreatedTime DESC
    '''
    
    cur.execute(eventsQuery)
    events = []
    
    for d in cur:

        if d[2] == "":
                UserName = "SYSTEM USER"
        else:
                UserName = d[2]
        
        events.append({
                "InfoCreatedTime": d[0],
                "StationID": d[1],
                "StationName": d[5],
                "UserName": UserName,
                "CpuDescription": d[3],
                "TrackerAccountID": d[4]
        })
    
    return events

def listImportDate(eventTime, accountId):
    assetTypesQuery = '''
    SELECT TOP 1 Changes
        FROM [ZettaDB].[dbo].[xLogger_ActivityLog]
        WHERE ActivityLocationID = 103
        AND InfoCreatedTime = ?
        AND TrackerAccountID = ?
    '''
    
    cur.execute(assetTypesQuery, [eventTime, int(accountId)])

    for d in cur:
        dateString = d[0].split(" Hour ")
        return dateString[0].split("log ")[1]
    

def listImportAssetTypes(eventTime, accountId):
    assetTypesQuery = '''
    SELECT Changes
        FROM [ZettaDB].[dbo].[xLogger_ActivityLog]
        WHERE ActivityLocationID = 103
        AND InfoCreatedTime = ?
        AND TrackerAccountID = ?
    '''
    
    cur.execute(assetTypesQuery, [eventTime, int(accountId)])

    # Store a count of every asset type found in the new log
    assetTypes = {
        "Other/Unknown": 0
    }

    assetTypesMatch = {
        "Song": "Play Song: ",
        "Link": "Play Link: ",
        "Spot": "Play Spot: ",
        "Exact Time Marker": "Exact Time Marker: ",
        "Unresolved Asset": "Unresolved Asset: ",
        "Top of Hour": "Top of Hour: ",
        "Spot Block": "Spot Block: ",
        "Macro": "Macro: ",
        "Empty Voice Track Slot": "Empty Voice Track Slot: ",
        "Comment": "Comment: ",
    }

    for assetType in assetTypesMatch:
        assetTypes[assetType] = 0
    
    for d in cur:
        foundMatch = False
        for assetType in assetTypesMatch:
                if assetTypesMatch[assetType] in d[0]:
                        assetTypes[assetType] += 1
                        foundMatch = True
                        break
        
        if foundMatch is False:
            print d[0]
            assetTypes['Other/Unknown'] += 1
    
    return assetTypes

def sendEmail(to, subject, body):
    msg = MIMEText(body)
    
    msg['Subject'] = subject
    msg['From'] = EMAIL_FROM
    msg['To'] = to
    
    s = smtplib.SMTP(EMAIL_SERVER)
    s.sendmail(EMAIL_FROM, [to], msg.as_string())
    s.quit()

if __name__ == "__main__":

    notifiedImportTimes = []
    
    while True:
        try:
            for event in listImportTimes():
                    if event['InfoCreatedTime'] not in notifiedImportTimes:
                        notifiedImportTimes.append(event['InfoCreatedTime'])
                        spotsOnly = True

                        emailSubject = "Log Imported: " + event['StationName']
                        emailBody = "A log was imported by " + event['UserName']
                        emailBody += " to station " + event['StationName']
                        emailBody += " on computer " + event['CpuDescription'] + "\r\n\r\n"

                        emailBody += "Log Date: " + listImportDate(event['InfoCreatedTime'], event['TrackerAccountID'])  + "\r\n\r\n"

                        emailBody += "Here's a breakdown of what was imported:\r\n"
                        assetBreakdown = listImportAssetTypes(event['InfoCreatedTime'], event['TrackerAccountID'])

                        for assetType in assetBreakdown:
                            if assetType != "Spot" and assetBreakdown[assetType] > 0:
                                spotsOnly = False
                            
                            emailBody += assetType + ": " + str(assetBreakdown[assetType]) + "\r\n"

                        emailBody += "\r\nImport Time: " + str(event['InfoCreatedTime']) + "\r\n"

                        if spotsOnly is True:
                            emailSubject += " (Spots Only)"
                        
                        print emailSubject
                        print emailBody

                        sendEmail(EMAIL_TO, emailSubject, emailBody)
                        
                        #print event
                        print 
            
            time.sleep(20)
            
        except Exception, e:
            print "Exception!!!", e
            time.sleep(120)