Server Script: Runaway Policy Report

[bkerns]

This is something that I made for my own use, but I thought I would post it in case it were useful to others out there.

Essentially, I needed a quick way to know about trouble policies that are executing over and over again - what I termed "Runaway Policies" - without going into the web interface and checking policy by policy. Sometimes this might be due to a misconfigured Smart Group, sometimes a client side issue, or any number of things, but I couldn't find a good way to easily get a rundown on this aspect of the policy runs.

So, I wrote up a Python script that runs on the JSS server that connects to the database, pulls the pertinent information, parses and formats it, and fires off a report to one or multiple email addresses that identifies policies that have executed on a machine more than 5 times in the last 24 hours. I have this scheduled to run each morning so that when I come in I have an idea of what I may have botched the previous day, or clients that have just gone crazy and need looked at.

I'm running it on Python 3.5 and the only requirement beyond standard libraries is the "pymysql" library, which can be added via pip.

RunawayPolicyReport.py :

import pymysql.cursors
from collections import Counter, OrderedDict
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# Mail Message Setup
fromaddr = "jss-report@company.com"
recipients = ['recipient1@company.com', 'recipient2@company.com']
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['To'] = ", ".join(recipients)
msg['Subject'] = "JSS Runaway Policies"

# Database Setup 
connection = pymysql.connect(host='localhost', user='root', password='yourdbpass', db='jamfsoftware', cursorclass=pymysql.cursors.DictCursor)

cursor = connection.cursor()

sql = "SELECT p.name, c.computer_name, count(*) AS count FROM policy_history h JOIN policies p ON h.policy_id = p.policy_id JOIN computers c ON h.computer_id = c.computer_id WHERE h.completed_epoch > ((UNIX_TIMESTAMP() * 1000) - 86400000) GROUP BY p.name, c.computer_name HAVING COUNT(*) > 4;"

# Get data and close connection to DB
cursor.execute(sql)
result = cursor.fetchall()
connection.close()

# Get list of unique policy names and counts
names = []
for item in result:
    names.append(item["name"])

names = Counter(names)
names = OrderedDict(sorted(names.items(), key=lambda t: t[0]))

# Create mail message from data
body=""
body+="The following Policies have executed at least 5 times on a single machine in the last 24 hours:


"

for key, value in names.items():
    body+='"%s" ( %s Computers )

' % (key, value)
    for res in result:
        if res["name"] == key:
            body+='	%-20s%-20i
' % (res["computer_name"], res["count"])
    body+="

"

# Complete message and send
msg.attach(MIMEText(body, 'plain'))
server = smtplib.SMTP('smtp.company.com', 25)
text = msg.as_string()
server.sendmail(fromaddr, recipients, text)
server.quit()

Sample E-Mail Message output:

The following Policies have executed at least 5 times on a single machine in the last 24 hours:


"Sample : Install Product 1" ( 1 Computers )

    1234-COMP-01        6                  


"Sample : Install Product 2" ( 1 Computers )

    1234-COMP-02        10                  


"Sample : Install Product 3" ( 8 Computers )

    1234-COMP-03        11                  
    1234-COMP-04        7                  
    1234-COMP-05        11                  
    1234-COMP-06        8                  
    1234-COMP-07        12                  
    1234-COMP-08        9                  
    1234-COMP-09        12                  
    1234-COMP-10        10                  


"Sample : Install Product 4" ( 2 Computers )

    1234-COMP-11        5                  
    1234-COMP-12        9                  


"Sample : Install Product 5" ( 2 Computers )

    1234-COMP-13        5                  
    1234-COMP-14        5                  


"Sample : Install Product 6" ( 2 Computers )

    1234-COMP-15        5                  
    1234-COMP-16        5

Hope this helps someone out that might need the same kind of information!