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

O Operator
Diff

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

added on local at 2026-07-12 01:04:47

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