Diff

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

modified on local at 2026-07-12 01:06:13

Added

lines

Removed

-1

lines

Context

273

unchanged

Blobs

from 25fec6115db3
to 02f155cf6060

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 -- /sites.cgi
4	4	#
5	5	# Grid view of every monitored site. Portfolio-style overview: click
6	6	# a card to jump into that site's focused page. Includes filter,
7	7	# sparklines, KPIs, activity dots, and lets sysadmin see fleet
8	8	# health at a glance.
9	9	#======================================================================
10	10	use strict;
11	11	use warnings;
12	12	use CGI ();
13	13	use POSIX ();
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 $cgi = CGI->new;
21	21	my $db = MODS::DBConnect->new;
22	22	my $tpl = MODS::Template->new;
23	23	$\|=1;
24	24
25	25	my $q = $cgi->param('q') \|\| '';
26	26
27	27	my $dbh = $db->db_connect or die "DB connect failed\n";
28	28
29	29	# ---- Sites list (every active monitor + parent server) --------------
30	30	my $where = "m.status = 1";
31	31	if (length $q) {
32	32	my $q_like = $dbh->quote('%' . $q . '%');
33	33	$where .= " AND (m.scan_name LIKE $q_like OR m.scan_path LIKE $q_like OR s.server_name LIKE $q_like)";
34	34	}
35	35	my @sites = $db->db_readwrite_multiple($dbh, qq~
36	36	SELECT m.file_monitor_list_id AS mid, m.scan_name, m.scan_path,
37	37	m.retention_days,
38	38	s.server_id, s.server_name, s.kind AS server_kind,
39	39	s.ssh_host, s.ssh_user
40	40	FROM FILE_MONITOR_SETTINGS m
41	41	LEFT JOIN SERVERS s ON s.server_id = m.server_id
42	42	WHERE $where
43	43	ORDER BY (s.kind = 'ssh_agent') DESC, s.server_id ASC, m.file_monitor_list_id ASC
44	44	~, __FILE__, __LINE__);
45	45
46	46	# ---- Per-site KPIs (single grouped query so we don't hammer DB) -----
47	47	my %kpi_by_mid;
48	48	foreach my $r ($db->db_readwrite_multiple($dbh, q~
49	49	SELECT file_monitor_list_id AS mid,
50	50	SUM(CASE WHEN date_time >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS c_24h,
51	51	SUM(CASE WHEN date_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS c_7d,
52	52	SUM(CASE WHEN date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS c_30d,
53	53	COUNT(DISTINCT CASE WHEN status != 'deleted' THEN file_name END) AS active_files,
54	54	SUM(CASE WHEN status = 'added' THEN 1 ELSE 0 END) AS n_added,
55	55	SUM(CASE WHEN status = 'modified' THEN 1 ELSE 0 END) AS n_modified,
56	56	SUM(CASE WHEN status = 'deleted' THEN 1 ELSE 0 END) AS n_deleted,
57	57	SUM(CASE WHEN is_ts_only = 1 THEN 1 ELSE 0 END) AS n_ts_only,
58	58	COUNT(*) AS c_all,
59	59	MAX(UNIX_TIMESTAMP(date_time)) AS last_epoch,
60	60	MIN(UNIX_TIMESTAMP(date_time)) AS first_epoch
61	61	FROM FILE_CHANGES
62	62	GROUP BY file_monitor_list_id
63	63	~, __FILE__, __LINE__)) {
64	64	$kpi_by_mid{$r->{mid}} = $r;
65	65	}
66	66
67	67	# Storage used: sum of BLOB_STORE sizes for blobs referenced by each monitor
68	68	my %storage_by_mid;
69	69	foreach my $r ($db->db_readwrite_multiple($dbh, q~
70	70	SELECT fc.file_monitor_list_id AS mid,
71	71	COALESCE(SUM(DISTINCT bs.size_compressed), 0) AS bytes_gz,
72	72	COALESCE(SUM(DISTINCT bs.size_uncompressed), 0) AS bytes_raw,
73	73	COUNT(DISTINCT bs.blob_sha) AS unique_blobs
74	74	FROM FILE_CHANGES fc
75	75	JOIN BLOB_STORE bs ON bs.blob_sha = fc.blob_sha
76	76	WHERE fc.blob_sha IS NOT NULL
77	77	GROUP BY fc.file_monitor_list_id
78	78	~, __FILE__, __LINE__)) {
79	79	$storage_by_mid{$r->{mid}} = $r;
80	80	}
81	81
82	82	# Named releases scoped to each monitor OR its parent server OR portfolio-wide
83	83	my %rel_by_mid;
84	84	foreach my $r ($db->db_readwrite_multiple($dbh, q~
85	85	SELECT scope_monitor_id AS mid,
86	86	SUM(CASE WHEN tier = 'stable' THEN 1 ELSE 0 END) AS stable_ct,
87	87	SUM(CASE WHEN tier = 'working' THEN 1 ELSE 0 END) AS working_ct,
88	88	COUNT(*) AS total_ct
89	89	FROM NAMED_RELEASES
90	90	WHERE scope_monitor_id IS NOT NULL
91	91	GROUP BY scope_monitor_id
92	92	~, __FILE__, __LINE__)) {
93	93	$rel_by_mid{$r->{mid}} = $r;
94	94	}
95	95	# Portfolio-wide release count (applies to all sites)
96	96	my $portfolio_releases = $db->db_readwrite($dbh, q~
97	97	SELECT COUNT(*) AS n FROM NAMED_RELEASES
98	98	WHERE scope_monitor_id IS NULL AND scope_server_id IS NULL
99	99	~, __FILE__, __LINE__);
100	100	my $portfolio_rel_ct = ($portfolio_releases && $portfolio_releases->{n}) \|\| 0;
101	101
102	102	# Explicit pins per monitor (via file_changes -> monitor lookup)
103	103	my %pins_by_mid;
104	104	foreach my $r ($db->db_readwrite_multiple($dbh, q~
105	105	SELECT fc.file_monitor_list_id AS mid, COUNT(*) AS pin_ct
106	106	FROM NAMED_RELEASE_PINS p
107	107	JOIN FILE_CHANGES fc ON fc.file_changes_id = p.file_changes_id
108	108	GROUP BY fc.file_monitor_list_id
109	109	~, __FILE__, __LINE__)) {
110	110	$pins_by_mid{$r->{mid}} = $r->{pin_ct};
111	111	}
112	112
113	113	# Compile-check pass/fail per monitor
114	114	my %compile_by_mid;
115	115	foreach my $r ($db->db_readwrite_multiple($dbh, q~
116	116	SELECT fc.file_monitor_list_id AS mid,
117	117	SUM(CASE WHEN bs.compile_status = 'ok' THEN 1 ELSE 0 END) AS n_ok,
118	118	SUM(CASE WHEN bs.compile_status = 'error' THEN 1 ELSE 0 END) AS n_err,
119	119	COUNT(DISTINCT bs.blob_sha) AS checked
120	120	FROM FILE_CHANGES fc
121	121	JOIN BLOB_STORE bs ON bs.blob_sha = fc.blob_sha
122	122	WHERE bs.compile_status IN ('ok', 'error')
123	123	GROUP BY fc.file_monitor_list_id
124	124	~, __FILE__, __LINE__)) {
125	125	$compile_by_mid{$r->{mid}} = $r;
126	126	}
127	127
128	128	# Pin drift (unnotified) per monitor
129	129	my %drift_by_mid;
130	130	foreach my $r ($db->db_readwrite_multiple($dbh, q~
131	131	SELECT fc.file_monitor_list_id AS mid, COUNT(*) AS drift_ct
132	132	FROM PIN_DRIFT_LOG d
133	133	JOIN FILE_CHANGES fc ON fc.file_changes_id = d.file_changes_id
134	134	WHERE d.notified = 0
135	135	GROUP BY fc.file_monitor_list_id
136	136	~, __FILE__, __LINE__)) {
137	137	$drift_by_mid{$r->{mid}} = $r->{drift_ct};
138	138	}
139	139
140	140	# ---- 30-day sparkline per site (one query, bucketed) ----------------
141	141	my %spark_by_mid;
142	142	foreach my $r ($db->db_readwrite_multiple($dbh, q~
143	143	SELECT file_monitor_list_id AS mid,
144	144	DATE_FORMAT(date_time, '%Y-%m-%d') AS d,
145	145	COUNT(*) AS n
146	146	FROM FILE_CHANGES
147	147	WHERE date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
148	148	GROUP BY file_monitor_list_id, d
149	149	~, __FILE__, __LINE__)) {
150	150	$spark_by_mid{$r->{mid}}{$r->{d}} = $r->{n};
151	151	}
152	152	my @day_keys;
153	153	{
154	154	my $now = time;
155	155	for (my $i = 29; $i >= 0; $i--) {
156	156	push @day_keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400));
157	157	}
158	158	}
159	159
160	160	$db->db_disconnect($dbh);
161	161
162	162	# ---- Enrich each site with display fields --------------------------
163	163	foreach my $s (@sites) {
164	164	my $kpi = $kpi_by_mid{$s->{mid}} \|\| {};
165	165	$s->{c_24h} = $kpi->{c_24h} \|\| 0;
166	166	$s->{c_7d} = $kpi->{c_7d} \|\| 0;
167	167	$s->{c_30d} = $kpi->{c_30d} \|\| 0;
168	168	$s->{active_files}= $kpi->{active_files} \|\| 0;
169	169	$s->{n_added} = $kpi->{n_added} \|\| 0;
170	170	$s->{n_modified} = $kpi->{n_modified} \|\| 0;
171	171	$s->{n_deleted} = $kpi->{n_deleted} \|\| 0;
172	172	$s->{n_ts_only} = $kpi->{n_ts_only} \|\| 0;
173	173	$s->{c_all} = $kpi->{c_all} \|\| 0;
174	174	$s->{last_epoch} = $kpi->{last_epoch} \|\| 0;
175	175	$s->{first_epoch} = $kpi->{first_epoch} \|\| 0;
176	176	$s->{last_h} = $s->{last_epoch}
177		? POSIX::strftime('%b %d %H:%i', localtime($s->{last_epoch}))
	177	? POSIX::strftime('%b %d %H:%M', localtime($s->{last_epoch}))
178	178	: 'never';
179	179
180	180	# Storage
181	181	my $stor = $storage_by_mid{$s->{mid}} \|\| {};
182	182	$s->{storage_raw_h} = _fmt_bytes($stor->{bytes_raw} \|\| 0);
183	183	$s->{storage_gz_h} = _fmt_bytes($stor->{bytes_gz} \|\| 0);
184	184	$s->{unique_blobs} = $stor->{unique_blobs} \|\| 0;
185	185	$s->{dedup_ratio} = ($stor->{bytes_gz} && $stor->{bytes_raw})
186	186	? sprintf('%.1fx', $stor->{bytes_raw} / $stor->{bytes_gz})
187	187	: '-';
188	188
189	189	# Releases scoped here + portfolio
190	190	my $rel = $rel_by_mid{$s->{mid}} \|\| {};
191	191	$s->{rel_stable} = $rel->{stable_ct} \|\| 0;
192	192	$s->{rel_working} = $rel->{working_ct} \|\| 0;
193	193	$s->{rel_scoped} = $rel->{total_ct} \|\| 0;
194	194	$s->{rel_portfolio} = $portfolio_rel_ct;
195	195	$s->{rel_total} = $s->{rel_scoped} + $s->{rel_portfolio};
196	196	$s->{pin_ct} = $pins_by_mid{$s->{mid}} \|\| 0;
197	197
198	198	# Compile status
199	199	my $cc = $compile_by_mid{$s->{mid}} \|\| {};
200	200	$s->{cc_ok} = $cc->{n_ok} \|\| 0;
201	201	$s->{cc_err} = $cc->{n_err} \|\| 0;
202	202	$s->{cc_checked} = $cc->{checked} \|\| 0;
203	203	$s->{has_compile_data}= $s->{cc_checked} ? 1 : 0;
204	204
205	205	# Drift
206	206	$s->{drift_ct} = $drift_by_mid{$s->{mid}} \|\| 0;
207	207	$s->{has_drift} = $s->{drift_ct} ? 1 : 0;
208	208
209	209	# Retention
210	210	$s->{retention_h} = defined($s->{retention_days}) && $s->{retention_days}
211	211	? "$s->{retention_days} days"
212	212	: 'global';
213	213
214	214	# Sparkline
215	215	my $bucket = $spark_by_mid{$s->{mid}} // {};
216	216	my @series = map { $bucket->{$_} \|\| 0 } @day_keys;
217	217	$s->{spark_svg} = MODS::Charts::sparkline(values => \@series, width => 200, height => 30);
218	218
219	219	# Display bits
220	220	$s->{initials} = _site_initials($s->{scan_name});
221	221	$s->{activity_dot}= $s->{c_24h} > 50 ? 'hot'
222	222	: $s->{c_24h} > 5 ? 'warm'
223	223	: $s->{c_24h} > 0 ? 'live' : 'quiet';
224	224	$s->{activity_label} = $s->{activity_dot} eq 'hot' ? 'HOT'
225	225	: $s->{activity_dot} eq 'warm' ? 'ACTIVE'
226	226	: $s->{activity_dot} eq 'live' ? 'LIVE'
227	227	: 'QUIET';
228	228	$s->{server_name} //= 'local';
229	229	$s->{server_kind} //= 'local';
230	230	$s->{kind_pill} = $s->{server_kind} eq 'ssh_agent' ? 'pill-info' : 'pill-mute';
231	231	$s->{scan_path_short}= length($s->{scan_path} // '') > 55
232	232	? '...' . substr($s->{scan_path}, -52) : $s->{scan_path};
233	233	$s->{site_url} = "/site.cgi?mid=$s->{mid}";
234	234	$s->{changes_url} = "/file_changes.cgi?monitor=$s->{mid}";
235	235	$s->{pin_url} = "/pin_hotspots.cgi?monitor=$s->{mid}";
236	236	}
237	237
238	238	sub _fmt_bytes {
239	239	my $n = shift; $n //= 0;
240	240	return "$n B" if $n < 1024;
241	241	return sprintf('%.1f KB', $n/1024) if $n < 1024*1024;
242	242	return sprintf('%.1f MB', $n/1024/1024) if $n < 102410241024;
243	243	return sprintf('%.1f GB', $n/1024/1024/1024);
244	244	}
245	245
246	246	my $tvars = {
247	247	sites => \@sites,
248	248	has_sites => scalar(@sites) ? 1 : 0,
249	249	total => scalar @sites,
250	250	q => $q,
251	251	total_24h => scalar(grep { $_->{c_24h} > 0 } @sites),
252	252	hot_sites => scalar(grep { $_->{c_24h} > 50 } @sites),
253	253	quiet_sites => scalar(grep { $_->{c_24h} == 0 } @sites),
254	254	};
255	255
256	256	print "Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n";
257	257	my @body = $tpl->template('sites.html', $tvars);
258	258	MODS::PageWrapper->new->wrapper(
259	259	page_title => 'Sites',
260	260	page_key => 'sites',
261	261	body_html => join('', @body),
262	262	userinfo => { display_name => 'Operator' },
263	263	);
264	264
265	265	#---------------------------------------------------------------------
266	266	sub _site_initials {
267	267	my $s = shift; $s //= '';
268	268	my $core = $s; $core =~ s/\s\(.$//;
269	269	my @tok = grep { length } split /[\s_\-\.\/]+/, $core;
270	270	my $i = '';
271	271	if (@tok >= 2) { $i = uc(substr($tok[0],0,1) . substr($tok[1],0,1)); }
272	272	elsif (@tok) { $i = uc(substr($tok[0],0,2)); }
273	273	return length($i) ? $i : '??';
274	274	}