2

I receive Google's DMARC reports daily as a zip file containing an XML file. I manually download each zip file ~/Documents/DMARC.

I want to aggregate the information from all the saved DMARC reports and produce a human-readable readable report (e.g. table in LibreOffice Calc spreadsheet). I would rather not have memory-hungry or CPU-hungry services running all the time.

Any suggestions on how to do this?

Background Info

Domain-based Message Authentication, Reporting and Conformance (DMARC) is an evolving technical standard and the DMARC report format is specified by that standard. It also looks like Ubuntu natively support DMARC, but I need detailed instructions.

The Google link above shows samples of input DMARC XML file format and an output tabular form. As requested, they are pasted here.

Sample input XML file:

<?xml version="1.0" encoding="UTF-8" ?>
<feedback>
  <report_metadata>
    <org_name>solarmora.com</org_name>
    <email>[email protected]</email>
   <extra_contact_info>http://solarmora.com/dmarc/support</extra_contact_info>
    <report_id>9391651994964116463</report_id>
    <date_range>
      <begin>1335571200</begin>
      <end>1335657599</end>
    </date_range>
  </report_metadata>
  <policy_published>
    <domain>bix-business.com</domain>
    <adkim>r</adkim>
    <aspf>r</aspf>
    <p>none</p>
    <sp>none</sp>
    <pct>100</pct>
  </policy_published>
  <record>
    <row>
      <source_ip>203.0.113.209</source_ip>
      <count>2</count>
      <policy_evaluated>
        <disposition>none</disposition>
        <dkim>fail</dkim>
        <spf>pass</spf>
      </policy_evaluated>
    </row>
    <identifiers>
      <header_from>bix-business.com</header_from>
    </identifiers>
    <auth_results>
      <dkim>
        <domain>bix-business.com</domain>
        <result>fail</result>
        <human_result></human_result>
      </dkim>
      <spf>
        <domain>bix-business.com</domain>
        <result>pass</result>
      </spf>
    </auth_results>
  </record>
</feedback>

Sample output table: Sample output table:

4
  • @Jaydin did you ever find a simple solution for collating XML DMARC reports into a spreadsheet? There seem to be many large scale processing solutions (including this open source option written in python: domainaware.github.io/parsedmarc/index.html) but they are excessive for a lot of situations.
    – SuprMan
    Feb 3 at 6:15
  • @SuprMan, yes, I got it working for my use-case two years ago. My solution had many steps for the initial setup and no one else seemed to be interested in this question, so I didn't bother posting the answer. Let me know if you're interested and I see if I can find my notes and post it.
    – Jaydin
    Feb 4 at 3:51
  • 1
    It's true that this question hasn't gotten many views but I would certainly be interested in your solution. I suspect the question may gain interest because large email service providers seem to be cracking down on messages that lack DAMRC recently.
    – SuprMan
    Feb 5 at 18:57
  • @SuprMan I added my answer below. Please have a look and let me know if it works for you..
    – Jaydin
    Feb 19 at 4:04

2 Answers 2

2

These are the steps I used with Ubuntu 22.04 for my use-case. I expect they can be improved upon for more general use, so please comment and let me know, and I will do my best to adapt the solution.

This solution will use the dmarcts-report-parser package and mysql server from the Ubuntu default repository. The final output is a csv file, so this solution is probably best suited for light users (e.g. one or two domains) and occasional users of DMARC aggregation reports.

Here is a sample of the output CSV file in LibreOffice Calc (around half the columns displayed): output csv file


Preparations

  1. Download DMARC aggregation reports: Download the DMARC aggregation report attachments from the emails. These files usually end with .xml.gz or .zip and there is no need to extract them as dmarcts-report-parser will do that for you.

    • Note: The dmarcts-report-parser could be configured to download the DMARC reports from an IMAP server. I did not use that functionality due to security concerns.
    • Hint: In addition to manually downloading DMARC report attachments, it is also possible to automate this using Google Apps Script (I use this), Microsoft Power Automate, and various email client add-ins.
  2. Create working folders: For the purpose of this exercise, I will create a folder called dmarcprocess to contain the config files and the final csv file output, and a subfolder called Reports to store the downloaded DMARC aggregation reports from step 1.

    • Open a terminal
    • mkdir ~/Documents/dmarcprocess
    • cd ~/Documents/dmarcprocess
    • mkdir Reports
    • Copy all the dmarc reports into the Reports folder

