From 6fb59bb7ba3e31e98afb6c484e207d3a688e1c85 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Tue, 29 Oct 2024 10:53:28 +0100 Subject: [PATCH] lib/postgres/host_report.sql --- lib/postgres/host_report.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 lib/postgres/host_report.sql diff --git a/lib/postgres/host_report.sql b/lib/postgres/host_report.sql new file mode 100644 index 00000000..c1dcd0a6 --- /dev/null +++ b/lib/postgres/host_report.sql @@ -0,0 +1,17 @@ +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 + ,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 + ,(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 + FROM host_info hi + GROUP BY host + ORDER BY git_updated DESC,backup_updated DESC,host +; +GRANT ALL PRIVILEGES ON host_report TO nico; +GRANT ALL PRIVILEGES ON host_report TO www; +GRANT ALL PRIVILEGES ON host_report TO root; -- 2.47.3