From 3bb770d521972bb432699886dad003a60c88199a Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Fri, 18 Oct 2024 12:48:19 +0200 Subject: [PATCH] lib/postgres/host.sql --- lib/postgres/host.sql | 12 +++++++++--- 1 file changed, 9 insertions(+), 3 deletions(-) diff --git a/lib/postgres/host.sql b/lib/postgres/host.sql index 10e98e7f..3025dd09 100644 --- a/lib/postgres/host.sql +++ b/lib/postgres/host.sql @@ -6,10 +6,16 @@ CREATE VIEW host AS -- ,array_to_string(array_agg(DISTINCT CASE WHEN key LIKE 'git.%.name' THEN val ELSE null END),' ') as installed ,count(DISTINCT CASE WHEN key LIKE 'git.%.path' THEN val ELSE null END) as repos ,max(DISTINCT CASE WHEN key LIKE 'git.%.exit_code' THEN val ELSE null END) as exit + --,array_to_string(array_agg(DISTINCT CASE WHEN key = CONCAT('backup_servers.',host,'.time') THEN REGEXP_REPLACE(key,'^backup_servers\.(.*?)\.time$','\1') ELSE null END),' ') as backup + --,array_to_string(array_agg((SELECT DISTINCT bk.host FROM host_info bk WHERE bk.key = CONCAT('backup_servers.',hi.host,'.time'))),' ') as backuped ,array_to_string(array_agg(DISTINCT CASE WHEN key LIKE 'git.%.branch' THEN val ELSE null END),' ') as branches - ,array_to_string(array_agg(DISTINCT CASE WHEN key IN ( 'sys.nproc','sys.arch','sys.model','sys.os' ) THEN val ELSE null END),chr(10)) as product - -- NB 17.10.24 ,array_to_string(array_agg(DISTINCT CASE WHEN key IN ( 'sys.nproc','sys.arch','sys.width','sys.product' ) THEN val ELSE null END),chr(10)) as product - FROM host_info + /* + */ + ,array_to_string(array_agg(DISTINCT CASE WHEN key IN ( 'sys.nproc','sys.arch','sys.model','sys.os' ) + THEN CONCAT(REPLACE(key,'sys.',''),':',val) + ELSE null END + ),chr(10)) as product + FROM host_info hi GROUP BY host ORDER BY updated DESC,host ; -- 2.47.3