dmarcstats

9 files changed, 562 insertions, 0 deletions

diff --git a/dmarcstats.py b/dmarcstats.py
new file mode 100755
index 0000000..67f9fdf
--- /dev/null
+++ b/dmarcstats.py
@@ -0,0 +1,193 @@
+#!/usr/bin/env python3
+import sqlite3
+from os.path import join as pathjoin
+from flask import Flask, g, render_template, request, Markup, url_for
+from datetime import datetime
+from multiprocessing.pool import ThreadPool
+from socket import getfqdn
+import pygal
+
+rg_style = pygal.style.Style(colors=('#00ff00', '#ff0000'))
+
+sv = Flask(__name__)
+sv.config.from_object(__name__)
+
+sv.config.update({
+    'database': pathjoin(sv.root_path, 'dmarc.db'),
+    })
+
+def connect_db():
+    rv = sqlite3.connect(sv.config['database'])
+    c = rv.cursor()
+    assert(c.execute('PRAGMA user_version').fetchone()[0] == 1)
+    rv.row_factory = sqlite3.Row
+    return rv
+
+def get_db():
+    if not hasattr(g, 'sqlite_db'):
+        g.sqlite_db = connect_db()
+    return g.sqlite_db
+
+@sv.teardown_appcontext
+def close_db(error):
+    if hasattr(g, 'sqlite_db'):
+        g.sqlite_db.close()
+
+def format_isotime(value, sep=' ', timespec='auto'):
+    return datetime.utcfromtimestamp(value).isoformat(sep=sep, timespec=timespec)
+sv.jinja_env.filters['isotime'] = format_isotime
+
+rlut = dict()
+
+def bulk_rdns_worker(addr):
+    return addr, getfqdn(addr)
+
+def bulk_rdns(addrs):
+    addrs = [a for a in addrs if a not in rlut.keys()]
+    if not addrs:
+        return
+    pool = ThreadPool(processes=min(len(addrs), 50))
+    for addr, dname in pool.imap(
+            bulk_rdns_worker,
+            addrs,
+            chunksize=1):
+        rlut[addr] = dname
+    pool.close()
+
+def format_ipaddr(value):
+    try:
+        return rlut[value]
+    except KeyError:
+        return value
+sv.jinja_env.filters['fqdn'] = format_ipaddr
+
+def format_tblclass(value):
+    return {
+            'pass': 'success',
+            'policy': 'info',
+            'none': 'notaclass',
+            'neutral': 'info',
+            'softfail': 'warning',
+            'temperror': 'warning',
+            'fail': 'danger',
+            'permerror': 'danger',
+            }[value]
+sv.jinja_env.filters['tblclass'] = format_tblclass
+
+def format_alignment(value):
+    return { 'r': 'Relaxed', 's': 'Strict', None: 'Unknown' }[value]
+sv.jinja_env.filters['alignment'] = format_alignment
+
+def order_header(name, dest, order, current):
+    icon = '△'
+    destorder = order
+    if order == current:
+        icon = '▲'
+        destorder = '{} desc'.format(order)
+    if '{} desc'.format(order) == current:
+        icon = '▼'
+    href = url_for(dest, order=destorder)
+    return '<a href="{}">{}</a> {}'.format(href, name, icon)
+
+def format_pctclass(value, maximum, td=None, tw=None):
+    td = td or maximum / 3
+    tw = tw or maximum / 3 * 2
+    if value < td:
+        return 'danger'
+    elif value < tw:
+        return 'warning'
+    else:
+        return 'success'
+sv.jinja_env.filters['pctclass'] = format_pctclass
+
+@sv.route('/')
+def index():
+    return render_template('index.html')
+
+@sv.route('/feedback')
+def feedback():
+    db = get_db()
+    order = request.args.get('order', 'rm_date_begin').split()
+    if order[0] not in ('rm_org_name', 'rm_date_begin', 'rm_date_end',
+            'nemails'):
+        order = 'rm_date_begin'
+    if len(order) == 2 and order[1] == 'desc':
+        order = ' '.join(order)
+    else:
+        order = order[0]
+    feedback = db.execute('''
+SELECT f.*, SUM(row_count) AS nemails,
+       SUM((row_pol_disposition = 'none') * row_count) AS pass_none,
+       SUM((row_pol_dkim = 'pass' OR row_pol_spf = 'pass') * row_count) AS pass_dmarc,
+       SUM((row_pol_dkim = 'pass') * row_count) AS pass_dkim,
+       SUM((row_pol_spf = 'pass') * row_count) AS pass_spf
+FROM feedback AS f
+JOIN record AS r USING (feedbackid)
+GROUP BY feedbackid
+ORDER BY {}
+'''.format(order)).fetchall()
+    return render_template('feedback/index.html', feedback=feedback,
+            order=order, orderhdr=order_header)
+
+@sv.route('/feedback/<int:feedbackid>')
+def report(feedbackid=None):
+    db = get_db()
+    feedback = db.execute('SELECT * FROM feedback WHERE feedbackid = ?',
+            (feedbackid, )).fetchone()
+    records = db.execute('SELECT * FROM record WHERE feedbackid = ?',
+            (feedbackid, )).fetchall()
+    bulk_rdns([r['row_source_ip'] for r in records])
+    total = dict(
+            records = sum(r['row_count'] for r in records),
+            none = sum(r['row_pol_disposition'] == 'none'
+                and r['row_count'] or 0 for r in records),
+            dmarc = sum((r['row_pol_dkim'] == 'pass' or
+                r['row_pol_spf'] == 'pass')
+                and r['row_count'] or 0 for r in records),
+            dkim = sum(r['row_pol_dkim'] == 'pass'
+                and r['row_count'] or 0 for r in records),
+            spf = sum(r['row_pol_spf'] == 'pass'
+                and r['row_count'] or 0 for r in records))
+    return render_template('feedback/report.html', feedback=feedback,
+        records=records, total=total)
+
+@sv.route('/record/<int:recordid>')
+def record(recordid):
+    db = get_db()
+    record = db.execute('SELECT * FROM record WHERE recordid = ?',
+            (recordid, )).fetchone()
+    bulk_rdns([record['row_source_ip']])
+    reasons = db.execute('SELECT * FROM row_pol_reason WHERE recordid = ?',
+            (recordid, )).fetchall()
+    resdkim = db.execute('SELECT * FROM res_dkim WHERE recordid = ?',
+            (recordid, )).fetchall()
+    resspf = db.execute('SELECT * FROM res_spf WHERE recordid = ?',
+            (recordid, )).fetchall()
+    return render_template('record/index.html', record=record, reasons=reasons,
+            resdkim=resdkim, resspf=resspf)
+
+@sv.route('/feedback/<int:feedbackid>.xml')
+def export(feedbackid):
+    db = get_db()
+    feedback = db.execute('SELECT * FROM feedback WHERE feedbackid = ?',
+            (feedbackid, )).fetchone()
+    records = db.execute('SELECT * FROM record WHERE feedbackid = ?',
+            (feedbackid, )).fetchall()
+    reasons = db.execute('''
+SELECT r.* FROM row_pol_reason AS r
+JOIN record USING (recordid)
+WHERE feedbackid = ?''', (feedbackid, )).fetchall()
+    resdkim = db.execute('''
+SELECT d.* FROM res_dkim AS d
+JOIN record USING (recordid)
+WHERE feedbackid = ?''', (feedbackid, )).fetchall()
+    resspf = db.execute('''
+SELECT s.* FROM res_spf AS s
+JOIN record USING (recordid)
+WHERE feedbackid = ?''', (feedbackid, )).fetchall()
+    return render_template('feedback/export.xml', feedback=feedback,
+            records=records, reasons=reasons, resdkim=resdkim, resspf=resspf)
+
+@sv.route('/stats')
+def stats():
+    return render_template('stats/index.html')
diff --git a/dmarcstats.sh b/dmarcstats.sh
new file mode 100755
index 0000000..5194c65
--- /dev/null
+++ b/dmarcstats.sh
@@ -0,0 +1,3 @@
+#!/bin/sh
+
+FLASK_DEBUG=$1 FLASK_APP=dmarcstats.py python -m flask run
diff --git a/templates/feedback/export.xml b/templates/feedback/export.xml
new file mode 100644
index 0000000..ad556d2
--- /dev/null
+++ b/templates/feedback/export.xml
@@ -0,0 +1,72 @@
+<?xml version="1.0"?>
+<feedback>
+   <report_metadata>
+      <org_name>{{ feedback.rm_org_name }}</org_name>
+      <email>{{ feedback.rm_email }}</email>
+      {% if feedback.rm_extra_contact_info %}
+      <extra_contact_info>{{ feedback.rm_extra_contact_info }}</extra_contact_info>
+      {% endif %}
+      <report_id>{{ feedback.rm_report_id }}</report_id>
+      <date_range>
+         <begin>{{ feedback.rm_date_begin }}</begin>
+         <end>{{ feedback.rm_date_end }}</end>
+      </date_range>
+   </report_metadata>
+   <policy_published>
+      <domain>{{ feedback.pp_domain }}</domain>
+      <adkim>{{ feedback.pp_adkim }}</adkim>
+      <aspf>{{ feedback.pp_aspf }}</aspf>
+      <p>{{ feedback.pp_p }}</p>
+      <sp>{{ feedback.pp_sp }}</sp>
+      <pct>{{ feedback.pp_pct }}</pct>
+   </policy_published>
+   {% for r in records %}
+   <record>
+      <row>
+         <source_ip>{{ r.row_source_ip }}</source_ip>
+         <count>{{ r.row_count }}</count>
+         {% if r.row_pol_disposition and r.row_pol_dkim and r.row_pol_spf %}
+         <policy_evaluated>
+            <disposition>{{ r.row_pol_disposition }}</disposition>
+            <dkim>{{ r.row_pol_dkim }}</dkim>
+            <spf>{{ r.row_pol_spf }}</spf>
+            {% for rs in reasons if rs.recordid == r.recordid %}
+            <reason>
+               <type>{{ rs.type }}</type>
+               {% if rs.comment %}
+               <comment>{{ rs.comment }}</comment>
+               {% endif %}
+            </reason>
+            {% endfor %}
+         </policy_evaluated>
+         {% endif %}
+      </row>
+      <identifiers>
+         {% if r.ids_envelope_to %}
+         <envelope_to>{{ r.ids_envelope_to }}</envelope_to>
+         {% endif %}
+         <header_from>{{ r.ids_header_from }}</header_from>
+      </identifiers>
+      <auth_results>
+         {% for d in resdkim if d.recordid == r.recordid %}
+         <dkim>
+            <domain>{{ d.domain }}</domain>
+            {% if d.selector %}
+            <selector>{{ d.selector }}</selector>
+            {% endif %}
+            <result>{{ d.result }}</result>
+            {% if d.human_result %}
+            <human_result>{{ d.human_result }}</human_result>
+            {% endif %}
+         </dkim>
+         {% endfor %}
+         {% for s in resspf if s.recordid == r.recordid %}
+         <spf>
+            <domain>{{ s.domain }}</domain>
+            <result>{{ s.result }}</result>
+         </spf>
+         {% endfor %}
+      </auth_results>
+   </record>
+   {% endfor %}
+</feedback>
diff --git a/templates/feedback/index.html b/templates/feedback/index.html
new file mode 100644
index 0000000..617c758
--- /dev/null
+++ b/templates/feedback/index.html
@@ -0,0 +1,49 @@
+{% extends "layout.html" %}
+{% block title %}Feedback{% endblock %}
+{% block content %}
+<ol class="breadcrumb">
+  <li><a href="{{ url_for('index') }}">Index</a></li>
+  <li class="active">Feedback</li>
+</ol>
+<h1>Feedback</h1>
+<table class="table table-condensed">
+  <tr>
+    <th>{{ orderhdr('Organisation Name', 'feedback', 'rm_org_name', order)|safe }}</th>
+    <th>{{ orderhdr('Date Begin', 'feedback', 'rm_date_begin', order)|safe }}</th>
+    <th>{{ orderhdr('Date End', 'feedback', 'rm_date_end', order)|safe }}</th>
+    <th>{{ orderhdr('Messages', 'feedback', 'nemails', order)|safe }}</th>
+    <th>None</th>
+    <th>Pass</th>
+    <th>DKIM</th>
+    <th>SPF</th>
+    <th></th>
+  </tr>
+  {% for item in feedback %}
+  <a href="{{ url_for('report', feedbackid=item.feedbackid) }}">
+  <tr>
+    <td>{{ item.rm_org_name }}</td>
+    <td><time>{{ item.rm_date_begin|isotime }}</time></td>
+    <td><time>{{ item.rm_date_end|isotime }}</time></td>
+    <td>{{ item.nemails }}</td>
+    <td class="{{ item.pass_none|pctclass(item.nemails) }}">
+      {{ (item.pass_none / item.nemails * 100)|round|int }}%
+    </td>
+    <td class="{{ item.pass_dmarc|pctclass(item.nemails) }}">
+      {{ (item.pass_dmarc / item.nemails * 100)|round|int }}%
+    </td>
+    <td class="{{ item.pass_dkim|pctclass(item.nemails) }}">
+      {{ (item.pass_dkim / item.nemails * 100)|round|int }}%
+    </td>
+    <td class="{{ item.pass_spf|pctclass(item.nemails) }}">
+      {{ (item.pass_spf / item.nemails * 100)|round|int }}%
+    </td>
+    <td>
+      <a class="btn btn-xs" href="{{ url_for('report', feedbackid=item.feedbackid) }}">
+        Report
+      </a>
+    </td>
+  </tr>
+  </a>
+  {% endfor %}
+</table>
+{% endblock %}
diff --git a/templates/feedback/report.html b/templates/feedback/report.html
new file mode 100644
index 0000000..141c823
--- /dev/null
+++ b/templates/feedback/report.html
@@ -0,0 +1,113 @@
+{% extends "layout.html" %}
+{% block title %}Report{% endblock %}
+{% block content %}
+<ol class="breadcrumb">
+  <li><a href="{{ url_for('index') }}">Index</a></li>
+  <li><a href="{{ url_for('feedback') }}">Feedback</a></li>
+  <li class="active">Report</li>
+</ol>
+<h1>Report</h1>
+<div class="row">
+  <div class="col-md-7">
+    <h2>Report Metadata</h1>
+    <dl class="dl-horizontal">
+      <dt>Organisation Name</dt><dd>{{ feedback.rm_org_name }}</dd>
+      <dt>Email</dt><dd>{{ feedback.rm_email }}</dd>
+      <dt>Extra Contact Info</dt><dd>{{ feedback.rm_extra_contact_info }}</dd>
+      <dt>Report ID</dt><dd>{{ feedback.rm_report_id }}</dd>
+      <dt>Date Range</dt><dd>
+      <time>{{ feedback.rm_date_begin|isotime }}</time>
+      to
+      <time>{{ feedback.rm_date_end|isotime }}</time>
+      </dd>
+    </dl>
+  </div>
+  <div class="col-md-5">
+    <h2>Policy Published</h1>
+    <dl class="dl-horizontal">
+      <dt>Domain</dt><dd>{{ feedback.pp_domain }}</dd>
+      <dt>DKIM Alignment</dt><dd>{{ feedback.pp_adkim|alignment }}</dd>
+      <dt>SPF Alignment</dt><dd>{{ feedback.pp_aspf|alignment }}</dd>
+      <dt>Policy</dt><dd>{{ feedback.pp_p|capitalize }}</dd>
+      <dt>Subdomain Policy</dt><dd>{{ feedback.pp_sp|capitalize }}</dd>
+    </dl>
+  </div>
+  <div class="col-md-6">
+    <h2>Statistics</h2>
+    <table class="table">
+      <tr>
+        <th></th>
+        <th>Total</th>
+        <th>%</th>
+      </tr>
+      <tr>
+        <td>Messages</td>
+        <td>{{ total.records }}</td>
+        <td></td>
+      </tr>
+      <tr class="{{ total.none|pctclass(total.records) }}">
+        <td>Disposition None</td>
+        <td>{{ total.none }}</td>
+        <td>{{ (total.none / total.records * 100)|round|int }}%</td>
+      </tr>
+      <tr class="{{ total.dmarc|pctclass(total.records) }}">
+        <td>DKIM or SPF Pass</td>
+        <td>{{ total.dmarc }}</td>
+        <td>{{ (total.dmarc / total.records * 100)|round|int }}%</td>
+      </tr>
+      <tr class="{{ total.dkim|pctclass(total.records) }}">
+        <td>DKIM Pass</td>
+        <td>{{ total.dkim }}</td>
+        <td>{{ (total.dkim / total.records * 100)|round|int }}%</td>
+      </tr>
+      <tr class="{{ total.spf|pctclass(total.records) }}">
+        <td>SPF Pass</td>
+        <td>{{ total.spf }}</td>
+        <td>{{ (total.spf / total.records * 100)|round|int }}%</td>
+      </tr>
+    </table>
+  </div>
+  <div class="col-md-6">
+    <h2>Utilities</h2>
+    <ul>
+      <li>XML Export:
+        <a class="btn btn-default" href="{{ url_for('export', feedbackid=feedback.feedbackid) }}">View</a>
+        <a class="btn btn-default" href="{{ url_for('export', feedbackid=feedback.feedbackid) }}" download="{{ feedback.rm_report_id }}">Download</a>
+      </li>
+    </ul>
+  </div>
+</div>
+<div class="row">
+  <div class="col-md-12">
+    <h2>Records</h2>
+    <table class="table table-condensed">
+      <tr>
+        <th>Source</th>
+        <th>Count</th>
+        <th>Disposition</th>
+        <th>DKIM</th>
+        <th>SPF</th>
+        <th>Envelope To</th>
+        <th>Header From</th>
+        <th></th>
+      </tr>
+      {% for item in records %}
+      <tr>
+        <td>{{ item['row_source_ip']|fqdn }}</td>
+        <td>{{ item['row_count'] }}</td>
+        <td>{{ item['row_pol_disposition'] }}</td>
+        <td class="{{ item['row_pol_dkim']|tblclass }}">{{ item['row_pol_dkim'] }}</td>
+        <td class="{{ item['row_pol_spf']|tblclass }}">{{ item['row_pol_spf'] }}</td>
+        <td>{{ item['ids_envelope_to'] }}</td>
+        <td>{{ item['ids_header_from'] }}</td>
+        <td>
+          <a class="btn btn-xs" href="{{ url_for('record', recordid=item['recordid']) }}">
+            Record
+          </a>
+        </td>
+      </tr>
+      {% endfor %}
+    </table>
+    {% endblock %}
+  </div>
+</div>
diff --git a/templates/index.html b/templates/index.html
new file mode 100644
index 0000000..1e0364f
--- /dev/null
+++ b/templates/index.html
@@ -0,0 +1,11 @@
+{% extends "layout.html" %}
+{% block title %}Index{% endblock %}
+{% block content %}
+<ol class="breadcrumb">
+  <li class="active">Index</li>
+</ol>
+<ul class="nav nav-pills nav-stacked">
+  <li role="presentation"><a href="{{ url_for('feedback') }}">Feedback list</a></li>
+  <li role="presentation"><a href="{{ url_for('stats') }}">Statistics</a></li>
+</ul>
+{% endblock %}
diff --git a/templates/layout.html b/templates/layout.html
new file mode 100644
index 0000000..d7ca750
--- /dev/null
+++ b/templates/layout.html
@@ -0,0 +1,15 @@
+<!DOCTYPE html>
+<html lang="en">
+  <head>
+    {% block head %}
+      <!--<link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}" />-->
+  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
+    <title>{% block title %}{% endblock %} - DMARC Statistics</title>
+    <meta name="viewport" content="width=device-width, initial-scale=1">
+    {% endblock %}
+  </head>
+  <body>
+    <!--<main>% block content %}% endblock %}</main>-->
+    <div class="container">{% block content %}{% endblock %}</div>
+  </body>
+</html>
diff --git a/templates/record/index.html b/templates/record/index.html
new file mode 100644
index 0000000..ace1995
--- /dev/null
+++ b/templates/record/index.html
@@ -0,0 +1,96 @@
+{% extends "layout.html" %}
+{% block title %}Record{% endblock %}
+{% block content %}
+<ol class="breadcrumb">
+  <li><a href="{{ url_for('index') }}">Index</a></li>
+  <li><a href="{{ url_for('feedback') }}">Feedback</a></li>
+  <li><a href="{{ url_for('report', feedbackid=record.feedbackid) }}">Report</a></li>
+  <li class="active">Record</li>
+</ol>
+<h1>Record</h2>
+<div class="row">
+  <div class="col-md-6">
+    <h2>Record Details</h2>
+    <dl class="dl-horizontal">
+      <dt>Source IP</dt><dd>{{ record.row_source_ip|fqdn }}</dd>
+      <dt>Count</dt><dd>{{ record.row_count }}</dd>
+    </dl>
+    <h3>Policy Evaluated</h3>
+    <dl class="dl-horizontal">
+      <dt>Disposition</dt><dd>{{ record.row_pol_disposition }}</dd>
+      <dt>DKIM</dt><dd>
+      <span class="label label-{{ record.row_pol_dkim|tblclass }}">
+        {{ record.row_pol_dkim }}
+      </span>
+      </dd>
+      <dt>SPF</dt><dd>
+      <span class="label label-{{ record.row_pol_spf|tblclass }}">
+        {{ record.row_pol_spf }}
+      </span>
+      </dd>
+    </dl>
+    {% if reasons %}
+    <h4>Reasons</h4>
+    <table class="table">
+      <tr>
+        <th>Type</th>
+        <th>Comment</th>
+      </tr>
+      {% for item in reasons %}
+      <tr>
+        <td>{{ item['type'] }}</td>
+        <td>{{ item['comment'] }}</td>
+      </tr>
+      {% endfor %}
+    </table>
+    {% endif %}
+  </div>
+  <div class="col-md-4">
+    <h2>Identifiers</h2>
+    <dl class="dl-horizontal">
+      <dt>Envelope To</dt><dd>{{ record.ids_envelope_to }}</dd>
+      <dt>Header From</dt><dd>{{ record.ids_header_from }}</dd>
+    </dl>
+  </div>
+</div>
+
+<div class="row">
+  {% if resdkim %}
+  <div class="col-md-8">
+    <h2>DKIM Results</h2>
+    <table class="table">
+      <tr>
+        <th>Domain</th>
+        <th>Selector</th>
+        <th>Result</th>
+        <th>Human Result</th>
+      </tr>
+      {% for item in resdkim %}
+      <tr>
+        <td>{{ item['domain'] }}</td>
+        <td>{{ item['selector'] }}</td>
+        <td class="{{ item['result']|tblclass }}">{{ item['result'] }}</td>
+        <td>{{ item['human_result'] }}</td>
+      </tr>
+      {% endfor %}
+    </table>
+  </div>
+  {% endif %}
+
+  <div class="col-md-4">
+    <h2>SPF Results</h2>
+    <table class="table">
+      <tr>
+        <th>Domain</th>
+        <th>Result</th>
+      </tr>
+      {% for item in resspf %}
+      <tr>
+        <td>{{ item['domain'] }}</td>
+        <td class="{{ item['result']|tblclass }}">{{ item['result'] }}</td>
+      </tr>
+      {% endfor %}
+    </table>
+    {% endblock %}
+  </div>
+</div>
diff --git a/templates/stats/index.html b/templates/stats/index.html
new file mode 100644
index 0000000..aab0f4e
--- /dev/null
+++ b/templates/stats/index.html
@@ -0,0 +1,10 @@
+{% extends "layout.html" %}
+{% block title %}Statistics{% endblock %}
+{% block content %}
+<ol class="breadcrumb">
+  <li><a href="{{ url_for('index') }}">Index</a></li>
+  <li class="active">Statistics</li>
+</ol>
+<h1>Statistics</h1>
+<p>Nothing here yet</p>
+{% endblock %}