apt-cron mails via imap receive + awk work, for putting them into a sqlite

[Denny Fuchs <linuxmail@4lin.net>]

Hello,

because of a requirement, we need to document, which packages (Weezy/Jessie/Stretch) where upgraded and which problems where fixed.
At the moment, we receive the apt-cron mails and copy the content into our MediaWiki (with some cleanups and Wiki syntax).
In that way, we make sure, not to have the duplicates in the Wiki.

That costs a half day and we want to make it better:

We use a python script, which receives the apt-cron mails (sorted by Debian version). This script creates for every mail a file, which has only the body as content (no headers).In the next step, we want to put the content of that "cleaned" files into sqlite DB, to have a tool, to remove the duplicates.

For example, the file "70.eml"

++++++++++++++++++++++++++++++++++++ cut ++++++++++++++++++++++++++++++++++++++++++++++

apticron report [Mon, 18 Jun 2018 11:27:07 +0000]
========================================================================

apticron has detected that some packages need upgrading on:

	auth.example.com
	[ 172.21.0.8 ]

The following packages are currently pending an upgrade:

	ldproxy 1.1.0-7
	libgcrypt20 1.7.6-2+deb9u3
	libperl5.24 5.24.1-3+deb9u4
	openotp 1.3.11-0
	perl 5.24.1-3+deb9u4
	perl-base 5.24.1-3+deb9u4
	perl-modules-5.24 5.24.1-3+deb9u4
	puppet-agent 5.5.3-1stretch
	spankey 2.0.0-3
	td-agent 3.2.0-0
	webadm 1.6.6-2

========================================================================

Package Details:

apt-listchanges: Reading changelogs...
apt-listchanges: Changelogs
---------------------------

--- Changes for libgcrypt20 ---
libgcrypt20 (1.7.6-2+deb9u3) stretch-security; urgency=high

 * Non-maintainer upload by the Security Team.
 * ecc: Add blinding for ECDSA (CVE-2018-0495)

-- Salvatore Bonaccorso <carnil@debian.org>  Fri, 15 Jun 2018 11:58:05 +0200

--- Changes for perl (libperl5.24 perl perl-base perl-modules-5.24) ---
perl (5.24.1-3+deb9u4) stretch-security; urgency=high

 * [SECURITY] CVE-2018-12015: fix directory traversal vulnerability
   in Archive-Tar (Closes: #900834)

-- Dominic Hargreaves <dom@earth.li>  Sun, 10 Jun 2018 18:37:28 +0100

--- Changes for ldproxy ---
ldproxy (1.1.0-7) experimental; urgency=low

 * Converted from .rpm format to .deb by alien version 8.90


-- root <root@max>  Thu, 14 Jun 2018 10:26:12 +0200

--- Changes for openotp ---
openotp (1.3.11-0) experimental; urgency=low

 * Converted from .rpm format to .deb by alien version 8.90


-- root <root@max>  Fri, 15 Jun 2018 17:31:47 +0200

--- Changes for puppet-agent ---
puppet-agent (5.5.3-1stretch) stretch; urgency=low

* Update to version 5.5.3

-- Puppet Labs <info@puppetlabs.com> Tue, 12 Jun 2018 19:56:37 +0000

--- Changes for spankey ---
spankey (2.0.0-3) experimental; urgency=low

 * Converted from .rpm format to .deb by alien version 8.90


-- root <root@max>  Wed, 13 Jun 2018 18:41:35 +0200

--- Changes for webadm ---
webadm (1.6.6-2) experimental; urgency=low

 * Converted from .rpm format to .deb by alien version 8.90


-- root <root@max>  Fri, 15 Jun 2018 18:35:11 +0200

========================================================================

You can perform the upgrade by issuing the command:

	apt-get dist-upgrade

as root on auth.example.com

--
apticron


++++++++++++++++++++++++++++++++++++ cut ++++++++++++++++++++++++++++++++++++++++++++++



I want to have this part in the sqlite:


libgcrypt20 (1.7.6-2+deb9u3) stretch-security; urgency=high

 * Non-maintainer upload by the Security Team.
 * ecc: Add blinding for ECDSA (CVE-2018-0495)


So, if I do a select * on the table, I get all these packages and maybe sorted by urgency and Debian Version. But the important part is,that I don't get duplicates back (DISTINCT?). At the very end, maybe I can put the Mediawiki syntax around the output.


But the first step is, how to get the AWK output into a sqlite DB ? Someone had the idea:


awk '/urgency/{file="tmp/"(FILENAME)(++i)".txt"}{print > file}' (which works on OSX, but not on Fedora18 ..) that creates files for every entry in tmp/ ... maybe it is possible to use that as an idea, but instead if creating files, put that output to the sqlite.


Any suggestions ?


cu denny