Adding upstream version 1:1.1.2.upstream/1%1.1.2upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

9 files changed, 765 insertions, 0 deletions

diff --git a/application/controllers/CertificateController.php b/application/controllers/CertificateController.php
new file mode 100644
index 0000000..414d1f3
--- /dev/null
+++ b/application/controllers/CertificateController.php
@@ -0,0 +1,40 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Exception\ConfigurationError;
+use Icinga\Module\X509\CertificateDetails;
+use Icinga\Module\X509\Controller;
+use ipl\Sql;
+
+class CertificateController extends Controller
+{
+    public function indexAction()
+    {
+        $certId = $this->params->getRequired('cert');
+
+        try {
+            $conn = $this->getDb();
+        } catch (ConfigurationError $_) {
+            $this->render('missing-resource', null, true);
+            return;
+        }
+
+        $cert = $conn->select(
+            (new Sql\Select())
+                ->from('x509_certificate')
+                ->columns('*')
+                ->where(['id = ?' => $certId])
+        )->fetch();
+
+        if ($cert === false) {
+            $this->httpNotFound($this->translate('Certificate not found.'));
+        }
+
+        $this->setTitle($this->translate('X.509 Certificate'));
+
+        $this->view->certificateDetails = (new CertificateDetails())
+            ->setCert($cert);
+    }
+}
diff --git a/application/controllers/CertificatesController.php b/application/controllers/CertificatesController.php
new file mode 100644
index 0000000..c145b03
--- /dev/null
+++ b/application/controllers/CertificatesController.php
@@ -0,0 +1,127 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Data\Filter\FilterExpression;
+use Icinga\Exception\ConfigurationError;
+use Icinga\Module\X509\CertificatesTable;
+use Icinga\Module\X509\Controller;
+use Icinga\Module\X509\FilterAdapter;
+use Icinga\Module\X509\SortAdapter;
+use Icinga\Module\X509\SqlFilter;
+use ipl\Web\Control\PaginationControl;
+use ipl\Sql;
+use ipl\Web\Url;
+
+class CertificatesController extends Controller
+{
+    public function indexAction()
+    {
+        $this
+            ->initTabs()
+            ->setTitle($this->translate('Certificates'));
+
+        try {
+            $conn = $this->getDb();
+        } catch (ConfigurationError $_) {
+            $this->render('missing-resource', null, true);
+            return;
+        }
+
+        $select = (new Sql\Select())
+            ->from('x509_certificate c')
+            ->columns([
+                'c.id', 'c.subject', 'c.issuer', 'c.version', 'c.self_signed', 'c.ca', 'c.trusted',
+                'c.pubkey_algo',  'c.pubkey_bits', 'c.signature_algo', 'c.signature_hash_algo',
+                'c.valid_from', 'c.valid_to',
+            ]);
+
+        $this->view->paginator = new PaginationControl(new Sql\Cursor($conn, $select), Url::fromRequest());
+        $this->view->paginator->apply();
+
+        $sortAndFilterColumns = [
+            'subject' => $this->translate('Certificate'),
+            'issuer' => $this->translate('Issuer'),
+            'version' => $this->translate('Version'),
+            'self_signed' => $this->translate('Is Self-Signed'),
+            'ca' => $this->translate('Is Certificate Authority'),
+            'trusted' => $this->translate('Is Trusted'),
+            'pubkey_algo' => $this->translate('Public Key Algorithm'),
+            'pubkey_bits' => $this->translate('Public Key Strength'),
+            'signature_algo' => $this->translate('Signature Algorithm'),
+            'signature_hash_algo' => $this->translate('Signature Hash Algorithm'),
+            'valid_from' => $this->translate('Valid From'),
+            'valid_to' => $this->translate('Valid To'),
+            'duration' => $this->translate('Duration'),
+            'expires' => $this->translate('Expiration')
+        ];
+
+        $this->setupSortControl(
+            $sortAndFilterColumns,
+            new SortAdapter($select, function ($field) {
+                if ($field === 'duration') {
+                    return '(valid_to - valid_from)';
+                } elseif ($field === 'expires') {
+                    return 'CASE WHEN UNIX_TIMESTAMP() > valid_to'
+                        . ' THEN 0 ELSE (valid_to - UNIX_TIMESTAMP()) / 86400 END';
+                }
+            })
+        );
+
+        $this->setupLimitControl();
+
+        $filterAdapter = new FilterAdapter();
+        $this->setupFilterControl(
+            $filterAdapter,
+            $sortAndFilterColumns,
+            ['subject', 'issuer'],
+            ['format']
+        );
+        SqlFilter::apply($select, $filterAdapter->getFilter(), function (FilterExpression $filter) {
+            switch ($filter->getColumn()) {
+                case 'issuer_hash':
+                    $value = $filter->getExpression();
+
+                    if (is_array($value)) {
+                        $value = array_map('hex2bin', $value);
+                    } else {
+                        $value = hex2bin($value);
+                    }
+
+                    return $filter->setExpression($value);
+                case 'duration':
+                    return $filter->setColumn('(valid_to - valid_from)');
+                case 'expires':
+                    return $filter->setColumn(
+                        'CASE WHEN UNIX_TIMESTAMP() > valid_to THEN 0 ELSE (valid_to - UNIX_TIMESTAMP()) / 86400 END'
+                    );
+                case 'valid_from':
+                case 'valid_to':
+                    $expr = $filter->getExpression();
+                    if (! is_numeric($expr)) {
+                        return $filter->setExpression(strtotime($expr));
+                    }
+
+                    // expression doesn't need changing
+                default:
+                    return false;
+            }
+        });
+
+        $this->handleFormatRequest($conn, $select, function (\PDOStatement $stmt) {
+            foreach ($stmt as $cert) {
+                $cert['valid_from'] = (new \DateTime())
+                    ->setTimestamp($cert['valid_from'])
+                    ->format('l F jS, Y H:i:s e');
+                $cert['valid_to'] = (new \DateTime())
+                    ->setTimestamp($cert['valid_to'])
+                    ->format('l F jS, Y H:i:s e');
+
+                yield $cert;
+            }
+        });
+
+        $this->view->certificatesTable = (new CertificatesTable())->setData($conn->select($select));
+    }
+}
diff --git a/application/controllers/ChainController.php b/application/controllers/ChainController.php
new file mode 100644
index 0000000..870fa81
--- /dev/null
+++ b/application/controllers/ChainController.php
@@ -0,0 +1,83 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Exception\ConfigurationError;
+use Icinga\Module\X509\ChainDetails;
+use Icinga\Module\X509\Controller;
+use ipl\Html\Attribute;
+use ipl\Html\Html;
+use ipl\Html\HtmlDocument;
+use ipl\Sql;
+
+class ChainController extends Controller
+{
+    public function indexAction()
+    {
+        $id = $this->params->getRequired('id');
+
+        try {
+            $conn = $this->getDb();
+        } catch (ConfigurationError $_) {
+            $this->render('missing-resource', null, true);
+            return;
+        }
+
+        $chainSelect = (new Sql\Select())
+            ->from('x509_certificate_chain ch')
+            ->columns('*')
+            ->join('x509_target t', 't.id = ch.target_id')
+            ->where(['ch.id = ?' => $id]);
+
+        $chain = $conn->select($chainSelect)->fetch();
+
+        if ($chain === false) {
+            $this->httpNotFound($this->translate('Certificate not found.'));
+        }
+
+        $this->setTitle($this->translate('X.509 Certificate Chain'));
+
+        $ip = $chain['ip'];
+        $ipv4 = ltrim($ip, "\0");
+        if (strlen($ipv4) === 4) {
+            $ip = $ipv4;
+        }
+
+        $chainInfo = Html::tag('div');
+        $chainInfo->add(Html::tag('dl', [
+            Html::tag('dt', $this->translate('Host')),
+            Html::tag('dd', $chain['hostname']),
+            Html::tag('dt', $this->translate('IP')),
+            Html::tag('dd', inet_ntop($ip)),
+            Html::tag('dt', $this->translate('Port')),
+            Html::tag('dd', $chain['port'])
+        ]));
+
+        $valid = Html::tag('div', ['class' => 'cert-chain']);
+
+        if ($chain['valid'] === 'yes') {
+            $valid->getAttributes()->add('class', '-valid');
+            $valid->add(Html::tag('p', $this->translate('Certificate chain is valid.')));
+        } else {
+            $valid->getAttributes()->add('class', '-invalid');
+            $valid->add(Html::tag('p', sprintf(
+                $this->translate('Certificate chain is invalid: %s.'),
+                $chain['invalid_reason']
+            )));
+        }
+
+        $certsSelect = (new Sql\Select())
+            ->from('x509_certificate c')
+            ->columns('*')
+            ->join('x509_certificate_chain_link ccl', 'ccl.certificate_id = c.id')
+            ->join('x509_certificate_chain cc', 'cc.id = ccl.certificate_chain_id')
+            ->where(['cc.id = ?' => $id])
+            ->orderBy('ccl.order');
+
+        $this->view->chain = (new HtmlDocument())
+            ->add($chainInfo)
+            ->add($valid)
+            ->add((new ChainDetails())->setData($conn->select($certsSelect)));
+    }
+}
diff --git a/application/controllers/ConfigController.php b/application/controllers/ConfigController.php
new file mode 100644
index 0000000..c64cd5c
--- /dev/null
+++ b/application/controllers/ConfigController.php
@@ -0,0 +1,29 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Application\Config;
+use Icinga\Module\X509\Forms\Config\BackendConfigForm;
+use Icinga\Web\Controller;
+
+class ConfigController extends Controller
+{
+    public function init()
+    {
+        $this->assertPermission('config/modules');
+
+        parent::init();
+    }
+
+    public function backendAction()
+    {
+        $form = (new BackendConfigForm())
+            ->setIniConfig(Config::module('x509'));
+
+        $form->handleRequest();
+
+        $this->view->tabs = $this->Module()->getConfigTabs()->activate('backend');
+        $this->view->form = $form;
+    }
+}
diff --git a/application/controllers/DashboardController.php b/application/controllers/DashboardController.php
new file mode 100644
index 0000000..a48bb98
--- /dev/null
+++ b/application/controllers/DashboardController.php
@@ -0,0 +1,134 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Exception\ConfigurationError;
+use Icinga\Module\X509\CertificateUtils;
+use Icinga\Module\X509\Controller;
+use Icinga\Module\X509\Donut;
+use Icinga\Web\Url;
+use ipl\Html\Html;
+use ipl\Sql\Select;
+
+class DashboardController extends Controller
+{
+    public function indexAction()
+    {
+        $this->setTitle($this->translate('Certificate Dashboard'));
+
+        try {
+            $db = $this->getDb();
+        } catch (ConfigurationError $_) {
+            $this->render('missing-resource', null, true);
+            return;
+        }
+
+        $byCa = $db->select(
+            (new Select())
+                ->from('x509_certificate i')
+                ->columns(['i.subject', 'cnt' => 'COUNT(*)'])
+                ->join('x509_certificate c', ['c.issuer_hash = i.subject_hash', 'i.ca = ?' => 'yes'])
+                ->groupBy(['i.id'])
+                ->orderBy('cnt', SORT_DESC)
+                ->limit(5)
+        );
+
+        $this->view->byCa = (new Donut())
+            ->setHeading($this->translate('Certificates by CA'), 2)
+            ->setData($byCa)
+            ->setLabelCallback(function ($data) {
+                return Html::tag(
+                    'a',
+                    [
+                        'href' => Url::fromPath('x509/certificates', ['issuer' => $data['subject']])->getAbsoluteUrl()
+                    ],
+                    $data['subject']
+                );
+            });
+
+        $duration = $db->select(
+            (new Select())
+                ->from('x509_certificate')
+                ->columns([
+                    'duration' => 'valid_to - valid_from',
+                    'cnt' => 'COUNT(*)'
+                ])
+                ->where(['ca = ?' => 'no'])
+                ->groupBy(['duration'])
+                ->orderBy('cnt', SORT_DESC)
+                ->limit(5)
+        );
+
+        $this->view->duration = (new Donut())
+            ->setHeading($this->translate('Certificates by Duration'), 2)
+            ->setData($duration)
+            ->setLabelCallback(function ($data) {
+                return Html::tag(
+                    'a',
+                    [
+                        'href' => Url::fromPath(
+                            "x509/certificates?duration={$data['duration']}&ca=no"
+                        )->getAbsoluteUrl()
+                    ],
+                    CertificateUtils::duration($data['duration'])
+                );
+            });
+
+        $keyStrength = $db->select(
+            (new Select())
+                ->from('x509_certificate')
+                ->columns(['pubkey_algo', 'pubkey_bits', 'cnt' => 'COUNT(*)'])
+                ->groupBy(['pubkey_algo', 'pubkey_bits'])
+                ->orderBy('cnt', SORT_DESC)
+                ->limit(5)
+        );
+
+        $this->view->keyStrength = (new Donut())
+            ->setHeading($this->translate('Key Strength'), 2)
+            ->setData($keyStrength)
+            ->setLabelCallback(function ($data) {
+                return Html::tag(
+                    'a',
+                    [
+                        'href' => Url::fromPath(
+                            'x509/certificates',
+                            [
+                                'pubkey_algo' => $data['pubkey_algo'],
+                                'pubkey_bits' => $data['pubkey_bits']
+                            ]
+                        )->getAbsoluteUrl()
+                    ],
+                    "{$data['pubkey_algo']} {$data['pubkey_bits']} bits"
+                );
+            });
+
+        $sigAlgos = $db->select(
+            (new Select())
+                ->from('x509_certificate')
+                ->columns(['signature_algo', 'signature_hash_algo', 'cnt' => 'COUNT(*)'])
+                ->groupBy(['signature_algo', 'signature_hash_algo'])
+                ->orderBy('cnt', SORT_DESC)
+                ->limit(5)
+        );
+
+        $this->view->sigAlgos = (new Donut())
+            ->setHeading($this->translate('Signature Algorithms'), 2)
+            ->setData($sigAlgos)
+            ->setLabelCallback(function ($data) {
+                return Html::tag(
+                    'a',
+                    [
+                        'href' => Url::fromPath(
+                            'x509/certificates',
+                            [
+                                'signature_hash_algo' => $data['signature_hash_algo'],
+                                'signature_algo' => $data['signature_algo']
+                            ]
+                        )->getAbsoluteUrl()
+                    ],
+                    "{$data['signature_hash_algo']} with {$data['signature_algo']}"
+                );
+            });
+    }
+}
diff --git a/application/controllers/IconsController.php b/application/controllers/IconsController.php
new file mode 100644
index 0000000..422dcd5
--- /dev/null
+++ b/application/controllers/IconsController.php
@@ -0,0 +1,31 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Web\Controller;
+
+class IconsController extends Controller
+{
+    /**
+     * Disable layout rendering as this controller doesn't provide any html layouts
+     */
+    public function init()
+    {
+        $this->_helper->viewRenderer->setNoRender(true);
+        $this->_helper->layout()->disableLayout();
+    }
+
+    public function indexAction()
+    {
+        $file = realpath(
+            $this->Module()->getBaseDir() . '/public/font/icons.' . $this->params->get('q', 'svg')
+        );
+
+        if ($file === false) {
+            $this->httpNotFound('File does not exist');
+        }
+
+        readfile($file);
+    }
+}
diff --git a/application/controllers/JobsController.php b/application/controllers/JobsController.php
new file mode 100644
index 0000000..0df196b
--- /dev/null
+++ b/application/controllers/JobsController.php
@@ -0,0 +1,83 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Exception\NotFoundError;
+use Icinga\Module\X509\Forms\Config\JobConfigForm;
+use Icinga\Module\X509\JobsIniRepository;
+use Icinga\Web\Controller;
+use Icinga\Web\Url;
+
+class JobsController extends Controller
+{
+    /**
+     * List all jobs
+     */
+    public function indexAction()
+    {
+        $this->view->tabs = $this->Module()->getConfigTabs()->activate('jobs');
+
+        $repo = new JobsIniRepository();
+
+        $this->view->jobs = $repo->select(array('name'));
+    }
+
+    /**
+     * Create a job
+     */
+    public function newAction()
+    {
+        $form = $this->prepareForm()->add();
+
+        $form->handleRequest();
+
+        $this->renderForm($form, $this->translate('New Job'));
+    }
+
+    /**
+     * Update a job
+     */
+    public function updateAction()
+    {
+        $form = $this->prepareForm()->edit($this->params->getRequired('name'));
+
+        try {
+            $form->handleRequest();
+        } catch (NotFoundError $_) {
+            $this->httpNotFound($this->translate('Job not found'));
+        }
+
+        $this->renderForm($form, $this->translate('Update Job'));
+    }
+
+    /**
+     * Remove a job
+     */
+    public function removeAction()
+    {
+        $form = $this->prepareForm()->remove($this->params->getRequired('name'));
+
+        try {
+            $form->handleRequest();
+        } catch (NotFoundError $_) {
+            $this->httpNotFound($this->translate('Job not found'));
+        }
+
+        $this->renderForm($form, $this->translate('Remove Job'));
+    }
+
+    /**
+     * Assert config permission and return a prepared RepositoryForm
+     *
+     * @return  JobConfigForm
+     */
+    protected function prepareForm()
+    {
+        $this->assertPermission('config/x509');
+
+        return (new JobConfigForm())
+            ->setRepository(new JobsIniRepository())
+            ->setRedirectUrl(Url::fromPath('x509/jobs'));
+    }
+}
diff --git a/application/controllers/SniController.php b/application/controllers/SniController.php
new file mode 100644
index 0000000..21da41f
--- /dev/null
+++ b/application/controllers/SniController.php
@@ -0,0 +1,83 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Exception\NotFoundError;
+use Icinga\Module\X509\Forms\Config\SniConfigForm;
+use Icinga\Module\X509\SniIniRepository;
+use Icinga\Web\Controller;
+use Icinga\Web\Url;
+
+class SniController extends Controller
+{
+    /**
+     * List all maps
+     */
+    public function indexAction()
+    {
+        $this->view->tabs = $this->Module()->getConfigTabs()->activate('sni');
+
+        $repo = new SniIniRepository();
+
+        $this->view->sni = $repo->select(array('ip'));
+    }
+
+    /**
+     * Create a map
+     */
+    public function newAction()
+    {
+        $form = $this->prepareForm()->add();
+
+        $form->handleRequest();
+
+        $this->renderForm($form, $this->translate('New SNI Map'));
+    }
+
+    /**
+     * Update a map
+     */
+    public function updateAction()
+    {
+        $form = $this->prepareForm()->edit($this->params->getRequired('ip'));
+
+        try {
+            $form->handleRequest();
+        } catch (NotFoundError $_) {
+            $this->httpNotFound($this->translate('IP not found'));
+        }
+
+        $this->renderForm($form, $this->translate('Update SNI Map'));
+    }
+
+    /**
+     * Remove a map
+     */
+    public function removeAction()
+    {
+        $form = $this->prepareForm()->remove($this->params->getRequired('ip'));
+
+        try {
+            $form->handleRequest();
+        } catch (NotFoundError $_) {
+            $this->httpNotFound($this->translate('IP not found'));
+        }
+
+        $this->renderForm($form, $this->translate('Remove SNI Map'));
+    }
+
+    /**
+     * Assert config permission and return a prepared RepositoryForm
+     *
+     * @return  SniConfigForm
+     */
+    protected function prepareForm()
+    {
+        $this->assertPermission('config/x509');
+
+        return (new SniConfigForm())
+            ->setRepository(new SniIniRepository())
+            ->setRedirectUrl(Url::fromPath('x509/sni'));
+    }
+}
diff --git a/application/controllers/UsageController.php b/application/controllers/UsageController.php
new file mode 100644
index 0000000..287b979
--- /dev/null
+++ b/application/controllers/UsageController.php
@@ -0,0 +1,155 @@
+<?php
+// Icinga Web 2 X.509 Module | (c) 2018 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\X509\Controllers;
+
+use Icinga\Data\Filter\FilterExpression;
+use Icinga\Exception\ConfigurationError;
+use Icinga\Module\X509\Controller;
+use Icinga\Module\X509\FilterAdapter;
+use Icinga\Module\X509\Job;
+use Icinga\Module\X509\SortAdapter;
+use Icinga\Module\X509\SqlFilter;
+use Icinga\Module\X509\UsageTable;
+use ipl\Web\Control\PaginationControl;
+use ipl\Sql;
+use ipl\Web\Url;
+
+class UsageController extends Controller
+{
+    public function indexAction()
+    {
+        $this
+            ->initTabs()
+            ->setTitle($this->translate('Certificate Usage'));
+
+        try {
+            $conn = $this->getDb();
+        } catch (ConfigurationError $_) {
+            $this->render('missing-resource', null, true);
+            return;
+        }
+
+        $select = (new Sql\Select())
+            ->from('x509_target t')
+            ->columns('*')
+            ->join('x509_certificate_chain cc', 'cc.id = t.latest_certificate_chain_id')
+            ->join('x509_certificate_chain_link ccl', 'ccl.certificate_chain_id = cc.id')
+            ->join('x509_certificate c', 'c.id = ccl.certificate_id')
+            ->where(['ccl.order = ?' => 0]);
+
+        $sortAndFilterColumns = [
+            'hostname' => $this->translate('Hostname'),
+            'ip' => $this->translate('IP'),
+            'subject' => $this->translate('Certificate'),
+            'issuer' => $this->translate('Issuer'),
+            'version' => $this->translate('Version'),
+            'self_signed' => $this->translate('Is Self-Signed'),
+            'ca' => $this->translate('Is Certificate Authority'),
+            'trusted' => $this->translate('Is Trusted'),
+            'pubkey_algo' => $this->translate('Public Key Algorithm'),
+            'pubkey_bits' => $this->translate('Public Key Strength'),
+            'signature_algo' => $this->translate('Signature Algorithm'),
+            'signature_hash_algo' => $this->translate('Signature Hash Algorithm'),
+            'valid_from' => $this->translate('Valid From'),
+            'valid_to' => $this->translate('Valid To'),
+            'valid' => $this->translate('Chain Is Valid'),
+            'duration' => $this->translate('Duration'),
+            'expires' => $this->translate('Expiration')
+        ];
+
+        $this->view->paginator = new PaginationControl(new Sql\Cursor($conn, $select), Url::fromRequest());
+        $this->view->paginator->apply();
+
+        $this->setupSortControl(
+            $sortAndFilterColumns,
+            new SortAdapter($select, function ($field) {
+                if ($field === 'duration') {
+                    return '(valid_to - valid_from)';
+                } elseif ($field === 'expires') {
+                    return 'CASE WHEN UNIX_TIMESTAMP() > valid_to'
+                        . ' THEN 0 ELSE (valid_to - UNIX_TIMESTAMP()) / 86400 END';
+                }
+            })
+        );
+
+        $this->setupLimitControl();
+
+        $filterAdapter = new FilterAdapter();
+        $this->setupFilterControl(
+            $filterAdapter,
+            $sortAndFilterColumns,
+            ['hostname', 'subject'],
+            ['format']
+        );
+        SqlFilter::apply($select, $filterAdapter->getFilter(), function (FilterExpression $filter) {
+            switch ($filter->getColumn()) {
+                case 'ip':
+                    $value = $filter->getExpression();
+
+                    if (is_array($value)) {
+                        $value = array_map('Job::binary', $value);
+                    } else {
+                        $value = Job::binary($value);
+                    }
+
+                    return $filter->setExpression($value);
+                case 'issuer_hash':
+                    $value = $filter->getExpression();
+
+                    if (is_array($value)) {
+                        $value = array_map('hex2bin', $value);
+                    } else {
+                        $value = hex2bin($value);
+                    }
+
+                    return $filter->setExpression($value);
+                case 'duration':
+                    return $filter->setColumn('(valid_to - valid_from)');
+                case 'expires':
+                    return $filter->setColumn(
+                        'CASE WHEN UNIX_TIMESTAMP() > valid_to THEN 0 ELSE (valid_to - UNIX_TIMESTAMP()) / 86400 END'
+                    );
+                case 'valid_from':
+                case 'valid_to':
+                    $expr = $filter->getExpression();
+                    if (! is_numeric($expr)) {
+                        return $filter->setExpression(strtotime($expr));
+                    }
+
+                    // expression doesn't need changing
+                default:
+                    return false;
+            }
+        });
+
+        $formatQuery = clone $select;
+        $formatQuery->resetColumns()->columns([
+            'valid', 'hostname', 'ip', 'port', 'subject', 'issuer', 'version',
+            'self_signed', 'ca', 'trusted', 'pubkey_algo',  'pubkey_bits',
+            'signature_algo', 'signature_hash_algo', 'valid_from', 'valid_to'
+        ]);
+
+        $this->handleFormatRequest($conn, $formatQuery, function (\PDOStatement $stmt) {
+            foreach ($stmt as $usage) {
+                $usage['valid_from'] = (new \DateTime())
+                    ->setTimestamp($usage['valid_from'])
+                    ->format('l F jS, Y H:i:s e');
+                $usage['valid_to'] = (new \DateTime())
+                    ->setTimestamp($usage['valid_to'])
+                    ->format('l F jS, Y H:i:s e');
+
+                $ip = $usage['ip'];
+                $ipv4 = ltrim($ip, "\0");
+                if (strlen($ipv4) === 4) {
+                    $ip = $ipv4;
+                }
+                $usage['ip'] = inet_ntop($ip);
+
+                yield $usage;
+            }
+        });
+
+        $this->view->usageTable = (new UsageTable())->setData($conn->select($select));
+    }
+}