| 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 | | use MODS::RangePicker; |
| 20 | 19 | |
| 21 | 20 | my $cfg = MODS::Config->new; |
| 22 | 21 | my $db = MODS::DBConnect->new; |
| 23 | 22 | my $tpl = MODS::Template->new; |
| 24 | 23 | my $wrap = MODS::PageWrapper->new; |
| 25 | 24 | |
| 26 | 25 | # Headers |
| 27 | 26 | $|=1; |
| 28 | 27 | print "Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n"; |
| 29 | 28 | |
| 30 | 29 | my $dbh = $db->db_connect |
| 31 | 30 | or die "DriftSense: cannot connect to storage DB. Check /etc/drift_sense/drift_sense.conf.\n"; |
| 32 | | |
| 33 | | # ----- Unified range: BOTH file and schema tiles use the same window ---- |
| 34 | | # RangePicker honors the ds_range localStorage persistence + defaults to |
| 35 | | # viewer-local "today" via the ds_tzo cookie. |
| 36 | | my $cgi_obj = CGI->new; |
| 37 | | my $r_pick = MODS::RangePicker::parse($cgi_obj); |
| 38 | | my $range_lower = $r_pick->{sql_lower_epoch}; |
| 39 | | my $range_upper = $r_pick->{sql_upper_epoch}; |
| 40 | | my $range_span = $range_upper - $range_lower; |
| 41 | | $range_span = 86400 if $range_span < 60; # sanity guard |
| 42 | | my $prev_upper = $range_lower; |
| 43 | | my $prev_lower = $range_lower - $range_span; |
| 44 | | # For linking to file_changes/schema_changes from the tile |
| 45 | | my $tr_qs = "tr=" . ($r_pick->{active_token} || 'today'); |
| 46 | | my $R = { |
| 47 | | sql_since => "FROM_UNIXTIME($range_lower)", |
| 48 | | prev_sql => "FROM_UNIXTIME($prev_lower) AND date_time < FROM_UNIXTIME($prev_upper)", |
| 49 | | prev_sql_schema |
| 50 | | => "FROM_UNIXTIME($prev_lower) AND change_datetime < FROM_UNIXTIME($prev_upper)", |
| 51 | | label => $r_pick->{range_label} || 'Today', |
| 52 | | fc_qs => $tr_qs, |
| 53 | | picker_html => MODS::RangePicker::picker_html( |
| 54 | | action => '/dashboard.cgi', |
| 55 | | range => $r_pick, |
| 56 | | hidden => {}, |
| 57 | | ), |
| 58 | | }; |
| 59 | 31 | |
| 60 | | # Per-status counts in the current range (baseline hidden). |
| 61 | | my $fc_by_status = $db->db_readwrite_multiple($dbh, qq~ |
| 62 | | SELECT status, COUNT(*) AS n |
| 63 | | FROM FILE_CHANGES |
| 64 | | WHERE date_time >= $R->{sql_since} |
| 32 | # ----- KPI queries ---------------------------------------------------- |
| 33 | # Exclude is_baseline=1 rows -- those are initial-inventory captures |
| 34 | # from a fresh monitor scan, not real "changes" the operator should see. |
| 35 | my $file_today = $db->db_readwrite($dbh, q~ |
| 36 | SELECT COUNT(*) AS n FROM FILE_CHANGES |
| 37 | WHERE date_time >= DATE_SUB(NOW(), INTERVAL 1 DAY) |
| 65 | 38 | AND is_baseline = 0 |
| 66 | | GROUP BY status |
| 67 | 39 | ~, __FILE__, __LINE__); |
| 68 | | my %fc = (modified => 0, added => 0, deleted => 0); |
| 69 | | foreach my $r (@$fc_by_status) { |
| 70 | | my $s = lc($r->{status} // ''); |
| 71 | | $fc{$s} = $r->{n} if exists $fc{$s}; |
| 72 | | } |
| 73 | | my $fc_all = $fc{modified} + $fc{added} + $fc{deleted}; |
| 74 | | |
| 75 | | my $file_today = { n => $fc_all }; |
| 76 | | my $file_prev = $db->db_readwrite($dbh, qq~ |
| 40 | my $file_prev = $db->db_readwrite($dbh, q~ |
| 77 | 41 | SELECT COUNT(*) AS n FROM FILE_CHANGES |
| 78 | | WHERE date_time >= $R->{prev_sql} |
| 42 | WHERE date_time >= DATE_SUB(NOW(), INTERVAL 2 DAY) |
| 43 | AND date_time < DATE_SUB(NOW(), INTERVAL 1 DAY) |
| 79 | 44 | AND is_baseline = 0 |
| 80 | 45 | ~, __FILE__, __LINE__); |
| 81 | 46 | |
| 82 | | my $schema_7d = $db->db_readwrite($dbh, qq~ |
| 47 | my $schema_7d = $db->db_readwrite($dbh, q~ |
| 83 | 48 | SELECT COUNT(*) AS n FROM SCHEMA_CHANGE |
| 84 | | WHERE change_datetime >= $R->{sql_since} |
| 85 | | AND is_baseline = 0 |
| 86 | | ~, __FILE__, __LINE__); |
| 87 | | |
| 88 | | # Per-status counts for the schema tile sub-grid (same window as file). |
| 89 | | my $sc_by_status = $db->db_readwrite_multiple($dbh, qq~ |
| 90 | | SELECT CASE |
| 91 | | WHEN changes LIKE 'first snapshot%' THEN 'added' |
| 92 | | WHEN changes LIKE 'table dropped%' THEN 'deleted' |
| 93 | | ELSE 'modified' |
| 94 | | END AS kind, |
| 95 | | COUNT(*) AS n |
| 96 | | FROM SCHEMA_CHANGE |
| 97 | | WHERE change_datetime >= $R->{sql_since} |
| 98 | | AND is_baseline = 0 |
| 99 | | GROUP BY kind |
| 49 | WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY) |
| 100 | 50 | ~, __FILE__, __LINE__); |
| 101 | | my %sc = (modified => 0, added => 0, deleted => 0); |
| 102 | | foreach my $row (@$sc_by_status) { |
| 103 | | my $k = lc($row->{kind} // ''); |
| 104 | | $sc{$k} = $row->{n} if exists $sc{$k}; |
| 105 | | } |
| 106 | | my $sc_all = $sc{modified} + $sc{added} + $sc{deleted}; |
| 107 | | my $schema_prev = $db->db_readwrite($dbh, qq~ |
| 51 | my $schema_prev = $db->db_readwrite($dbh, q~ |
| 108 | 52 | SELECT COUNT(*) AS n FROM SCHEMA_CHANGE |
| 109 | | WHERE change_datetime >= $R->{prev_sql_schema} |
| 110 | | AND is_baseline = 0 |
| 53 | WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 14 DAY) |
| 54 | AND change_datetime < DATE_SUB(NOW(), INTERVAL 7 DAY) |
| 111 | 55 | ~, __FILE__, __LINE__); |
| 112 | 56 | |
| 113 | 57 | my $releases_ct = $db->db_readwrite($dbh, q~ |
| 114 | 58 | SELECT COUNT(*) AS n FROM NAMED_RELEASES |
| 115 | 59 | ~, __FILE__, __LINE__); |
| 116 | 60 | |
| 117 | 61 | my $drifted_ct = $db->db_readwrite($dbh, q~ |
| 118 | 62 | SELECT COUNT(*) AS n FROM CONFIG_DRIFT_BASELINES WHERE is_drifted = 1 |
| 119 | 63 | ~, __FILE__, __LINE__); |
| 120 | 64 | |
| 121 | 65 | # ----- Recent activity (merged file + schema changes, newest first) -- |
| 122 | 66 | my @file_recent = $db->db_readwrite_multiple($dbh, q~ |
| 123 | 67 | SELECT 'file' AS kind, |
| 124 | 68 | file_changes_id AS id, |
| 125 | 69 | file_name AS what, |
| 126 | 70 | status AS detail, |
| 127 | 71 | date_time AS ts, |
| 128 | 72 | UNIX_TIMESTAMP(date_time) AS ts_epoch, |
| 129 | 73 | server_id |
| 130 | 74 | FROM FILE_CHANGES |
| 131 | 75 | ORDER BY date_time DESC |
| 132 | 76 | LIMIT 20 |
| 133 | 77 | ~, __FILE__, __LINE__); |
| 134 | 78 | my @schema_recent = $db->db_readwrite_multiple($dbh, q~ |
| 135 | 79 | SELECT 'schema' AS kind, |
| 136 | 80 | schema_change_id AS id, |
| 137 | 81 | CONCAT(database_name, '.', table_name) AS what, |
| 138 | 82 | changes AS detail, |
| 139 | 83 | change_datetime AS ts, |
| 140 | 84 | UNIX_TIMESTAMP(change_datetime) AS ts_epoch, |
| 141 | 85 | server_id |
| 142 | 86 | FROM SCHEMA_CHANGE |
| 143 | 87 | ORDER BY change_datetime DESC |
| 144 | 88 | LIMIT 20 |
| 145 | 89 | ~, __FILE__, __LINE__); |
| 146 | 90 | my @recent = sort { ($b->{ts} || '') cmp ($a->{ts} || '') } (@file_recent, @schema_recent); |
| 147 | 91 | @recent = splice(@recent, 0, 20) if @recent > 20; |
| 148 | 92 | foreach my $r (@recent) { |
| 149 | 93 | my $d = $r->{detail} // ''; |
| 150 | 94 | $d =~ s/\s+/ /g; |
| 151 | 95 | $r->{detail} = length($d) > 90 ? substr($d, 0, 87) . '...' : $d; |
| 152 | 96 | $r->{what} = length($r->{what} // '') > 60 ? substr($r->{what}, 0, 57) . '...' : $r->{what}; |
| 153 | 97 | $r->{diff_url} = ($r->{kind} eq 'file' |
| 154 | 98 | ? "/diff.cgi?kind=file&id=$r->{id}" |
| 155 | 99 | : "/diff.cgi?kind=schema&id=$r->{id}"); |
| 156 | 100 | $r->{ts} //= ''; |
| 157 | 101 | $r->{ts_epoch} //= 0; |
| 158 | 102 | $r->{kind_pill} = ($r->{kind} eq 'file') ? 'pill-info' : 'pill-warn'; |
| 159 | 103 | } |
| 160 | 104 | |
| 161 | 105 | # ----- 90-day activity timeline (stacked bars) ----------------------- |
| 162 | 106 | my $today_dow = 0; |
| 163 | 107 | my @file_by_day = $db->db_readwrite_multiple($dbh, q~ |
| 164 | 108 | SELECT DATE_FORMAT(date_time, '%Y-%m-%d') AS d, COUNT(*) AS n |
| 165 | 109 | FROM FILE_CHANGES |
| 166 | 110 | WHERE date_time >= DATE_SUB(NOW(), INTERVAL 90 DAY) |
| 167 | 111 | GROUP BY d |
| 168 | 112 | ~, __FILE__, __LINE__); |
| 169 | 113 | my @schema_by_day = $db->db_readwrite_multiple($dbh, q~ |
| 170 | 114 | SELECT DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d, COUNT(*) AS n |
| 171 | 115 | FROM SCHEMA_CHANGE |
| 172 | 116 | WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 90 DAY) |
| 173 | 117 | GROUP BY d |
| 174 | 118 | ~, __FILE__, __LINE__); |
| 175 | 119 | my (%file_map, %schema_map); |
| 176 | 120 | $file_map{$_->{d}} = $_->{n} for @file_by_day; |
| 177 | 121 | $schema_map{$_->{d}} = $_->{n} for @schema_by_day; |
| 178 | 122 | |
| 179 | 123 | use POSIX (); |
| 180 | 124 | my (@days, @day_keys, @file_series, @schema_series); |
| 181 | 125 | my $today = time; |
| 182 | 126 | for (my $i = 89; $i >= 0; $i--) { |
| 183 | 127 | my $t = $today - ($i * 86400); |
| 184 | 128 | my $key = POSIX::strftime('%Y-%m-%d', localtime($t)); |
| 185 | 129 | my $lbl = POSIX::strftime('%b %d', localtime($t)); |
| 186 | 130 | push @days, $lbl; |
| 187 | 131 | push @day_keys, $key; |
| 188 | 132 | push @file_series, ($file_map{$key} || 0); |
| 189 | 133 | push @schema_series, ($schema_map{$key} || 0); |
| 190 | 134 | } |
| 191 | 135 | my $timeline_svg = MODS::Charts::stacked_bars( |
| 192 | 136 | days => \@days, |
| 193 | 137 | day_keys => \@day_keys, |
| 194 | 138 | series => { file => \@file_series, schema => \@schema_series }, |
| 195 | 139 | width => 1080, |
| 196 | 140 | height => 180, |
| 197 | 141 | ); |
| 198 | 142 | |
| 199 | 143 | # ----- Per-site health (Feature 2) ----------------------------------- |
| 200 | 144 | # One row per active file monitor: total captured, active (non-deleted) files, |
| 201 | 145 | # last change timestamp. Sparkline of last 30 days per site. |
| 202 | 146 | my @per_site = $db->db_readwrite_multiple($dbh, q~ |
| 203 | 147 | SELECT m.file_monitor_list_id AS mid, |
| 204 | 148 | m.scan_name, |
| 205 | 149 | m.scan_path, |
| 206 | 150 | s.server_name, |
| 207 | 151 | s.kind AS server_kind, |
| 208 | 152 | COUNT(fc.file_changes_id) AS captured_ct, |
| 209 | 153 | COUNT(DISTINCT CASE WHEN fc.status != 'deleted' |
| 210 | 154 | THEN fc.file_name END) AS active_files, |
| 211 | 155 | SUM(CASE WHEN fc.date_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR) |
| 212 | 156 | THEN 1 ELSE 0 END) AS changes_24h, |
| 213 | 157 | MAX(UNIX_TIMESTAMP(fc.date_time)) AS last_change_epoch, |
| 214 | 158 | DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS last_change_h |
| 215 | 159 | FROM FILE_MONITOR_SETTINGS m |
| 216 | 160 | LEFT JOIN SERVERS s ON s.server_id = m.server_id |
| 217 | 161 | LEFT JOIN FILE_CHANGES fc ON fc.file_monitor_list_id = m.file_monitor_list_id |
| 218 | 162 | WHERE m.status = 1 |
| 219 | 163 | GROUP BY m.file_monitor_list_id, m.scan_name, m.scan_path, s.server_name, s.kind |
| 220 | 164 | ORDER BY captured_ct DESC |
| 221 | 165 | ~, __FILE__, __LINE__); |
| 222 | 166 | |
| 223 | 167 | # 30-day sparkline per monitor (one query, bucket in Perl) |
| 224 | 168 | my %spark_by_mid; |
| 225 | 169 | { |
| 226 | 170 | my @day_rows = $db->db_readwrite_multiple($dbh, q~ |
| 227 | 171 | SELECT file_monitor_list_id AS mid, |
| 228 | 172 | DATE_FORMAT(date_time, '%Y-%m-%d') AS d, |
| 229 | 173 | COUNT(*) AS n |
| 230 | 174 | FROM FILE_CHANGES |
| 231 | 175 | WHERE date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) |
| 232 | 176 | GROUP BY file_monitor_list_id, d |
| 233 | 177 | ~, __FILE__, __LINE__); |
| 234 | 178 | my %by; |
| 235 | 179 | foreach my $r (@day_rows) { $by{$r->{mid}}{$r->{d}} = $r->{n}; } |
| 236 | 180 | my $now = time; |
| 237 | 181 | my @keys; |
| 238 | 182 | for (my $i = 29; $i >= 0; $i--) { |
| 239 | 183 | push @keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400)); |
| 240 | 184 | } |
| 241 | 185 | foreach my $r (@per_site) { |
| 242 | 186 | my @series; |
| 243 | 187 | for my $k (@keys) { push @series, ($by{$r->{mid}}{$k} || 0); } |
| 244 | 188 | $spark_by_mid{$r->{mid}} = \@series; |
| 245 | 189 | } |
| 246 | 190 | } |
| 247 | 191 | foreach my $r (@per_site) { |
| 248 | 192 | $r->{active_files} //= 0; |
| 249 | 193 | $r->{captured_ct} //= 0; |
| 250 | 194 | $r->{changes_24h} //= 0; |
| 251 | 195 | $r->{last_change_epoch} //= 0; |
| 252 | 196 | $r->{last_change_h} //= 'never'; |
| 253 | 197 | $r->{kind_pill} = ($r->{server_kind} // '') eq 'ssh_agent' ? 'pill-info' : 'pill-mute'; |
| 254 | 198 | my $s = $spark_by_mid{$r->{mid}}; |
| 255 | 199 | $r->{spark_svg} = $s ? MODS::Charts::sparkline(values => $s, width => 120, height => 28) : ''; |
| 256 | 200 | $r->{drilldown_url} = "/file_changes.cgi?monitor=" . $r->{mid}; |
| 257 | 201 | $r->{path_short} = length($r->{scan_path} // '') > 55 |
| 258 | 202 | ? '...' . substr($r->{scan_path}, -52) : ($r->{scan_path} // ''); |
| 259 | 203 | } |
| 260 | 204 | |
| 261 | 205 | # ----- Change-frequency ranking (Feature 5) -------------------------- |
| 262 | 206 | # Top-20 files by change count in the last 30 days -- the "live-config" |
| 263 | 207 | # heatmap. Shows which files are being edited most, per-site. |
| 264 | 208 | my @top_files = $db->db_readwrite_multiple($dbh, q~ |
| 265 | 209 | SELECT fc.file_name, |
| 266 | 210 | m.scan_name, |
| 267 | 211 | COUNT(*) AS change_ct, |
| 268 | 212 | MAX(fc.file_changes_id) AS latest_id, |
| 269 | 213 | MAX(UNIX_TIMESTAMP(fc.date_time)) AS latest_epoch, |
| 270 | 214 | DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS latest_h |
| 271 | 215 | FROM FILE_CHANGES fc |
| 272 | 216 | LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id |
| 273 | 217 | WHERE fc.date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) |
| 274 | 218 | GROUP BY fc.file_name, m.scan_name |
| 275 | 219 | ORDER BY change_ct DESC, latest_epoch DESC |
| 276 | 220 | LIMIT 20 |
| 277 | 221 | ~, __FILE__, __LINE__); |
| 278 | 222 | foreach my $t (@top_files) { |
| 279 | 223 | $t->{file_short} = length($t->{file_name} // '') > 70 |
| 280 | 224 | ? '...' . substr($t->{file_name}, -67) : ($t->{file_name} // ''); |
| 281 | 225 | $t->{diff_url} = "/diff.cgi?kind=file&id=" . ($t->{latest_id} || 0); |
| 282 | 226 | $t->{scan_name} //= 'unknown'; |
| 283 | 227 | $t->{latest_epoch} //= 0; |
| 284 | 228 | } |
| 285 | 229 | |
| 286 | 230 | # ----- Cross-site dedup surfacing (Feature 3, dashboard preview) ----- |
| 287 | 231 | # Blobs that appear in FILE_CHANGES rows tied to >1 distinct monitor. |
| 288 | 232 | my @shared_blobs = $db->db_readwrite_multiple($dbh, q~ |
| 289 | 233 | SELECT fc.blob_sha, |
| 290 | 234 | COUNT(DISTINCT fc.file_monitor_list_id) AS site_ct, |
| 291 | 235 | COUNT(DISTINCT fc.file_name) AS distinct_names, |
| 292 | 236 | GROUP_CONCAT(DISTINCT m.scan_name ORDER BY m.scan_name SEPARATOR ' | ') AS scans, |
| 293 | 237 | MAX(fc.file_changes_id) AS example_id, |
| 294 | 238 | bs.size_uncompressed, |
| 295 | 239 | bs.ref_count |
| 296 | 240 | FROM FILE_CHANGES fc |
| 297 | 241 | JOIN BLOB_STORE bs ON bs.blob_sha = fc.blob_sha |
| 298 | 242 | LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id |
| 299 | 243 | WHERE fc.blob_sha IS NOT NULL |
| 300 | 244 | GROUP BY fc.blob_sha, bs.size_uncompressed, bs.ref_count |
| 301 | 245 | HAVING site_ct > 1 |
| 302 | 246 | ORDER BY site_ct DESC, distinct_names DESC |
| 303 | 247 | LIMIT 8 |
| 304 | 248 | ~, __FILE__, __LINE__); |
| 305 | 249 | foreach my $b (@shared_blobs) { |
| 306 | 250 | $b->{sha_short} = substr($b->{blob_sha} // '', 0, 12); |
| 307 | 251 | $b->{size_h} = _fmt_bytes($b->{size_uncompressed} || 0); |
| 308 | 252 | $b->{diff_url} = "/diff.cgi?kind=file&id=" . ($b->{example_id} || 0); |
| 309 | 253 | $b->{scans_h} = length($b->{scans} // '') > 90 ? substr($b->{scans}, 0, 87) . '...' : $b->{scans}; |
| 310 | 254 | } |
| 311 | 255 | |
| 312 | 256 | sub _fmt_bytes { |
| 313 | 257 | my $n = shift; $n //= 0; |
| 314 | 258 | return "$n B" if $n < 1024; |
| 315 | 259 | return sprintf('%.1f KB', $n/1024) if $n < 1024*1024; |
| 316 | 260 | return sprintf('%.1f MB', $n/1024/1024); |
| 317 | 261 | } |
| 318 | 262 | |
| 319 | 263 | # ----- Named releases strip (last 5) --------------------------------- |
| 320 | 264 | my @releases = $db->db_readwrite_multiple($dbh, q~ |
| 321 | 265 | SELECT named_release_id, name, description, |
| 322 | 266 | DATE_FORMAT(released_at, '%b %d, %Y') AS released_h, |
| 323 | 267 | UNIX_TIMESTAMP(released_at) AS released_epoch, |
| 324 | 268 | released_by |
| 325 | 269 | FROM NAMED_RELEASES |
| 326 | 270 | ORDER BY released_at DESC |
| 327 | 271 | LIMIT 5 |
| 328 | 272 | ~, __FILE__, __LINE__); |
| 329 | 273 | $_->{released_epoch} //= 0 for @releases; |
| 330 | 274 | |
| 331 | 275 | # ----- Server status strip ------------------------------------------- |
| 332 | 276 | my @servers = $db->db_readwrite_multiple($dbh, q~ |
| 333 | 277 | SELECT server_id, server_name, hostname, role, status, |
| 334 | 278 | DATE_FORMAT(last_heartbeat_at, '%b %d %H:%i') AS last_heartbeat_h, |
| 335 | 279 | UNIX_TIMESTAMP(last_heartbeat_at) AS heartbeat_epoch |
| 336 | 280 | FROM SERVERS |
| 337 | 281 | ORDER BY (status='active') DESC, server_id ASC |
| 338 | 282 | LIMIT 8 |
| 339 | 283 | ~, __FILE__, __LINE__); |
| 340 | 284 | foreach my $s (@servers) { |
| 341 | 285 | $s->{status_pill} = $s->{status} eq 'active' ? 'pill-ok' |
| 342 | 286 | : $s->{status} eq 'offline' ? 'pill-bad' |
| 343 | 287 | : 'pill-mute'; |
| 344 | 288 | $s->{last_heartbeat_h} //= 'never'; |
| 345 | 289 | $s->{heartbeat_epoch} //= 0; |
| 346 | 290 | } |
| 347 | 291 | |
| 348 | 292 | # ----- Stable versions tile (Wave 5 F2 / user ask) ------------------ |
| 349 | 293 | my @stable_versions = $db->db_readwrite_multiple($dbh, q~ |
| 350 | 294 | SELECT nr.named_release_id AS id, nr.name, nr.version_label, nr.tier, |
| 351 | 295 | nr.description, |
| 352 | 296 | DATE_FORMAT(nr.released_at, '%b %d, %Y') AS released_h, |
| 353 | 297 | UNIX_TIMESTAMP(nr.released_at) AS released_epoch, |
| 354 | 298 | (SELECT COUNT(*) FROM NAMED_RELEASE_PINS p WHERE p.named_release_id = nr.named_release_id) AS pin_ct |
| 355 | 299 | FROM NAMED_RELEASES nr |
| 356 | 300 | WHERE nr.tier IN ('stable', 'working') |
| 357 | 301 | ORDER BY (nr.tier = 'stable') DESC, nr.released_at DESC |
| 358 | 302 | LIMIT 6 |
| 359 | 303 | ~, __FILE__, __LINE__); |
| 360 | 304 | foreach my $r (@stable_versions) { |
| 361 | 305 | $r->{label} = $r->{version_label} || $r->{name}; |
| 362 | 306 | $r->{tier_pill} = $r->{tier} eq 'stable' ? 'pill-ok' : 'pill-info'; |
| 363 | 307 | $r->{tier_lbl} = uc($r->{tier}); |
| 364 | 308 | $r->{restore_url} = "/restore_release.cgi?release_id=$r->{id}"; |
| 365 | 309 | $r->{released_epoch} //= 0; |
| 366 | 310 | $r->{pin_ct} //= 0; |
| 367 | 311 | } |
| 368 | 312 | |
| 369 | 313 | # ----- Pin-drift tile (Wave 5 feature 1) ---------------------------- |
| 370 | 314 | my $drift_kpi = $db->db_readwrite($dbh, q~ |
| 371 | 315 | SELECT COUNT(*) AS drifted_ct, |
| 372 | 316 | COUNT(DISTINCT named_release_id) AS releases_affected |
| 373 | 317 | FROM PIN_DRIFT_LOG |
| 374 | 318 | WHERE notified = 0 |
| 375 | 319 | AND detected_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) |
| 376 | 320 | ~, __FILE__, __LINE__); |
| 377 | 321 | |
| 378 | 322 | my @drift_rows = $db->db_readwrite_multiple($dbh, q~ |
| 379 | 323 | SELECT d.file_name, d.pinned_sha, d.current_sha, |
| 380 | 324 | r.name AS release_name, r.named_release_id AS rid, |
| 381 | 325 | UNIX_TIMESTAMP(d.detected_at) AS detected_epoch, |
| 382 | 326 | DATE_FORMAT(d.detected_at, '%b %d %H:%i') AS detected_h |
| 383 | 327 | FROM PIN_DRIFT_LOG d |
| 384 | 328 | LEFT JOIN NAMED_RELEASES r ON r.named_release_id = d.named_release_id |
| 385 | 329 | WHERE d.notified = 0 |
| 386 | 330 | ORDER BY d.drift_id DESC |
| 387 | 331 | LIMIT 5 |
| 388 | 332 | ~, __FILE__, __LINE__); |
| 389 | 333 | foreach my $d (@drift_rows) { |
| 390 | 334 | $d->{file_short} = length($d->{file_name} // '') > 55 |
| 391 | 335 | ? '...' . substr($d->{file_name}, -52) : $d->{file_name}; |
| 392 | 336 | $d->{pinned_short} = substr($d->{pinned_sha} // '', 0, 12); |
| 393 | 337 | $d->{current_short} = substr($d->{current_sha} // '', 0, 12); |
| 394 | 338 | $d->{restore_url} = "/restore_release.cgi?release_id=" . ($d->{rid} || 0); |
| 395 | 339 | $d->{detected_epoch} //= 0; |
| 396 | 340 | } |
| 397 | 341 | |
| 398 | 342 | # ----- Recent restores (last 5) -------------------------------------- |
| 399 | 343 | my @recent_restores = $db->db_readwrite_multiple($dbh, q~ |
| 400 | 344 | SELECT rl.restore_id, rl.target_file, rl.status, |
| 401 | 345 | UNIX_TIMESTAMP(rl.restored_at) AS restored_epoch, |
| 402 | 346 | DATE_FORMAT(rl.restored_at, '%b %d %H:%i') AS restored_h, |
| 403 | 347 | LEFT(rl.error_message, 100) AS err_short, |
| 404 | 348 | s.server_name |
| 405 | 349 | FROM RESTORE_LOG rl |
| 406 | 350 | LEFT JOIN SERVERS s ON s.server_id = rl.server_id |
| 407 | 351 | ORDER BY rl.restore_id DESC |
| 408 | 352 | LIMIT 5 |
| 409 | 353 | ~, __FILE__, __LINE__); |
| 410 | 354 | foreach my $r (@recent_restores) { |
| 411 | 355 | $r->{status_pill} = $r->{status} eq 'success' ? 'pill-ok' : |
| 412 | 356 | $r->{status} eq 'failed' ? 'pill-bad' : 'pill-mute'; |
| 413 | 357 | $r->{restored_epoch} //= 0; |
| 414 | 358 | $r->{file_short} = length($r->{target_file} // '') > 55 |
| 415 | 359 | ? '...' . substr($r->{target_file}, -52) : $r->{target_file}; |
| 416 | 360 | } |
| 417 | 361 | |
| 418 | 362 | $db->db_disconnect($dbh); |
| 419 | 363 | |
| 420 | 364 | sub _trend { |
| 421 | | my ($cur, $prev, $noun) = @_; |
| 422 | | $cur //= 0; $prev //= 0; $noun //= 'changes'; |
| 423 | | # Always include the noun so a bare zero reads as "0 schema changes" |
| 424 | | # not just an em-dash the operator has to decode. |
| 425 | | if ($prev == 0 && $cur == 0) { return ('flat', "$noun"); } |
| 426 | | if ($prev == 0) { return ('up', "↑ $noun"); } |
| 365 | my ($cur, $prev) = @_; |
| 366 | $cur //= 0; $prev //= 0; |
| 367 | if ($prev == 0 && $cur == 0) { return ('flat', '—'); } |
| 368 | if ($prev == 0) { return ('up', '↑ new'); } |
| 427 | 369 | my $delta = ($cur - $prev) / $prev * 100; |
| 428 | 370 | my $sign = $delta > 5 ? 'up' : $delta < -5 ? 'down' : 'flat'; |
| 429 | 371 | my $arrow = $sign eq 'up' ? '↑' : $sign eq 'down' ? '↓' : '•'; |
| 430 | 372 | return ($sign, sprintf('%s %+.0f%%', $arrow, $delta)); |
| 431 | 373 | } |
| 432 | | my ($ft_class, $ft_txt) = _trend(($file_today && $file_today->{n}), ($file_prev && $file_prev->{n}), 'file changes'); |
| 433 | | my ($st_class, $st_txt) = _trend(($schema_7d && $schema_7d->{n}), ($schema_prev && $schema_prev->{n}), 'schema changes'); |
| 374 | my ($ft_class, $ft_txt) = _trend(($file_today && $file_today->{n}), ($file_prev && $file_prev->{n})); |
| 375 | my ($st_class, $st_txt) = _trend(($schema_7d && $schema_7d->{n}), ($schema_prev && $schema_prev->{n})); |
| 434 | 376 | |
| 435 | 377 | # ----- Render body --------------------------------------------------- |
| 436 | 378 | my $tvars = { |
| 437 | 379 | kpi_file_today => ($file_today && $file_today->{n}) || 0, |
| 438 | 380 | kpi_file_prev => ($file_prev && $file_prev->{n}) || 0, |
| 439 | 381 | kpi_file_class => $ft_class, kpi_file_txt => $ft_txt, |
| 440 | | kpi_fc_modified => $fc{modified}, |
| 441 | | kpi_fc_added => $fc{added}, |
| 442 | | kpi_fc_deleted => $fc{deleted}, |
| 443 | | kpi_fc_all => $fc_all, |
| 444 | | kpi_fc_range => $R->{fc_qs} || 'tr=today', |
| 445 | | fc_mod_hot => $fc{modified} > 0 ? 1 : 0, |
| 446 | | fc_add_hot => $fc{added} > 0 ? 1 : 0, |
| 447 | | fc_del_hot => $fc{deleted} > 0 ? 1 : 0, |
| 448 | | fc_all_hot => $fc_all > 0 ? 1 : 0, |
| 449 | | kpi_sc_modified => $sc{modified}, |
| 450 | | kpi_sc_added => $sc{added}, |
| 451 | | kpi_sc_deleted => $sc{deleted}, |
| 452 | | kpi_sc_all => $sc_all, |
| 453 | | sc_mod_hot => $sc{modified} > 0 ? 1 : 0, |
| 454 | | sc_add_hot => $sc{added} > 0 ? 1 : 0, |
| 455 | | sc_del_hot => $sc{deleted} > 0 ? 1 : 0, |
| 456 | | sc_all_hot => $sc_all > 0 ? 1 : 0, |
| 457 | | range_label => $R->{label}, |
| 458 | | range_picker => $R->{picker_html}, |
| 459 | | range_lower_epoch=> $range_lower, |
| 460 | 382 | kpi_schema_7d => ($schema_7d && $schema_7d->{n}) || 0, |
| 461 | 383 | kpi_schema_prev => ($schema_prev && $schema_prev->{n}) || 0, |
| 462 | 384 | kpi_schema_class => $st_class, kpi_schema_txt => $st_txt, |
| 463 | 385 | kpi_releases => ($releases_ct && $releases_ct->{n}) || 0, |
| 464 | 386 | kpi_drifted => ($drifted_ct && $drifted_ct->{n}) || 0, |
| 465 | 387 | timeline_svg => $timeline_svg, |
| 466 | 388 | per_site => \@per_site, |
| 467 | 389 | has_per_site => scalar(@per_site) ? 1 : 0, |
| 468 | 390 | top_files => \@top_files, |
| 469 | 391 | has_top_files => scalar(@top_files) ? 1 : 0, |
| 470 | 392 | shared_blobs => \@shared_blobs, |
| 471 | 393 | has_shared_blobs => scalar(@shared_blobs) ? 1 : 0, |
| 472 | 394 | recent_restores => \@recent_restores, |
| 473 | 395 | has_restores => scalar(@recent_restores) ? 1 : 0, |
| 474 | 396 | drifted_ct => ($drift_kpi && $drift_kpi->{drifted_ct}) || 0, |
| 475 | 397 | releases_affected=> ($drift_kpi && $drift_kpi->{releases_affected}) || 0, |
| 476 | 398 | drift_rows => \@drift_rows, |
| 477 | 399 | has_drifts => scalar(@drift_rows) ? 1 : 0, |
| 478 | 400 | stable_versions => \@stable_versions, |
| 479 | 401 | has_stables => scalar(@stable_versions) ? 1 : 0, |
| 480 | 402 | has_recent => scalar(@recent) ? 1 : 0, |
| 481 | 403 | recent => \@recent, |
| 482 | 404 | has_releases => scalar(@releases) ? 1 : 0, |
| 483 | 405 | releases => \@releases, |
| 484 | 406 | servers => \@servers, |
| 485 | 407 | }; |
| 486 | 408 | my @body = $tpl->template('dashboard.html', $tvars); |
| 487 | 409 | |
| 488 | 410 | $wrap->wrapper( |
| 489 | 411 | page_title => 'Dashboard', |
| 490 | 412 | page_key => 'dashboard', |
| 491 | 413 | body_html => join('', @body), |
| 492 | 414 | userinfo => { display_name => 'Operator' }, # single-user install; auth added later |
| 493 | 415 | ); |