Diff

/var/www/vhosts/3dshawn.com/site1/dashboard.cgi

modified on local at 2026-07-11 23:34:26

Added

+148

lines

Removed

-0

lines

Context

180

unchanged

Blobs

from d9e3ea458c4f
to b7658e07661d

Restore this content →

Unified diff

Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.

1	1	#!/usr/bin/perl
2	2	#======================================================================
3	3	# DriftSense -- /dashboard.cgi
4	4	#
5	5	# Landing page. Shows portfolio-wide health at a glance:
6	6	# * KPI tiles: file changes today \| schema changes 7d \| named releases \| drift alerts
7	7	# * Recent activity table (last 30 file + schema changes, merged, newest first)
8	8	# * Named releases strip (last 5 bookmarks)
9	9	# * Servers status strip
10	10	#======================================================================
11	11	use strict;
12	12	use warnings;
13	13	use CGI ();
14	14	use MODS::Config;
15	15	use MODS::DBConnect;
16	16	use MODS::Template;
17	17	use MODS::PageWrapper;
18	18	use MODS::Charts;
19	19
20	20	my $cfg = MODS::Config->new;
21	21	my $db = MODS::DBConnect->new;
22	22	my $tpl = MODS::Template->new;
23	23	my $wrap = MODS::PageWrapper->new;
24	24
25	25	# Headers
26	26	$\|=1;
27	27	print "Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n";
28	28
29	29	my $dbh = $db->db_connect
30	30	or die "DriftSense: cannot connect to storage DB. Check /etc/drift_sense/drift_sense.conf.\n";
31	31
32	32	# ----- KPI queries ----------------------------------------------------
33	33	my $file_today = $db->db_readwrite($dbh, q~
34	34	SELECT COUNT(*) AS n FROM FILE_CHANGES
35	35	WHERE date_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
36	36	~, __FILE__, __LINE__);
37	37
38	38	my $schema_7d = $db->db_readwrite($dbh, q~
39	39	SELECT COUNT(*) AS n FROM SCHEMA_CHANGE
40	40	WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
41	41	~, __FILE__, __LINE__);
42	42
43	43	my $releases_ct = $db->db_readwrite($dbh, q~
44	44	SELECT COUNT(*) AS n FROM NAMED_RELEASES
45	45	~, __FILE__, __LINE__);
46	46
47	47	my $drifted_ct = $db->db_readwrite($dbh, q~
48	48	SELECT COUNT(*) AS n FROM CONFIG_DRIFT_BASELINES WHERE is_drifted = 1
49	49	~, __FILE__, __LINE__);
50	50
51	51	# ----- Recent activity (merged file + schema changes, newest first) --
52	52	my @file_recent = $db->db_readwrite_multiple($dbh, q~
53	53	SELECT 'file' AS kind,
54	54	file_changes_id AS id,
55	55	file_name AS what,
56	56	status AS detail,
57	57	date_time AS ts,
58	58	UNIX_TIMESTAMP(date_time) AS ts_epoch,
59	59	server_id
60	60	FROM FILE_CHANGES
61	61	ORDER BY date_time DESC
62	62	LIMIT 20
63	63	~, __FILE__, __LINE__);
64	64	my @schema_recent = $db->db_readwrite_multiple($dbh, q~
65	65	SELECT 'schema' AS kind,
66	66	schema_change_id AS id,
67	67	CONCAT(database_name, '.', table_name) AS what,
68	68	changes AS detail,
69	69	change_datetime AS ts,
70	70	UNIX_TIMESTAMP(change_datetime) AS ts_epoch,
71	71	server_id
72	72	FROM SCHEMA_CHANGE
73	73	ORDER BY change_datetime DESC
74	74	LIMIT 20
75	75	~, __FILE__, __LINE__);
76	76	my @recent = sort { ($b->{ts} \|\| '') cmp ($a->{ts} \|\| '') } (@file_recent, @schema_recent);
77	77	@recent = splice(@recent, 0, 20) if @recent > 20;
78	78	foreach my $r (@recent) {
79	79	my $d = $r->{detail} // '';
80	80	$d =~ s/\s+/ /g;
81	81	$r->{detail} = length($d) > 90 ? substr($d, 0, 87) . '...' : $d;
82	82	$r->{what} = length($r->{what} // '') > 60 ? substr($r->{what}, 0, 57) . '...' : $r->{what};
83	83	$r->{diff_url} = ($r->{kind} eq 'file'
84	84	? "/diff.cgi?kind=file&id=$r->{id}"
85	85	: "/diff.cgi?kind=schema&id=$r->{id}");
86	86	$r->{ts} //= '';
87	87	$r->{ts_epoch} //= 0;
88	88	$r->{kind_pill} = ($r->{kind} eq 'file') ? 'pill-info' : 'pill-warn';
89	89	}
90	90
91	91	# ----- 90-day activity timeline (stacked bars) -----------------------
92	92	my $today_dow = 0;
93	93	my @file_by_day = $db->db_readwrite_multiple($dbh, q~
94	94	SELECT DATE_FORMAT(date_time, '%Y-%m-%d') AS d, COUNT(*) AS n
95	95	FROM FILE_CHANGES
96	96	WHERE date_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
97	97	GROUP BY d
98	98	~, __FILE__, __LINE__);
99	99	my @schema_by_day = $db->db_readwrite_multiple($dbh, q~
100	100	SELECT DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d, COUNT(*) AS n
101	101	FROM SCHEMA_CHANGE
102	102	WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 90 DAY)
103	103	GROUP BY d
104	104	~, __FILE__, __LINE__);
105	105	my (%file_map, %schema_map);
106	106	$file_map{$_->{d}} = $_->{n} for @file_by_day;
107	107	$schema_map{$_->{d}} = $_->{n} for @schema_by_day;
108	108
109	109	use POSIX ();
110	110	my (@days, @day_keys, @file_series, @schema_series);
111	111	my $today = time;
112	112	for (my $i = 89; $i >= 0; $i--) {
113	113	my $t = $today - ($i * 86400);
114	114	my $key = POSIX::strftime('%Y-%m-%d', localtime($t));
115	115	my $lbl = POSIX::strftime('%b %d', localtime($t));
116	116	push @days, $lbl;
117	117	push @day_keys, $key;
118	118	push @file_series, ($file_map{$key} \|\| 0);
119	119	push @schema_series, ($schema_map{$key} \|\| 0);
120	120	}
121	121	my $timeline_svg = MODS::Charts::stacked_bars(
122	122	days => \@days,
123	123	day_keys => \@day_keys,
124	124	series => { file => \@file_series, schema => \@schema_series },
125	125	width => 1080,
126	126	height => 180,
127	127	);
	128
	129	# ----- Per-site health (Feature 2) -----------------------------------
	130	# One row per active file monitor: total captured, active (non-deleted) files,
	131	# last change timestamp. Sparkline of last 30 days per site.
	132	my @per_site = $db->db_readwrite_multiple($dbh, q~
	133	SELECT m.file_monitor_list_id AS mid,
	134	m.scan_name,
	135	m.scan_path,
	136	s.server_name,
	137	s.kind AS server_kind,
	138	COUNT(fc.file_changes_id) AS captured_ct,
	139	COUNT(DISTINCT CASE WHEN fc.status != 'deleted'
	140	THEN fc.file_name END) AS active_files,
	141	SUM(CASE WHEN fc.date_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
	142	THEN 1 ELSE 0 END) AS changes_24h,
	143	MAX(UNIX_TIMESTAMP(fc.date_time)) AS last_change_epoch,
	144	DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS last_change_h
	145	FROM FILE_MONITOR_SETTINGS m
	146	LEFT JOIN SERVERS s ON s.server_id = m.server_id
	147	LEFT JOIN FILE_CHANGES fc ON fc.file_monitor_list_id = m.file_monitor_list_id
	148	WHERE m.status = 1
	149	GROUP BY m.file_monitor_list_id, m.scan_name, m.scan_path, s.server_name, s.kind
	150	ORDER BY captured_ct DESC
	151	~, __FILE__, __LINE__);
	152
	153	# 30-day sparkline per monitor (one query, bucket in Perl)
	154	my %spark_by_mid;
	155	{
	156	my @day_rows = $db->db_readwrite_multiple($dbh, q~
	157	SELECT file_monitor_list_id AS mid,
	158	DATE_FORMAT(date_time, '%Y-%m-%d') AS d,
	159	COUNT(*) AS n
	160	FROM FILE_CHANGES
	161	WHERE date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
	162	GROUP BY file_monitor_list_id, d
	163	~, __FILE__, __LINE__);
	164	my %by;
	165	foreach my $r (@day_rows) { $by{$r->{mid}}{$r->{d}} = $r->{n}; }
	166	my $now = time;
	167	my @keys;
	168	for (my $i = 29; $i >= 0; $i--) {
	169	push @keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400));
	170	}
	171	foreach my $r (@per_site) {
	172	my @series;
	173	for my $k (@keys) { push @series, ($by{$r->{mid}}{$k} \|\| 0); }
	174	$spark_by_mid{$r->{mid}} = \@series;
	175	}
	176	}
	177	foreach my $r (@per_site) {
	178	$r->{active_files} //= 0;
	179	$r->{captured_ct} //= 0;
	180	$r->{changes_24h} //= 0;
	181	$r->{last_change_epoch} //= 0;
	182	$r->{last_change_h} //= 'never';
	183	$r->{kind_pill} = ($r->{server_kind} // '') eq 'ssh_agent' ? 'pill-info' : 'pill-mute';
	184	my $s = $spark_by_mid{$r->{mid}};
	185	$r->{spark_svg} = $s ? MODS::Charts::sparkline(values => $s, width => 120, height => 28) : '';
	186	$r->{drilldown_url} = "/file_changes.cgi?monitor=" . $r->{mid};
	187	$r->{path_short} = length($r->{scan_path} // '') > 55
	188	? '...' . substr($r->{scan_path}, -52) : ($r->{scan_path} // '');
	189	}
	190
	191	# ----- Change-frequency ranking (Feature 5) --------------------------
	192	# Top-20 files by change count in the last 30 days -- the "live-config"
	193	# heatmap. Shows which files are being edited most, per-site.
	194	my @top_files = $db->db_readwrite_multiple($dbh, q~
	195	SELECT fc.file_name,
	196	m.scan_name,
	197	COUNT(*) AS change_ct,
	198	MAX(fc.file_changes_id) AS latest_id,
	199	MAX(UNIX_TIMESTAMP(fc.date_time)) AS latest_epoch,
	200	DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS latest_h
	201	FROM FILE_CHANGES fc
	202	LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
	203	WHERE fc.date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
	204	GROUP BY fc.file_name, m.scan_name
	205	ORDER BY change_ct DESC, latest_epoch DESC
	206	LIMIT 20
	207	~, __FILE__, __LINE__);
	208	foreach my $t (@top_files) {
	209	$t->{file_short} = length($t->{file_name} // '') > 70
	210	? '...' . substr($t->{file_name}, -67) : ($t->{file_name} // '');
	211	$t->{diff_url} = "/diff.cgi?kind=file&id=" . ($t->{latest_id} \|\| 0);
	212	$t->{scan_name} //= 'unknown';
	213	$t->{latest_epoch} //= 0;
	214	}
	215
	216	# ----- Cross-site dedup surfacing (Feature 3, dashboard preview) -----
	217	# Blobs that appear in FILE_CHANGES rows tied to >1 distinct monitor.
	218	my @shared_blobs = $db->db_readwrite_multiple($dbh, q~
	219	SELECT fc.blob_sha,
	220	COUNT(DISTINCT fc.file_monitor_list_id) AS site_ct,
	221	COUNT(DISTINCT fc.file_name) AS distinct_names,
	222	GROUP_CONCAT(DISTINCT m.scan_name ORDER BY m.scan_name SEPARATOR ' \| ') AS scans,
	223	MAX(fc.file_changes_id) AS example_id,
	224	bs.size_uncompressed,
	225	bs.ref_count
	226	FROM FILE_CHANGES fc
	227	JOIN BLOB_STORE bs ON bs.blob_sha = fc.blob_sha
	228	LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
	229	WHERE fc.blob_sha IS NOT NULL
	230	GROUP BY fc.blob_sha, bs.size_uncompressed, bs.ref_count
	231	HAVING site_ct > 1
	232	ORDER BY site_ct DESC, distinct_names DESC
	233	LIMIT 8
	234	~, __FILE__, __LINE__);
	235	foreach my $b (@shared_blobs) {
	236	$b->{sha_short} = substr($b->{blob_sha} // '', 0, 12);
	237	$b->{size_h} = _fmt_bytes($b->{size_uncompressed} \|\| 0);
	238	$b->{diff_url} = "/diff.cgi?kind=file&id=" . ($b->{example_id} \|\| 0);
	239	$b->{scans_h} = length($b->{scans} // '') > 90 ? substr($b->{scans}, 0, 87) . '...' : $b->{scans};
	240	}
	241
	242	sub _fmt_bytes {
	243	my $n = shift; $n //= 0;
	244	return "$n B" if $n < 1024;
	245	return sprintf('%.1f KB', $n/1024) if $n < 1024*1024;
	246	return sprintf('%.1f MB', $n/1024/1024);
	247	}
128	248
129	249	# ----- Named releases strip (last 5) ---------------------------------
130	250	my @releases = $db->db_readwrite_multiple($dbh, q~
131	251	SELECT named_release_id, name, description,
132	252	DATE_FORMAT(released_at, '%b %d, %Y') AS released_h,
133	253	UNIX_TIMESTAMP(released_at) AS released_epoch,
134	254	released_by
135	255	FROM NAMED_RELEASES
136	256	ORDER BY released_at DESC
137	257	LIMIT 5
138	258	~, __FILE__, __LINE__);
139	259	$_->{released_epoch} //= 0 for @releases;
140	260
141	261	# ----- Server status strip -------------------------------------------
142	262	my @servers = $db->db_readwrite_multiple($dbh, q~
143	263	SELECT server_id, server_name, hostname, role, status,
144	264	DATE_FORMAT(last_heartbeat_at, '%b %d %H:%i') AS last_heartbeat_h,
145	265	UNIX_TIMESTAMP(last_heartbeat_at) AS heartbeat_epoch
146	266	FROM SERVERS
147	267	ORDER BY (status='active') DESC, server_id ASC
148	268	LIMIT 8
149	269	~, __FILE__, __LINE__);
150	270	foreach my $s (@servers) {
151	271	$s->{status_pill} = $s->{status} eq 'active' ? 'pill-ok'
152	272	: $s->{status} eq 'offline' ? 'pill-bad'
153	273	: 'pill-mute';
154	274	$s->{last_heartbeat_h} //= 'never';
155	275	$s->{heartbeat_epoch} //= 0;
	276	}
	277
	278	# ----- Recent restores (last 5) --------------------------------------
	279	my @recent_restores = $db->db_readwrite_multiple($dbh, q~
	280	SELECT rl.restore_id, rl.target_file, rl.status,
	281	UNIX_TIMESTAMP(rl.restored_at) AS restored_epoch,
	282	DATE_FORMAT(rl.restored_at, '%b %d %H:%i') AS restored_h,
	283	LEFT(rl.error_message, 100) AS err_short,
	284	s.server_name
	285	FROM RESTORE_LOG rl
	286	LEFT JOIN SERVERS s ON s.server_id = rl.server_id
	287	ORDER BY rl.restore_id DESC
	288	LIMIT 5
	289	~, __FILE__, __LINE__);
	290	foreach my $r (@recent_restores) {
	291	$r->{status_pill} = $r->{status} eq 'success' ? 'pill-ok' :
	292	$r->{status} eq 'failed' ? 'pill-bad' : 'pill-mute';
	293	$r->{restored_epoch} //= 0;
	294	$r->{file_short} = length($r->{target_file} // '') > 55
	295	? '...' . substr($r->{target_file}, -52) : $r->{target_file};
156	296	}
157	297
158	298	$db->db_disconnect($dbh);
159	299
160	300	# ----- Render body ---------------------------------------------------
161	301	my $tvars = {
162	302	kpi_file_today => ($file_today && $file_today->{n}) \|\| 0,
163	303	kpi_schema_7d => ($schema_7d && $schema_7d->{n}) \|\| 0,
164	304	kpi_releases => ($releases_ct && $releases_ct->{n}) \|\| 0,
165	305	kpi_drifted => ($drifted_ct && $drifted_ct->{n}) \|\| 0,
166	306	timeline_svg => $timeline_svg,
	307	per_site => \@per_site,
	308	has_per_site => scalar(@per_site) ? 1 : 0,
	309	top_files => \@top_files,
	310	has_top_files => scalar(@top_files) ? 1 : 0,
	311	shared_blobs => \@shared_blobs,
	312	has_shared_blobs => scalar(@shared_blobs) ? 1 : 0,
	313	recent_restores => \@recent_restores,
	314	has_restores => scalar(@recent_restores) ? 1 : 0,
167	315	has_recent => scalar(@recent) ? 1 : 0,
168	316	recent => \@recent,
169	317	has_releases => scalar(@releases) ? 1 : 0,
170	318	releases => \@releases,
171	319	servers => \@servers,
172	320	};
173	321	my @body = $tpl->template('dashboard.html', $tvars);
174	322
175	323	$wrap->wrapper(
176	324	page_title => 'Dashboard',
177	325	page_key => 'dashboard',
178	326	body_html => join('', @body),
179	327	userinfo => { display_name => 'Operator' }, # single-user install; auth added later
180	328	);