Setup MySQL Server

  1. Install MySQL: This step assumes that mysql server is not already installed.

    • sudo apt update
    • sudo apt install mysql-server
    • Check msql server is active: sudo service mysql status (use Ctrl-C to exit). The response should say Active: active (running). If not active, try sudo systemctl start mysql.
    • Recommended: sudo systemctl disable mysql to stop mysql from auto starting at PC boot and using system resources. You can use sudo systemctl start mysql to manually start and sudo systemctl stop mysql to manually stop the mysql server.
    • Note: mysql server is installed with default settings, which aren't very secure (e.g. no password for root user). You may want to change these setting later if you intend to auto start mysql server at boot.
  2. Create database and user: We will create the default database and default user for dmarcts-report-parser. These can be customised later.

    • sudo mysql -u root -p to log into the mysql server and then press enter at "Enter password:" prompt, as there is no root password. The below commands need to be entered at the mysql> prompt. I have also included how the server should respond to each command.
    • CREATE DATABASE dmarc; to create a new database called dmarc. Server response: Query OK, 1 row affected.
    • USE dmarc; to connect to the newly created database. Server response: Database changed.
    • CREATE USER 'dmarc'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; to create a new user. Server response: Query OK.
    • GRANT ALL PRIVILEGES ON dmarc.* TO 'dmarc'@'localhost'; to give the new user full access to the new database. Server response: Query OK.
    • FLUSH PRIVILEGES; to reload the permissions. Server response: Query OK.
    • quit; back to the terminal.

Setup dmarcts-report-parser

  1. Install dmarcts-report-parser

    • sudo apt install dmarcts-report-parser
  2. Configure dmarcts-report-parser: Copy the sample config file into the working folder and then edit it. Make sure you are in the ~/Documents/dmarcprocess/ folder for the below commands.

    • cp /usr/share/doc/dmarcts-report-parser/examples/dmarcts-report-parser.conf.sample ./dmarcts-report-parser.conf copies and renames the sample config file.
    • sed -i "s/'dbhost'/'localhost'/" ./dmarcts-report-parser.conf changes the name of the mysql server to localhost.

Setup tables and export view

  1. First run of dmarcts-report-parser: The first run of the dmarcts-report-parser will create the database tables and then populate the database using the dmarc reports previously downloaded.

    • dmarcts-report-parser -z ./Reports/*
    • The system should respond with Adding missing table <report> to the database. and Adding missing table <rptrecord> to the database. This means that the two database tables that dmarcts-report-parser needs were created.
    • Note: -z is for reading zip files according to the project page. I found that this option works for both .zip and .xml.gz files. Strangely, this option is not currently documented in the Ubuntu man page. Other potential file types are mbox (-m), MIME (-e) and xml (-x).
  2. Create a database View: A database view simplifies the export of data.

    • sudo mysql -u root -p to log back into the mysql server and then press enter at "Enter password:" prompt, as there is no root password. The below commands need to be entered at the mysql> prompt. I have also included how the server should respond to each command.
    • USE dmarc; to connect to the newly created database. Server response: Database changed.
    • CREATE VIEW exportview AS SELECT * FROM rptrecord INNER JOIN report USING (serial); to create a new View called exportview from the two tables that the dmarcts-report-parser populated.
    • quit; back to the terminal.

Export data and subsequent usage

  1. Export data: The below command will export dmarc data using into the file dmarc-report.csv. It assumes you are in the ~/Documents/dmarcprocess/ folder.

    • mysql --user='dmarc' --password='password' --batch --execute='SELECT * FROM dmarc.exportview;' | sed 's/\xml.*/xml/;s/\t/","/g;s/^/"/;s/$/"/' > ./dmarc-report.csv.
    • Mysql will complain with mysql: [Warning] Using a password on the command line interface can be insecure. but that warning will not affect the outcome of the above command.
    • The new file dmarc-report.csv is a CSV file that can be opened and analysed with a spreadsheet app (e.g. LibreOffice Calc).
    • This command uses the MySQL Command-Line Client to connect to the database, export the view it tsv format. Then sed removes the xml output and then converts the reminder to csv format.
      • --execute= execute the following statement and returns the result.
      • --batch print results using tab as the column separator. This option could be excluded as --execute seem to do the same thing by default.
      • sed 's/\xml.*/xml/; removes all text after "xml". This is needed because one of the database columns (raw_xml) contains the raw xml data which makes it difficult to convert into CSV format. Please let me know if anyone has a better method.
      • s/\t/","/g; replaces all tabs with a ,.
      • s/^/"/; inserts a " at the begging of each line.
      • s/$/"/' inserts a " at the end of each line.
  2. Subsequent use

    • I don't need mysql to run all the time, so I use sudo systemctl disable mysql to stop mysql from auto starting at reboot.
    • I use a bash batch file to automate parsing and import of new reports and the export the results.
      • sudo systemctl start mysql to start the mysql server
      • dmarcts-report-parser -z ./Reports/* to parse the reports and store them in the database
      • mysql --user='dmarc' --password='password' --batch --execute='SELECT * FROM dmarc.exportview;' | sed 's/\xml.*/xml/;s/\t/","/g;s/^/"/;s/$/"/' > ./dmarc-report.csv to export file.
      • sudo systemctl stop mysql to stop the mysql server after the export is finished.
    • At the moment I export all contents each time, but at some point I will probably modify the export statement to only export out the data I need while fine-tuning the dmarc, dkim and spf settings.
    • The Ubuntu man page implies that dmarcts-report-viewer will be supported in the future by the default repository. I intend to update this answer once that happens.

