A script for processing data from Ingress attack notification emails.

IngressAlertParser.md

Requirements

• phpQuery
• Medoo

NB: db.php is just a wrapper for Medoo that reads $dbPath and returns a corresponding Medoo instance.

Database schema

CREATE TABLE attacks (
  id integer primary key,
  time integer not null,
  email text not null,
  reporter text,
  name text,
  lat real,
  long real,
  address text,
  attacker string,
  hour integer,
  min integer,
  level integer,
  health integer,
  owner text,
  link integer,
  foreign key(link) references attacks(id));

Setup with Mailgun

This script accepts POST requests with a Mailgun-style JSON body.

Once you have a sandbox or verified domain, create a route that maps an email address of your choice to the URL of the script.

Notes/caveats on parsing

This should work with the current email format (as of at least 2014). Some email clients will completely munge the HTML body though.

Good clients: Gmail, Outlook 365, Thunderbird
Bad clients: K-9 Mail

Historical submissions may be possible, depending on the client forwarding the email -- we look for lines of the form Date: <date> or On <date>, .... Otherwise, the date of the forwarded email is used.

Linked portals are also parsed, and store a reference back to the parent portal.

IngressAlertParser.php

<?php
require_once getenv("PHPLIB") . "phpquery.php";
$dbFile = "rice.db";
$db = require_once getenv("PHPLIB") . "db.php";
// process email
$meta = array("email" => str_replace("<", "", str_replace(">", "", $_POST["Message-Id"])), "time" => null);
$match = array();
if (preg_match("/^(?:Date|Sent): (.*)$/im", $_POST["body-plain"], $match) || preg_match("/^On (.*),/im", $_POST["body-plain"], $match)) {
    $td = date_parse($match[1]);
    $meta["time"] = mktime($td["hour"], $td["minute"], $td["second"], $td["month"], $td["day"], $td["year"]);
}
if (!$meta["time"]) $meta["time"] = strtotime($_POST["Date"]);
$entries = array();
$match = array();
if (array_key_exists("Subject", $_POST)) {
    preg_match("/Entities attacked by (\S+)/i", $_POST["Subject"], $match);
}
// parse methods
function clean($s) {
    return trim(preg_replace("/\s+/", " ", $s));
}
function parsePortal($td) {
 /* <td>
      <div>$name</div>
      <div><a href="...ll=$lat,$long...">$addr</a></div>
    </td> */
    $a = pq("a", $td)->eq(0);
    $name = clean(pq("div", $td)->eq(0)->text());
    $addr = clean($a->text());
    $match = array();
    preg_match("/ll=(\-?[0-9\.]+),(\-?[0-9\.]+)/i", $a->attr("href"), $match);
    return array("name" => $name, "address" => $addr, "lat" => floatval($match[1]), "long" => floatval($match[2]), "links" => array());
}
function parseLinkPortal($td) {
 /* <td>
      <table border="0" cellpadding="0" cellspacing="0" width="700px">
        <tbody>
          <tr>
            <td style="line-height:0" width="50px"><img src="..." height="26" width="50"></td>
            <td>$name: <a href="...ll=$lat,$long..." style="color:#d73b8e;border:none;text-decoration:none" target="_blank">$addr</a></td>
          </tr>
        </tbody>
      </table>
    </td> */
    $td2 = pq("table > tbody > tr > td", $td)->eq(1);
    $a = pq("a", $td2)->eq(0);
    $name = clean(explode(":", $td2->text())[0]);
    $addr = clean($a->text());
    $match = array();
    preg_match("/ll=(\-?[0-9\.]+),(\-?[0-9\.]+)/i", $a->attr("href"), $match);
    return array("name" => $name, "address" => $addr, "lat" => floatval($match[1]), "long" => floatval($match[2]));
}
function isLinkTitle($td) {
 /* <td>
      <table border="0" cellpadding="0" cellspacing="0" width="700px">
        <tbody>
          <tr>
            <td style="line-height:0" width="50px"><img src="..." height="26" width="50"></td>
            <td>LINK DESTROYED</td>
          </tr>
        </tbody>
      </table>
    </td> */
    return preg_match("/LINKS? DESTROYED/i", pq("table > tbody > tr > td", $td)->eq(1)->text());
}
function isDamage($td) {
 /* <td style="padding:1em 0">
      <table border="0" cellpadding="0" cellspacing="0" width="700px">
        <tbody>
          <tr>
            <td width="400px">
              <div>DAMAGE:<br>
                1 Link destroyed by <span style="color:#428f43">$attacker</span> at $hour:$min hrs GMT<span class=""><br>
                8 Resonators remaining on this Portal.</span></div>
            </td>
            <td>
              <div>STATUS:<br>
                Level $level<br>
                Health: $health%<br>
                Owner: <span style="color:#3679b9">$owner</span></div>
            </td>
          </tr>
        </tbody>
      </table>
    </td> */
    return preg_match("/DAMAGE:/i", pq("table > tbody > tr > td > div", $td)->eq(0)->text());
}
function parseDamage($td) {
    $divs = pq("table > tbody > tr > td > div", $td);
    $match1 = array();
    preg_match("/by (\S+) at ([0-9]+):([0-9]+) hrs/i", $divs->eq(0)->text(), $match1);
    $match2 = array();
    preg_match("/Level ([0-8]).*?Health: ([0-9]+)%.*?Owner: (\S+)/is", $divs->eq(1)->text(), $match2);
    return array("attacker" => $match1[1], "hour" => intval($match1[2]), "min" => intval($match1[3]),
                 "level" => intval($match2[1]), "health" => intval($match2[2]), "owner" => $match2[3]);
}
// start parsing
phpQuery::newDocument($_POST["body-html"]);
$pos = 0;
$links = false;
for ($tds = pq("div > table > tbody > tr:nth-child(2) > td > table > tbody > tr > td"), $i = 0; $i < count($tds); $i++) {
    $td = $tds->eq($i);
    if ($i === 0) {
        $meta["reporter"] = $td->children("span")->eq(1)->text();
        $i++; // skip heading row
    } elseif (isLinkTitle($td)) {
        $links = true;
    } elseif (isDamage($td)) {
        $entries[$pos] = array_merge($entries[$pos], parseDamage($td));
        $links = false;
        $i++; // skip empty row
    } elseif ($links) {
        $entries[$pos]["links"][] = parseLinkPortal($td);
        $i++; // skip image row
    } else {
        $pos = count($entries);
        $entries[$pos] = parsePortal($td);
        $i++; // skip image row
    }
}
// write back to db
foreach ($entries as $entry) {
    $data = array_merge($meta, array_diff_key($entry, array("links" => null)));
    $id = $db->insert("attacks", $data);
    foreach ($entry["links"] as $link) {
        $db->insert("attacks", array_merge($data, $link, array("link" => $id)));
    }
}