From 2563f21a299f059637847cc32e3aa31020309ac8 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Mon, 18 Mar 2019 04:33:30 +0000 Subject: [PATCH] /opt/nb/lib/postgres/host.sql --- etc/dbq/data.php | 2 +- lib/postgres/host.sql | 7 +++++-- 2 files changed, 6 insertions(+), 3 deletions(-) diff --git a/etc/dbq/data.php b/etc/dbq/data.php index 8aec03d4..b61f0dde 100644 --- a/etc/dbq/data.php +++ b/etc/dbq/data.php @@ -4,7 +4,7 @@ $DBQ['data'] = [ 'tables' => [ 'host_info_err' => [ 'sql' => "SELECT * FROM host_info WHERE 0=1" - ." OR key LIKE '%.exit_code' AND val<> '0'" + ." OR (key LIKE '%.exit_code' AND val<> '0')" , ] ], diff --git a/lib/postgres/host.sql b/lib/postgres/host.sql index d161990d..65e5323f 100644 --- a/lib/postgres/host.sql +++ b/lib/postgres/host.sql @@ -1,6 +1,9 @@ -DROP TABLE IF EXISTS host; +DROP VIEW IF EXISTS host; CREATE VIEW host AS - SELECT host,LEFT(CAST(MAX(updated) AS TEXT),19) as updated + SELECT host + ,LEFT(CAST(MAX(updated) AS TEXT),19) as updated + ,count(*) as infos + ,array_to_string(array_agg(DISTINCT CASE WHEN key LIKE 'git.%.branch' THEN val ELSE null END),' ') as branches FROM host_info GROUP BY host ; -- 2.47.3