PLEASE HELP IMPROVE THIS ANSWER by proving feedback in the comments.

2
  • Since you wanted "feedback in the comments", the first thing I noticed is that installing MySQL is the opposite of "I would rather not have memory-hungry or CPU-hungry services running all the time". If possible, that app should be mode to use SQLite
    – Daniel T
    Feb 19 at 4:10
  • @DanielT thanks for the feedback. You make a good point and I added another dot point under 'Subsequent use' to show how to stop and start the server. Unfortunately, the dmarcts-report-parser project page only states MySQL and PostgreSQL as being supported.
    – Jaydin
    Feb 19 at 4:21
0

Here is a more lightweight solution:

#!/usr/bin/env python3
import xml.etree.ElementTree as ET
import os.path

addresses = {}

dirname = os.path.expanduser("~/Documents/DMARC")
for basename in os.listdir(dirname):
    if not basename.endswith(".xml"):
        continue
    tree = ET.parse(os.path.join(dirname, basename))
    row = tree.find("record/row")
    ip = row.find("source_ip").text
    current = addresses.setdefault(ip, { "count": 0, "dmarc_pass": 0, "dmarc_fail": 0, "dkim_pass": 0, "dkim_fail": 0, "spf_pass": 0, "spf_fail": 0 })

    current["count"] += int(row.find("count").text)
    policy = row.find("policy_evaluated")
    dkim_pass = policy.find("dkim").text == "pass"
    spf_pass = policy.find("spf").text == "pass"
    current["dkim_pass" if dkim_pass else "dkim_fail"] += 1
    current["spf_pass" if spf_pass else "spf_fail"] += 1
    current["dmarc_pass" if dkim_pass and spf_pass else "dmarc_fail"] += 1

print("ip_address,email_volume,dmarc_pass,dmarc_fail,dmarc_rate,spf_pass,spf_fail,dkim_pass,dkim_fail")
for ip, current in addresses.items():
    dmarc_pass = current["dmarc_pass"]
    dmarc_fail = current["dmarc_fail"]
    print(f'{ip},{current["count"]},{dmarc_pass},{dmarc_fail},{dmarc_pass/dmarc_fail}%,{current["spf_pass"]},{current["spf_fail"]},{current["dkim_pass"]},{current["dkim_fail"]}')

Save it to dmarc.py , chmod +x dmarc.py . Run it with ./dmarc.py > dmarc.csv . I only guessed how the DMARC XML file format is supposed to work, so this may need adjusting.

Sample output

ip_address,email_volume,dmarc_pass,dmarc_fail,dmarc_rate,spf_pass,spf_fail,dkim_pass,dkim_fail
203.0.113.209,2,0,1,0.0%,1,0,0,1

sample output csv libreoffice

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .