From 4501f68fa04a34934ff3c3ec8e9ddf43e060c5e5 Mon Sep 17 00:00:00 2001 From: Neil Smith Date: Wed, 30 Mar 2016 22:39:20 +0100 Subject: [PATCH] Tidied, created stand-alone script --- complete.ipynb | 935 ++++++++++++------------------------------- dmarc_to_database.py | 202 ++++++++++ make_database.ipynb | 28 +- queries.ipynb | 456 +++++++++++++++++++++ 4 files changed, 928 insertions(+), 693 deletions(-) create mode 100644 dmarc_to_database.py create mode 100644 queries.ipynb diff --git a/complete.ipynb b/complete.ipynb index 25659d3..bd272eb 100644 --- a/complete.ipynb +++ b/complete.ipynb @@ -21,19 +21,19 @@ }, { "cell_type": "code", - "execution_count": 11, + "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "def fetch_msg(num):\n", - " return mailbox.fetch(num, '(RFC822)')[1][0][1]" + " return mailbox.uid('FETCH', num, '(RFC822)')[1][0][1]" ] }, { "cell_type": "code", - "execution_count": 36, + "execution_count": 3, "metadata": { "collapsed": true }, @@ -48,7 +48,7 @@ }, { "cell_type": "code", - "execution_count": 42, + "execution_count": 4, "metadata": { "collapsed": true }, @@ -67,7 +67,7 @@ }, { "cell_type": "code", - "execution_count": 35, + "execution_count": 5, "metadata": { "collapsed": true }, @@ -80,669 +80,22 @@ }, { "cell_type": "code", - "execution_count": 38, - "metadata": { - "collapsed": false - }, - "outputs": [ - { - "data": { - "text/plain": [ - "['dmarc.ini']" - ] - }, - "execution_count": 38, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "config = configparser.ConfigParser()\n", - "config.read('dmarc.ini')" - ] - }, - { - "cell_type": "code", - "execution_count": 39, - "metadata": { - "collapsed": false - }, - "outputs": [ - { - "data": { - "text/plain": [ - "('OK', [b'174'])" - ] - }, - "execution_count": 39, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "mailbox = imaplib.IMAP4(host=config['imap']['server'], \n", - " port=config['imap']['port'])\n", - "mailbox.starttls()\n", - "mailbox.login(config['imap']['username'], config['imap']['password'])\n", - "mailbox.select('INBOX', readonly=True)" - ] - }, - { - "cell_type": "code", - "execution_count": 40, - "metadata": { - "collapsed": false - }, - "outputs": [ - { - "data": { - "text/plain": [ - "('OK',\n", - " [b'1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174'])" - ] - }, - "execution_count": 40, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "resp, nums = mailbox.search(None, \"ALL\")\n", - "resp, nums" - ] - }, - { - "cell_type": "code", - "execution_count": 44, - "metadata": { - "collapsed": false - }, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "b'1'\n", - "b'1' []\n", - "b'2'\n", - "b'2' []\n", - "b'3'\n", - "b'3' []\n", - "b'4'\n", - "b'4' []\n", - "b'5'\n", - "b'5' []\n", - "b'6'\n", - "b'6' []\n", - "b'7'\n", - "b'7' []\n", - "b'8'\n", - "b'8' []\n", - "b'9'\n", - "b'9' []\n", - "b'10'\n", - "b'10' []\n", - "b'11'\n", - "b'11' []\n", - "b'12'\n", - "b'12' []\n", - "b'13'\n", - "b'13' []\n", - "b'14'\n", - "b'14' []\n", - "b'15'\n", - "b'15' []\n", - "b'16'\n", - "b'16' []\n", - "b'17'\n", - "b'17' []\n", - "b'18'\n", - "b'18' []\n", - "b'19'\n", - "b'19' []\n", - "b'20'\n", - "b'20' []\n", - "b'21'\n", - "b'21' []\n", - "b'22'\n", - "b'22' []\n", - "b'23'\n", - "b'23' []\n", - "b'24'\n", - "b'24' []\n", - "b'25'\n", - "b'25' []\n", - "b'26'\n", - "b'26' []\n", - "b'27'\n", - "b'27' []\n", - "b'28'\n", - "b'28' []\n", - "b'29'\n", - "b'29' []\n", - "b'30'\n", - "b'30' []\n", - "b'31'\n", - "b'31' []\n", - "b'32'\n", - "b'32' []\n", - "b'33'\n", - "b'33' []\n", - "b'34'\n", - "b'34' []\n", - "b'35'\n", - "b'35' []\n", - "b'36'\n", - "b'36' []\n", - "b'37'\n", - "b'37' []\n", - "b'38'\n", - "b'38' []\n", - "b'39'\n", - "b'39' []\n", - "b'40'\n", - "b'40' []\n", - "b'41'\n", - "b'41' []\n", - "b'42'\n", - "b'42' []\n", - "b'43'\n", - "b'43' []\n", - "b'44'\n", - "b'44' []\n", - "b'45'\n", - "b'45' []\n", - "b'46'\n", - "b'46' []\n", - "b'47'\n", - "b'47' []\n", - "b'48'\n", - "b'48' []\n", - "b'49'\n", - "b'49' []\n", - "b'50'\n", - "b'50' []\n", - "b'51'\n", - "b'51' []\n", - "b'52'\n", - "b'52' []\n", - "b'53'\n", - "b'53' []\n", - "b'54'\n", - "b'54' []\n", - "b'55'\n", - "b'55' []\n", - "b'56'\n", - "b'56' []\n", - "b'57'\n", - "b'57' []\n", - "b'58'\n", - "b'58' []\n", - "b'59'\n", - "b'59' []\n", - "b'60'\n", - "b'60' []\n", - "b'61'\n", - "b'61' []\n", - "b'62'\n", - "b'62' []\n", - "b'63'\n", - "b'63' []\n", - "b'64'\n", - "b'64' []\n", - "b'65'\n", - "b'65' []\n", - "b'66'\n", - "b'66' []\n", - "b'67'\n", - "b'67' []\n", - "b'68'\n", - "b'68' []\n", - "b'69'\n", - "b'69' []\n", - "b'70'\n", - "b'70' []\n", - "b'71'\n", - "b'71' []\n", - "b'72'\n", - "b'72' []\n", - "b'73'\n", - "b'73' []\n", - "b'74'\n", - "b'74' []\n", - "b'75'\n", - "b'75' []\n", - "b'76'\n", - "b'76' []\n", - "b'77'\n", - "b'77' []\n", - "b'78'\n", - "b'78' []\n", - "b'79'\n", - "b'79' []\n", - "b'80'\n", - "b'80' []\n", - "b'81'\n", - "b'81' []\n", - "b'82'\n", - "b'82' []\n", - "b'83'\n", - "b'83' []\n", - "b'84'\n", - "b'84' []\n", - "b'85'\n", - "b'85' []\n", - "b'86'\n", - "b'86' []\n", - "b'87'\n", - "b'87' []\n", - "b'88'\n", - "b'88' []\n", - "b'89'\n", - "b'89' []\n", - "b'90'\n", - "b'90' []\n", - "b'91'\n", - "b'91' []\n", - "b'92'\n", - "b'92' []\n", - "b'93'\n", - "b'93' []\n", - "b'94'\n", - "b'94' []\n", - "b'95'\n", - "b'95' []\n", - "b'96'\n", - "b'96' []\n", - "b'97'\n", - "b'97' []\n", - "b'98'\n", - "b'98' []\n", - "b'99'\n", - "b'99' []\n", - "b'100'\n", - "b'100' []\n", - "b'101'\n", - "b'101' []\n", - "b'102'\n", - "b'102' []\n", - "b'103'\n", - "b'103' []\n", - "b'104'\n", - "b'104' []\n", - "b'105'\n", - "b'105' []\n", - "b'106'\n", - "b'106' []\n", - "b'107'\n", - "b'107' []\n", - "b'108'\n", - "b'108' []\n", - "b'109'\n", - "b'109' []\n", - "b'110'\n", - "b'110' []\n", - "b'111'\n", - "b'111' []\n", - "b'112'\n", - "b'112' []\n", - "b'113'\n", - "b'113' []\n", - "b'114'\n", - "b'114' []\n", - "b'115'\n", - "b'115' []\n", - "b'116'\n", - "b'116' []\n", - "b'117'\n", - "b'117' []\n", - "b'118'\n", - "b'118' []\n", - "b'119'\n", - "b'119' []\n", - "b'120'\n", - "b'120' []\n", - "b'121'\n", - "b'121' []\n", - "b'122'\n", - "b'122' []\n", - "b'123'\n", - "b'123' []\n", - "b'124'\n", - "b'124' []\n", - "b'125'\n", - "b'125' []\n", - "b'126'\n", - "b'126' []\n", - "b'127'\n", - "b'127' []\n", - "b'128'\n", - "b'128' []\n", - "b'129'\n", - "b'129' []\n", - "b'130'\n", - "b'130' []\n", - "b'131'\n", - "b'131' []\n", - "b'132'\n", - "b'132' []\n", - "b'133'\n", - "b'133' []\n", - "b'134'\n", - "b'134' []\n", - "b'135'\n", - "b'135' []\n", - "b'136'\n", - "b'136' []\n", - "b'137'\n", - "b'137' []\n", - "b'138'\n", - "b'138' []\n", - "b'139'\n", - "b'139' []\n", - "b'140'\n", - "b'140' []\n", - "b'141'\n", - "b'141' []\n", - "b'142'\n", - "b'142' []\n", - "b'143'\n", - "b'143' []\n", - "b'144'\n", - "b'144' []\n", - "b'145'\n", - "b'145' []\n", - "b'146'\n", - "b'146' []\n", - "b'147'\n", - "b'147' []\n", - "b'148'\n", - "b'148' []\n", - "b'149'\n", - "b'149' []\n", - "b'150'\n", - "b'150' []\n", - "b'151'\n", - "b'151' []\n", - "b'152'\n", - "b'152' []\n", - "b'153'\n", - "b'153' []\n", - "b'154'\n", - "b'154' []\n", - "b'155'\n", - "b'155' []\n", - "b'156'\n", - "b'156' []\n", - "b'157'\n", - "b'157' []\n", - "b'158'\n", - "b'158' []\n", - "b'159'\n", - "b'159' []\n", - "b'160'\n", - "b'160' []\n", - "b'161'\n", - "b'161' []\n", - "b'162'\n", - "b'162' []\n", - "b'163'\n", - "b'163' []\n", - "b'164'\n", - "b'164' []\n", - "b'165'\n", - "b'165' []\n", - "b'166'\n", - "b'166' []\n", - "b'167'\n", - "b'167' []\n", - "b'168'\n", - "b'168' []\n", - "b'169'\n", - "b'169' []\n", - "b'170'\n", - "b'170' []\n", - "b'171'\n", - "b'171' []\n", - "b'172'\n", - "b'172' []\n", - "b'173'\n", - "b'173' []\n", - "b'174'\n", - "b'174' []\n" - ] - } - ], - "source": [ - "for n in nums[0].split():\n", - " print(n)\n", - " reports = extract_report(fetch_msg(n))\n", - " print(n, reports)" - ] - }, - { - "cell_type": "code", - "execution_count": 45, - "metadata": { - "collapsed": false - }, - "outputs": [ - { - "data": { - "text/plain": [ - "[,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ,\n", - " ]" - ] - }, - "execution_count": 45, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "dmarc_reports = [report for report_set in [extract_report(fetch_msg(n)) for n in nums[0].split()]\n", - " for report in report_set]\n", - "dmarc_reports" - ] - }, - { - "cell_type": "code", - "execution_count": 46, + "execution_count": 6, "metadata": { - "collapsed": false + "collapsed": true }, - "outputs": [ - { - "data": { - "text/plain": [ - "('BYE', [b'Logging out'])" - ] - }, - "execution_count": 46, - "metadata": {}, - "output_type": "execute_result" - } - ], + "outputs": [], "source": [ - "mailbox.close()\n", - "mailbox.logout()" + "def maybe_strip(text):\n", + " if text:\n", + " return text.strip()\n", + " else:\n", + " return ''" ] }, { "cell_type": "code", - "execution_count": 47, + "execution_count": 7, "metadata": { "collapsed": true }, @@ -812,28 +165,82 @@ }, { "cell_type": "code", - "execution_count": 48, + "execution_count": 8, "metadata": { "collapsed": true }, "outputs": [], "source": [ - "def maybe_strip(text):\n", - " if text:\n", - " return text.strip()\n", - " else:\n", - " return ''" + "def build_insert_command(table_name, report, preamble_values=None, i=None):\n", + " field_names = []\n", + " if preamble_values:\n", + " values = preamble_values.copy()\n", + " else:\n", + " values = {}\n", + " for f in [f for f in field_maps if field_maps[f]['pg_table'] == table_name]:\n", + " # print(f)\n", + " if i:\n", + " fp = f.format(i)\n", + " else:\n", + " fp = f\n", + " field_names += [field_maps[f]['pg_field_name']]\n", + " if field_maps[f]['pg_type'] == 'int':\n", + " values[field_maps[f]['pg_field_name']] = int(report.find(fp).text)\n", + " elif field_maps[f]['pg_type'] == 'timestamp':\n", + " values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(fp).text))\n", + " elif field_maps[f]['pg_type'] == 'inet':\n", + " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text)\n", + " else:\n", + " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text)\n", + " insert_string = 'insert into {} ('.format(table_name)\n", + " if preamble_values:\n", + " insert_string += ', '.join(sorted(preamble_values.keys())) + ', '\n", + " insert_string += ', '.join(field_names) + ') '\n", + " insert_string += 'values ('\n", + " if preamble_values:\n", + " insert_string += ', '.join('%({})s'.format(fn) for fn in sorted(preamble_values.keys())) + ', '\n", + " insert_string += ', '.join('%({})s'.format(f) for f in field_names) + ');'\n", + " return insert_string, values" ] }, { "cell_type": "code", - "execution_count": 49, + "execution_count": 9, "metadata": { "collapsed": true }, "outputs": [], "source": [ "def write_report(connection, cursor, report):\n", + " insert_string, values = build_insert_command('reports', report)\n", + " # print(insert_string, values)\n", + " cursor.execute(insert_string, values)\n", + " \n", + " for i in range(1, len(report.findall('./record'))+1):\n", + " field_names = []\n", + " cursor.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n", + " [report.find('./report_metadata/report_id').text])\n", + " results = cursor.fetchall()\n", + " if len(results) != 1:\n", + " raise RuntimeError('Could not find report record for report item')\n", + " else:\n", + " report_id = results[0][0]\n", + " insert_string, values = build_insert_command('report_items', report, i=i,\n", + " preamble_values={'report_id': report_id})\n", + " # print(insert_string, values)\n", + " cursor.execute(insert_string, values)\n", + " connection.commit()" + ] + }, + { + "cell_type": "code", + "execution_count": 10, + "metadata": { + "collapsed": true + }, + "outputs": [], + "source": [ + "def write_report_old(connection, cursor, report):\n", " \n", " field_names = []\n", " values = {}\n", @@ -849,6 +256,7 @@ " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f).text)\n", " insert_string = 'insert into reports (' + ', '.join(field_names) + ') '\n", " insert_string += 'values (' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n", + " # print(insert_string, values)\n", " cursor.execute(insert_string, values)\n", " \n", " for i in range(1, len(report.findall('./record'))+1):\n", @@ -873,36 +281,203 @@ " values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f.format(i)).text)\n", " insert_string = 'insert into report_items (report_id, ' + ', '.join(field_names) + ') '\n", " insert_string += 'values (%(report_id)s, ' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n", + " # print(insert_string, values)\n", " cursor.execute(insert_string, values)\n", " connection.commit()" ] }, { "cell_type": "code", - "execution_count": 51, + "execution_count": 11, "metadata": { - "collapsed": true + "collapsed": false }, - "outputs": [], + "outputs": [ + { + "data": { + "text/plain": [ + "['dmarc.ini']" + ] + }, + "execution_count": 11, + "metadata": {}, + "output_type": "execute_result" + } + ], "source": [ - "conn = psycopg2.connect(host=config['database']['server'],\n", + "config = configparser.ConfigParser()\n", + "config.read('dmarc.ini')" + ] + }, + { + "cell_type": "code", + "execution_count": 12, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "datetime.datetime(2016, 3, 29, 23, 59, 59)" + ] + }, + "execution_count": 12, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "with psycopg2.connect(host=config['database']['server'],\n", " database=config['database']['database'], \n", " user=config['database']['username'], \n", - " password=config['database']['password'])\n", - "with conn.cursor() as cur:\n", - " for report in dmarc_reports:\n", - " write_report(conn, cur, report)" + " password=config['database']['password']) as conn:\n", + " with conn.cursor() as cur:\n", + " cur.execute('select max(report_metadata_date_range_end) from reports')\n", + " results = cur.fetchall()\n", + "most_recent_date = results[0][0]\n", + "most_recent_date" ] }, { "cell_type": "code", - "execution_count": 52, + "execution_count": 13, "metadata": { - "collapsed": true + "collapsed": false }, - "outputs": [], + "outputs": [ + { + "data": { + "text/plain": [ + "('OK', [b'178'])" + ] + }, + "execution_count": 13, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "mailbox = imaplib.IMAP4(host=config['imap']['server'], \n", + " port=config['imap']['port'])\n", + "mailbox.starttls()\n", + "mailbox.login(config['imap']['username'], config['imap']['password'])\n", + "mailbox.select('INBOX', readonly=True)" + ] + }, + { + "cell_type": "code", + "execution_count": 14, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "('SINCE 27-Mar-2016', 'OK', [b'169 170 171 172 173 174 175 176 177 178 179'])" + ] + }, + "execution_count": 14, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "if most_recent_date:\n", + " mails_from = \"SINCE \" + (most_recent_date - datetime.timedelta(days=2)).strftime(\"%d-%b-%Y\")\n", + "else:\n", + " mails_from = \"ALL\"\n", + "resp, nums = mailbox.uid('SEARCH', None, mails_from)\n", + "mails_from, resp, nums" + ] + }, + { + "cell_type": "code", + "execution_count": 15, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "['1458957186.548175',\n", + " '2150510829392606201',\n", + " '68aad5080a774e2c997d159b546569b9@hotmail.com',\n", + " '1459129809.695034',\n", + " '16143280651570354241',\n", + " '8c177254c3cb41869dc3afab59f74c76@hotmail.com',\n", + " '15410706527896810898',\n", + " '1459216304.582931',\n", + " '15497495941279624940',\n", + " '1459302353.261157',\n", + " '7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com']" + ] + }, + "execution_count": 15, + "metadata": {}, + "output_type": "execute_result" + } + ], "source": [ - "conn.close()" + "dmarc_reports = [report for report_set in [extract_report(fetch_msg(n)) for n in nums[0].split()]\n", + " for report in report_set]\n", + "[r.find('./report_metadata/report_id').text for r in dmarc_reports]" + ] + }, + { + "cell_type": "code", + "execution_count": 16, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "('BYE', [b'Logging out'])" + ] + }, + "execution_count": 16, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "mailbox.close()\n", + "mailbox.logout()" + ] + }, + { + "cell_type": "code", + "execution_count": 18, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "write 1459302353.261157\n", + "write 7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com\n" + ] + } + ], + "source": [ + "with psycopg2.connect(host=config['database']['server'],\n", + " database=config['database']['database'], \n", + " user=config['database']['username'], \n", + " password=config['database']['password']) as conn:\n", + " with conn.cursor() as cur:\n", + " for report in dmarc_reports:\n", + " cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n", + " [report.find('./report_metadata/report_id').text])\n", + " results = cur.fetchall()\n", + " if not results:\n", + " print('write', report.find('./report_metadata/report_id').text)\n", + " write_report(conn, cur, report)" ] }, { diff --git a/dmarc_to_database.py b/dmarc_to_database.py new file mode 100644 index 0000000..2fd2375 --- /dev/null +++ b/dmarc_to_database.py @@ -0,0 +1,202 @@ +import configparser +import imaplib +import email +import io +import zipfile +import xml.etree.ElementTree +import psycopg2 +import re +import datetime + +def fetch_msg(num): + return mailbox.uid('FETCH', num, '(RFC822)')[1][0][1] + +def xml_of_part(part): + with zipfile.ZipFile(io.BytesIO(part.get_payload(decode=True))) as zf: + fn = zf.infolist()[0].filename + contents = zf.read(fn).decode('utf-8') + return xml.etree.ElementTree.fromstring(contents) + + +def xml_of(message): + reports = [] + if message.is_multipart(): + for p in message.get_payload(): + if 'zip' in p.get_content_type(): + reports += [xml_of_part(p)] + else: + reports = [xml_of_part(message)] + return reports + +def extract_report(msg): + pmsg = email.message_from_bytes(msg) + return xml_of(pmsg) + +def maybe_strip(text): + if text: + return text.strip() + else: + return '' + +field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_adkim', + 'pg_table': 'reports', + 'pg_type': 'varchar'}, + './policy_published/aspf': {'pg_field_name': 'policy_published_aspf', + 'pg_table': 'reports', + 'pg_type': 'varchar'}, + './policy_published/domain': {'pg_field_name': 'policy_published_domain', + 'pg_table': 'reports', + 'pg_type': 'varchar'}, + './policy_published/p': {'pg_field_name': 'policy_published_p', + 'pg_table': 'reports', + 'pg_type': 'varchar'}, + './policy_published/pct': {'pg_field_name': 'policy_published_pct', + 'pg_table': 'reports', + 'pg_type': 'int'}, + './record[{}]/auth_results/dkim/domain': {'pg_field_name': 'auth_results_dkim_domain', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/auth_results/dkim/result': {'pg_field_name': 'auth_results_dkim_result', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/auth_results/spf/domain': {'pg_field_name': 'auth_results_spf_domain', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/auth_results/spf/result': {'pg_field_name': 'auth_results_spf_result', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/identifiers/header_from': {'pg_field_name': 'identifiers_header_from', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/row/count': {'pg_field_name': 'count', + 'pg_table': 'report_items', + 'pg_type': 'int'}, + './record[{}]/row/policy_evaluated/disposition': {'pg_field_name': 'policy_evaluated_disposition', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/row/policy_evaluated/dkim': {'pg_field_name': 'policy_evaluated_dkim', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/row/policy_evaluated/spf': {'pg_field_name': 'policy_evaluated_spf', + 'pg_table': 'report_items', + 'pg_type': 'varchar'}, + './record[{}]/row/source_ip': {'pg_field_name': 'source_ip', + 'pg_table': 'report_items', + 'pg_type': 'inet'}, + './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin', + 'pg_table': 'reports', + 'pg_type': 'timestamp'}, + './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end', + 'pg_table': 'reports', + 'pg_type': 'timestamp'}, + './report_metadata/email': {'pg_field_name': 'report_metadata_email', + 'pg_table': 'reports', + 'pg_type': 'varchar'}, + './report_metadata/org_name': {'pg_field_name': 'report_metadata_org_name', + 'pg_table': 'reports', + 'pg_type': 'varchar'}, + './report_metadata/report_id': {'pg_field_name': 'report_metadata_report_id', + 'pg_table': 'reports', + 'pg_type': 'varchar'}} + + + +def build_insert_command(table_name, report, preamble_values=None, i=None): + field_names = [] + if preamble_values: + values = preamble_values.copy() + else: + values = {} + for f in [f for f in field_maps if field_maps[f]['pg_table'] == table_name]: + if i: + fp = f.format(i) + else: + fp = f + field_names += [field_maps[f]['pg_field_name']] + if field_maps[f]['pg_type'] == 'int': + values[field_maps[f]['pg_field_name']] = int(report.find(fp).text) + elif field_maps[f]['pg_type'] == 'timestamp': + values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(fp).text)) + elif field_maps[f]['pg_type'] == 'inet': + values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text) + else: + values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text) + insert_string = 'insert into {} ('.format(table_name) + if preamble_values: + insert_string += ', '.join(sorted(preamble_values.keys())) + ', ' + insert_string += ', '.join(field_names) + ') ' + insert_string += 'values (' + if preamble_values: + insert_string += ', '.join('%({})s'.format(fn) for fn in sorted(preamble_values.keys())) + ', ' + insert_string += ', '.join('%({})s'.format(f) for f in field_names) + ');' + return insert_string, values + + +def write_report(connection, cursor, report): + insert_string, values = build_insert_command('reports', report) + # print(insert_string, values) + cursor.execute(insert_string, values) + + for i in range(1, len(report.findall('./record'))+1): + field_names = [] + cursor.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', + [report.find('./report_metadata/report_id').text]) + results = cursor.fetchall() + if len(results) != 1: + raise RuntimeError('Could not find report record for report item') + else: + report_id = results[0][0] + insert_string, values = build_insert_command('report_items', report, i=i, + preamble_values={'report_id': report_id}) + # print(insert_string, values) + cursor.execute(insert_string, values) + connection.commit() + +config = configparser.ConfigParser() +config.read('dmarc.ini') + +with psycopg2.connect(host=config['database']['server'], + database=config['database']['database'], + user=config['database']['username'], + password=config['database']['password']) as conn: + with conn.cursor() as cur: + cur.execute('select max(report_metadata_date_range_end) from reports') + results = cur.fetchall() +most_recent_date = results[0][0] + +mailbox = imaplib.IMAP4(host=config['imap']['server'], + port=config['imap']['port']) +mailbox.starttls() +mailbox.login(config['imap']['username'], config['imap']['password']) +mailbox.select('INBOX', readonly=True) + + +if most_recent_date: + mails_from = "SINCE " + (most_recent_date - datetime.timedelta(days=2)).strftime("%d-%b-%Y") +else: + mails_from = "ALL" +resp, nums = mailbox.uid('SEARCH', None, mails_from) + + +dmarc_reports = [report for report_set in [extract_report(fetch_msg(n)) for n in nums[0].split()] + for report in report_set] + +mailbox.close() +mailbox.logout() + + +with psycopg2.connect(host=config['database']['server'], + database=config['database']['database'], + user=config['database']['username'], + password=config['database']['password']) as conn: + with conn.cursor() as cur: + for report in dmarc_reports: + cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', + [report.find('./report_metadata/report_id').text]) + results = cur.fetchall() + if not results: + print('write', report.find('./report_metadata/report_id').text) + write_report(conn, cur, report) + + + diff --git a/make_database.ipynb b/make_database.ipynb index 895bed8..793df6c 100644 --- a/make_database.ipynb +++ b/make_database.ipynb @@ -2,7 +2,7 @@ "cells": [ { "cell_type": "code", - "execution_count": 22, + "execution_count": 14, "metadata": { "collapsed": true }, @@ -14,7 +14,7 @@ }, { "cell_type": "code", - "execution_count": 23, + "execution_count": 15, "metadata": { "collapsed": true }, @@ -84,7 +84,7 @@ }, { "cell_type": "code", - "execution_count": 24, + "execution_count": 16, "metadata": { "collapsed": false }, @@ -95,7 +95,7 @@ "['dmarc.ini']" ] }, - "execution_count": 24, + "execution_count": 16, "metadata": {}, "output_type": "execute_result" } @@ -107,7 +107,7 @@ }, { "cell_type": "code", - "execution_count": 25, + "execution_count": 17, "metadata": { "collapsed": false }, @@ -121,7 +121,7 @@ }, { "cell_type": "code", - "execution_count": 26, + "execution_count": 18, "metadata": { "collapsed": false }, @@ -139,7 +139,7 @@ }, { "cell_type": "code", - "execution_count": 27, + "execution_count": 19, "metadata": { "collapsed": false }, @@ -147,10 +147,10 @@ { "data": { "text/plain": [ - "'create table reports (id serial primary key, policy_published_pct int, policy_published_adkim varchar, report_metadata_org_name varchar, policy_published_aspf varchar, policy_published_domain varchar, report_metadata_date_range_end timestamp, policy_published_p varchar, report_metadata_report_id varchar, report_metadata_email varchar, report_metadata_date_range_begin timestamp);'" + "'create table reports (id serial primary key, report_metadata_org_name varchar, policy_published_domain varchar, report_metadata_email varchar, report_metadata_date_range_begin timestamp, report_metadata_date_range_end timestamp, policy_published_adkim varchar, report_metadata_report_id varchar, policy_published_aspf varchar, policy_published_pct int, policy_published_p varchar);'" ] }, - "execution_count": 27, + "execution_count": 19, "metadata": {}, "output_type": "execute_result" } @@ -165,7 +165,7 @@ }, { "cell_type": "code", - "execution_count": 28, + "execution_count": 20, "metadata": { "collapsed": false }, @@ -173,10 +173,10 @@ { "data": { "text/plain": [ - "'create table report_items (id serial primary key, report_id integer references reports, auth_results_spf_domain varchar, source_ip inet, policy_evaluated_spf varchar, count int, auth_results_dkim_result varchar, auth_results_dkim_domain varchar, policy_evaluated_disposition varchar, identifiers_header_from varchar, policy_evaluated_dkim varchar, auth_results_spf_result varchar);'" + "'create table report_items (id serial primary key, report_id integer references reports, auth_results_dkim_result varchar, auth_results_spf_result varchar, count int, auth_results_dkim_domain varchar, source_ip inet, policy_evaluated_disposition varchar, identifiers_header_from varchar, auth_results_spf_domain varchar, policy_evaluated_dkim varchar, policy_evaluated_spf varchar);'" ] }, - "execution_count": 28, + "execution_count": 20, "metadata": {}, "output_type": "execute_result" } @@ -192,7 +192,7 @@ }, { "cell_type": "code", - "execution_count": 29, + "execution_count": 21, "metadata": { "collapsed": true }, @@ -201,6 +201,8 @@ "with conn.cursor() as cur:\n", " cur.execute(create_report_table_string)\n", " cur.execute(create_report_item_table_string)\n", + " cur.execute('create index on reports (report_metadata_date_range_end);')\n", + " cur.execute('create index on reports (report_metadata_report_id);')\n", "conn.commit()" ] }, diff --git a/queries.ipynb b/queries.ipynb new file mode 100644 index 0000000..b2a54d3 --- /dev/null +++ b/queries.ipynb @@ -0,0 +1,456 @@ +{ + "cells": [ + { + "cell_type": "code", + "execution_count": 18, + "metadata": { + "collapsed": true + }, + "outputs": [], + "source": [ + "import configparser\n", + "import imaplib\n", + "import email\n", + "import io\n", + "import zipfile\n", + "import xml.etree.ElementTree\n", + "import psycopg2\n", + "import re\n", + "import datetime\n", + "import socket" + ] + }, + { + "cell_type": "code", + "execution_count": 5, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "['dmarc.ini']" + ] + }, + "execution_count": 5, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "config = configparser.ConfigParser()\n", + "config.read('dmarc.ini')" + ] + }, + { + "cell_type": "code", + "execution_count": 31, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "[(('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'pass', 'pass', '2607:f8b0:400d:c04::22a'),\n", + " 'mail-qg0-x22a.google.com'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '54.84.192.51'), 'mail1.keybase.io'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n", + " (('google.com', 'softfail', 'pass', '94.136.40.149'), 'mailex.mailcore.me'),\n", + " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::232'),\n", + " 'mail-wm0-x232.google.com'),\n", + " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::231'),\n", + " 'mail-wm0-x231.google.com'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n", + " (('google.com', 'pass', 'pass', '82.109.184.9'), 'clublloyds.com'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk')]" + ] + }, + "execution_count": 31, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "with psycopg2.connect(host=config['database']['server'],\n", + " database=config['database']['database'], \n", + " user=config['database']['username'], \n", + " password=config['database']['password']) as conn:\n", + " with conn.cursor() as cur:\n", + " cur.execute(\"\"\"\n", + " select report_metadata_org_name, auth_results_spf_result, auth_results_dkim_result, source_ip \n", + " from reports, report_items\n", + " where report_items.report_id = reports.id and\n", + " source_ip <> inet '212.69.55.62'\"\"\")\n", + " results = cur.fetchall()\n", + "[(r, socket.gethostbyaddr(r[3])[0]) for r in results]" + ] + }, + { + "cell_type": "code", + "execution_count": 32, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "[(('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '54.84.192.51'), 'mail1.keybase.io'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n", + " (('google.com', 'softfail', 'pass', '94.136.40.149'), 'mailex.mailcore.me'),\n", + " (('google.com', 'softfail', 'pass', '94.136.40.147'), 'mailex.mailcore.me'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::232'),\n", + " 'mail-wm0-x232.google.com'),\n", + " (('google.com', 'softfail', 'pass', '2a00:1450:400c:c09::231'),\n", + " 'mail-wm0-x231.google.com'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n", + " (('google.com', 'softfail', 'pass', '146.185.136.235'), 'new.realms.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk'),\n", + " (('Yahoo! Inc.', 'softfail', 'pass', '65.20.0.12'), 'lb.lon5.cpcloud.co.uk')]" + ] + }, + "execution_count": 32, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "with psycopg2.connect(host=config['database']['server'],\n", + " database=config['database']['database'], \n", + " user=config['database']['username'], \n", + " password=config['database']['password']) as conn:\n", + " with conn.cursor() as cur:\n", + " cur.execute(\"\"\"\n", + " select report_metadata_org_name, auth_results_spf_result, auth_results_dkim_result, source_ip \n", + " from reports, report_items\n", + " where report_items.report_id = reports.id and\n", + " auth_results_spf_result <> 'pass'\"\"\")\n", + " results = cur.fetchall()\n", + "[(r, socket.gethostbyaddr(r[3])[0]) for r in results]" + ] + }, + { + "cell_type": "code", + "execution_count": 20, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "'lb.lon5.cpcloud.co.uk'" + ] + }, + "execution_count": 20, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "socket.gethostbyaddr('65.20.0.12')[0]" + ] + }, + { + "cell_type": "code", + "execution_count": 30, + "metadata": { + "collapsed": false + }, + "outputs": [ + { + "data": { + "text/plain": [ + "[('02aca4c6bb144bb6b54773374c14a43a@hotmail.com',),\n", + " ('10356602625290246518',),\n", + " ('10491829640821461358',),\n", + " ('10706879170593875813',),\n", + " ('10744929815548178249',),\n", + " ('11019573554877343650',),\n", + " ('1102163566627474204',),\n", + " ('11743474449139138027',),\n", + " ('1178c4fe34364ff39cb6e24a51219093@hotmail.com',),\n", + " ('1207168150882246442',),\n", + " ('12137203242660163862',),\n", + " ('121bb39637c14847a40669dd3e7cdf49@hotmail.com',),\n", + " ('1233953228184721030',),\n", + " ('12434676377746978866',),\n", + " ('12700770160536483846',),\n", + " ('129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com',),\n", + " ('13032699446207263761',),\n", + " ('13702792868066092542',),\n", + " ('13959504937502303973',),\n", + " ('13f28e952d67451281b90d55eee9c7c3@hotmail.com',),\n", + " ('14089985608998676943',),\n", + " ('14217880258295310962',),\n", + " ('14416654486328871570',),\n", + " ('1448246712.259522',),\n", + " ('1448419746.206772',),\n", + " ('1448592471.754446',),\n", + " ('1448851661.602960',),\n", + " ('1448938177.97045',),\n", + " ('1449024342.601561',),\n", + " ('1449110875.443288',),\n", + " ('1449197294.177690',),\n", + " ('1449283514.868026',),\n", + " ('1449542804.259788',),\n", + " ('1449629119.120001',),\n", + " ('1449888489.668429',),\n", + " ('1449974899.615312',),\n", + " ('1450234735.690828',),\n", + " ('1451184218.12383',),\n", + " ('1451443783.692950',),\n", + " ('1451616408.476341',),\n", + " ('1452134765.308922',),\n", + " ('1452393953.83099',),\n", + " ('1452480188.666644',),\n", + " ('1452566814.805501',),\n", + " ('1452653275.294149',),\n", + " ('1452741832.342075',),\n", + " ('1452826445.971145',),\n", + " ('1452912512.114840',),\n", + " ('1453084984.369330',),\n", + " ('1453171440.157305',),\n", + " ('1453258067.440890',),\n", + " ('1453344550.144698',),\n", + " ('1453517236.399019',),\n", + " ('1453689981.768611',),\n", + " ('1453776417.898739',),\n", + " ('1453862958.482897',),\n", + " ('1454381343.228960',),\n", + " ('1454467952.258012',),\n", + " ('1454640482.903992',),\n", + " ('1454986282.636073',),\n", + " ('1455072659.49250',),\n", + " ('1455418081.446180',),\n", + " ('1455679107.469767',),\n", + " ('1455763965.140751',),\n", + " ('1456109267.60200',),\n", + " ('1456282325.455667',),\n", + " ('1456455410.661669',),\n", + " ('1456541727.873510',),\n", + " ('1456713880.905762',),\n", + " ('1456800606.884839',),\n", + " ('1456886761.643377',),\n", + " ('1457059537.625770',),\n", + " ('1457146943.63077',),\n", + " ('1457232742.472142',),\n", + " ('1457318880.887129',),\n", + " ('1457491692.793016',),\n", + " ('1457750956.253773',),\n", + " ('1457919850.989656',),\n", + " ('1458006833.731671',),\n", + " ('1458093082.193350',),\n", + " ('1458353823.362308',),\n", + " ('1458524834.32161',),\n", + " ('1458611429.595569',),\n", + " ('1458784545.303847',),\n", + " ('1458957186.548175',),\n", + " ('1459129809.695034',),\n", + " ('1459216304.582931',),\n", + " ('14593873841710243963',),\n", + " ('14661842628106423589',),\n", + " ('14662396456930987863',),\n", + " ('15111277194568576101',),\n", + " ('15410706527896810898',),\n", + " ('15497495941279624940',),\n", + " ('15974729567081493290',),\n", + " ('16143280651570354241',),\n", + " ('16409503372348966326',),\n", + " ('164180405175632647',),\n", + " ('16520810497197721925',),\n", + " ('16824795799639390032',),\n", + " ('17044038014166863505',),\n", + " ('17071564483265101388',),\n", + " ('17659080448126516391',),\n", + " ('17880573400612806781',),\n", + " ('17984785070730076605',),\n", + " ('18203685646345145151',),\n", + " ('1862812391966551302',),\n", + " ('18c9a966da81401e80ca2a22310ffee6@hotmail.com',),\n", + " ('1922265692629464594',),\n", + " ('1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com',),\n", + " ('1fadaaf3d9d544568a41042a1f42df05@hotmail.com',),\n", + " ('2002148329698541727',),\n", + " ('20246ec4a70041caa276560032fdc595@hotmail.com',),\n", + " ('2150510829392606201',),\n", + " ('2273327349069127175',),\n", + " ('271c80b9234a4c99bb9608abc5470c99@hotmail.com',),\n", + " ('2884439811958006165',),\n", + " ('2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com',),\n", + " ('30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com',),\n", + " ('3188090359734380692',),\n", + " ('3267388508667988463',),\n", + " ('3508955360705645678',),\n", + " ('3544432548848738700',),\n", + " ('3584764172722215181',),\n", + " ('3689737760348689620',),\n", + " ('36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com',),\n", + " ('3eba8b9e31724809aaf8d517e5656c4b@hotmail.com',),\n", + " ('4158049796460953654',),\n", + " ('4226319448276355598',),\n", + " ('43e7b5e2e0fb400aa914058360c8aaab@hotmail.com',),\n", + " ('456ffff78456494fbaed47b465259d46@hotmail.com',),\n", + " ('4654132158287507198',),\n", + " ('511d6fbb8484454b916ad8d799938390@hotmail.com',),\n", + " ('5164714352250096219',),\n", + " ('543258397004515473',),\n", + " ('580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com',),\n", + " ('594749737641304465',),\n", + " ('6110291457768008366',),\n", + " ('641b4cc500334a7eb3f649b21af003ca@hotmail.com',),\n", + " ('6474240574237085621',),\n", + " ('6505353761044749597',),\n", + " ('6876229717666715946',),\n", + " ('68aad5080a774e2c997d159b546569b9@hotmail.com',),\n", + " ('6996084840178215735',),\n", + " ('7006513620611789399',),\n", + " ('724976339745122723',),\n", + " ('726a3261dfab4b4590b5fc898c561b08@hotmail.com',),\n", + " ('730219275619457',),\n", + " ('75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com',),\n", + " ('77b6c2aa32bf440aa240195db229cd4a@hotmail.com',),\n", + " ('7815164892280952980',),\n", + " ('7834597727856283739',),\n", + " ('840fc02d5cb847ec9f007fb236c0c190@hotmail.com',),\n", + " ('8426152571808739514',),\n", + " ('85136d21c514431e9f5cf9d36faa4a22@hotmail.com',),\n", + " ('8986675713054039362',),\n", + " ('8c177254c3cb41869dc3afab59f74c76@hotmail.com',),\n", + " ('9037150256193522538',),\n", + " ('9138782308848375092',),\n", + " ('9594298247075547297',),\n", + " ('a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com',),\n", + " ('b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com',),\n", + " ('ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com',),\n", + " ('ba64911adb0743d0b926588647ec5dcf@hotmail.com',),\n", + " ('c8e4fe772ae3434594e8396a8abf77b1@hotmail.com',),\n", + " ('c8f339f8f3364d78820dd03a84d11daa@hotmail.com',),\n", + " ('d1019291012e4f4589f59f1dacef31a3@hotmail.com',),\n", + " ('d71792d8afc246e9a4756bd13e600b59@hotmail.com',),\n", + " ('d77c5b90344149f5bb7b50ff69400aeb@hotmail.com',),\n", + " ('dbe2f49dbea947428b179e0ecec5f813@hotmail.com',),\n", + " ('de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com',),\n", + " ('e245ef81f05b4697bea34bed977d8286@hotmail.com',),\n", + " ('e327753089e04950906848d104a93695@hotmail.com',),\n", + " ('ea3a96df66a845c5a666068ef8ca1368@hotmail.com',),\n", + " ('eda16c25bbdb48b2bae0d18eab13f736@hotmail.com',),\n", + " ('f94f00dee713479fb8bfdbcad539da88@hotmail.com',),\n", + " ('fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com',)]" + ] + }, + "execution_count": 30, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "with psycopg2.connect(host=config['database']['server'],\n", + " database=config['database']['database'], \n", + " user=config['database']['username'], \n", + " password=config['database']['password']) as conn:\n", + " with conn.cursor() as cur:\n", + " cur.execute(\"\"\"\n", + " select report_metadata_report_id\n", + " from reports\"\"\")\n", + " results = cur.fetchall()\n", + "sorted(results)" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": { + "collapsed": true + }, + "outputs": [], + "source": [] + } + ], + "metadata": { + "kernelspec": { + "display_name": "Python 3", + "language": "python", + "name": "python3" + }, + "language_info": { + "codemirror_mode": { + "name": "ipython", + "version": 3 + }, + "file_extension": ".py", + "mimetype": "text/x-python", + "name": "python", + "nbconvert_exporter": "python", + "pygments_lexer": "ipython3", + "version": "3.4.3+" + } + }, + "nbformat": 4, + "nbformat_minor": 0 +} -- 2.34.1