From 012c958f5cb08dcaf981f6abcaa595cb6eed922a Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Fri, 22 Nov 2024 22:18:19 +0100 Subject: [PATCH] lib/postgres/host_report.sql --- lib/postgres/host_report.sql | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/lib/postgres/host_report.sql b/lib/postgres/host_report.sql index 92998501..f2a94e0f 100644 --- a/lib/postgres/host_report.sql +++ b/lib/postgres/host_report.sql @@ -1,19 +1,19 @@ DROP VIEW IF EXISTS host_report; CREATE VIEW host_report AS SELECT host - ,TO_CHAR(MAX(DISTINCT CASE WHEN key LIKE 'git.%' THEN updated ELSE null END),'YYYY-MM-DD HH:MI:SS') as git_updated + ,COALESCE(TO_CHAR(MAX(DISTINCT CASE WHEN key LIKE 'git.%' THEN updated ELSE null END),'YYYY-MM-DD HH'),'') as git_updated ,COUNT(DISTINCT CASE WHEN key LIKE 'git.%.path' THEN val ELSE null END) as git_path ,MAX(DISTINCT CASE WHEN key LIKE 'git.%.exit_code' THEN val ELSE null END) as git_exit ,array_to_string(array_agg(DISTINCT CASE WHEN key LIKE 'git.%.branch' THEN val ELSE null END),' ') as git_branch - ,TO_CHAR((SELECT MAX(hb.updated) FROM host_info hb WHERE hb.key LIKE CONCAT('backup_servers.',hi.host,'.%')),'YYYY-MM-DD HH:MI:SS') as backup_updated + ,COALESCE(TO_CHAR((SELECT MAX(hb.updated) FROM host_info hb WHERE hb.key LIKE CONCAT('backup_servers.',hi.host,'.%')),'YYYY-MM-DD HH'),'') as backup_time ,(SELECT array_to_string(array_agg(DISTINCT hb.host),' ') FROM host_info hb WHERE hb.key LIKE CONCAT('backup_servers.',hi.host,'.time')) as backup_hosts ,(SELECT MAX(hb.val) FROM host_info hb WHERE hb.key LIKE CONCAT('backup_servers.',hi.host,'.files_transfer')) as backup_files - ,array_to_string(array_agg(DISTINCT CASE WHEN key LIKE 'backup-datas.%' THEN CONCAT(REPLACE(key,'backup-datas.',''),':',val,':',TO_CHAR(updated,'YYYY-MM-DD')) ELSE null END),' ') as datas_service + ,array_to_string(array_agg(DISTINCT CASE WHEN key LIKE 'backup-datas.%' THEN CONCAT(REPLACE(key,'backup-datas.',''),':',val,':',TO_CHAR(updated,'YYYY-MM-DD')) ELSE null END),' ') as backup_datas FROM host_info hi GROUP BY host -ORDER BY backup_updated DESC,git_updated DESC,host +ORDER BY backup_time DESC,git_updated DESC,host ; GRANT ALL PRIVILEGES ON host_report TO nico; GRANT ALL PRIVILEGES ON host_report TO icolas; -- 2.47.3