added on local at 2026-07-13 13:59:36
| 1 | #!/usr/bin/perl | |
| 2 | #====================================================================== | |
| 3 | # DriftSense -- _weekly_digest.pl | |
| 4 | # | |
| 5 | # Emits a plain-text-and-HTML weekly summary via /usr/sbin/sendmail. | |
| 6 | # Runs Sunday 08:00 (or whenever cron says). | |
| 7 | # | |
| 8 | # Rollup: | |
| 9 | # * file changes total, by-status, by-monitor | |
| 10 | # * schema changes | |
| 11 | # * releases created (new NAMED_RELEASES rows) | |
| 12 | # * restores performed (RESTORE_LOG this week) | |
| 13 | # * pending auto-promotes (STABLE_PROMOTE_LOG decisions) | |
| 14 | # * blob integrity check status | |
| 15 | # * quick storage / dedup snapshot | |
| 16 | # | |
| 17 | # Config: reads `weekly_digest_recipient` and `alert_from_email` from | |
| 18 | # settings (falls back to root@localhost). | |
| 19 | #====================================================================== | |
| 20 | use strict; | |
| 21 | use warnings; | |
| 22 | use lib '/var/www/vhosts/3dshawn.com/site1'; | |
| 23 | use POSIX (); | |
| 24 | use MODS::Config; | |
| 25 | use MODS::DBConnect; | |
| 26 | ||
| 27 | $| = 1; | |
| 28 | my $now_ts = POSIX::strftime('%Y-%m-%d %H:%M:%S', localtime); | |
| 29 | my $cfg = MODS::Config->new; | |
| 30 | my $db = MODS::DBConnect->new; | |
| 31 | my $dbh = $db->db_connect or die "[$now_ts] cannot connect\n"; | |
| 32 | ||
| 33 | my $public_url = $cfg->settings('public_url') || 'https://driftsense.3dshawn.com'; | |
| 34 | my $to = $cfg->settings('weekly_digest_recipient') || $cfg->settings('alert_from_email') || 'root@localhost'; | |
| 35 | my $from = $cfg->settings('alert_from_email') || 'drift-sense@localhost'; | |
| 36 | ||
| 37 | # ---- Aggregates ---------------------------------------------------- | |
| 38 | sub scalar_q { my ($sql) = @_; my $r = $db->db_readwrite($dbh, $sql, __FILE__, __LINE__); return $r ? ($r->{n} // 0) : 0; } | |
| 39 | ||
| 40 | my $ch_total = scalar_q(q~SELECT COUNT(*) AS n FROM FILE_CHANGES WHERE date_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 41 | my $ch_added = scalar_q(q~SELECT COUNT(*) AS n FROM FILE_CHANGES WHERE status='added' AND date_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 42 | my $ch_modified = scalar_q(q~SELECT COUNT(*) AS n FROM FILE_CHANGES WHERE status='modified' AND date_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 43 | my $ch_deleted = scalar_q(q~SELECT COUNT(*) AS n FROM FILE_CHANGES WHERE status='deleted' AND date_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 44 | my $sc_total = scalar_q(q~SELECT COUNT(*) AS n FROM SCHEMA_CHANGE WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 45 | my $releases = scalar_q(q~SELECT COUNT(*) AS n FROM NAMED_RELEASES WHERE released_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 46 | my $restores = scalar_q(q~SELECT COUNT(*) AS n FROM RESTORE_LOG WHERE restored_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 47 | my $rest_ok = scalar_q(q~SELECT COUNT(*) AS n FROM RESTORE_LOG WHERE status='success' AND restored_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 48 | my $rest_fail = $restores - $rest_ok; | |
| 49 | ||
| 50 | # Auto-promote decisions | |
| 51 | my $ap_promoted = 0; my $ap_held = 0; | |
| 52 | { | |
| 53 | my $has_tab = $db->db_readwrite($dbh, q~ | |
| 54 | SELECT 1 AS ok FROM INFORMATION_SCHEMA.TABLES | |
| 55 | WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='STABLE_PROMOTE_LOG' | |
| 56 | ~, __FILE__, __LINE__); | |
| 57 | if ($has_tab && $has_tab->{ok}) { | |
| 58 | $ap_promoted = scalar_q(q~SELECT COUNT(*) AS n FROM STABLE_PROMOTE_LOG WHERE decision='promoted' AND decided_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 59 | $ap_held = scalar_q(q~SELECT COUNT(*) AS n FROM STABLE_PROMOTE_LOG WHERE decision LIKE 'held_%' AND decided_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 60 | } | |
| 61 | } | |
| 62 | ||
| 63 | # Integrity | |
| 64 | my $iv_ok = 0; my $iv_corrupt = 0; my $iv_missing = 0; | |
| 65 | { | |
| 66 | my $has_tab = $db->db_readwrite($dbh, q~ | |
| 67 | SELECT 1 AS ok FROM INFORMATION_SCHEMA.TABLES | |
| 68 | WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='BLOB_INTEGRITY_LOG' | |
| 69 | ~, __FILE__, __LINE__); | |
| 70 | if ($has_tab && $has_tab->{ok}) { | |
| 71 | $iv_ok = scalar_q(q~SELECT COUNT(*) AS n FROM BLOB_INTEGRITY_LOG WHERE status='ok' AND checked_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 72 | $iv_corrupt = scalar_q(q~SELECT COUNT(*) AS n FROM BLOB_INTEGRITY_LOG WHERE status='corrupt' AND checked_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 73 | $iv_missing = scalar_q(q~SELECT COUNT(*) AS n FROM BLOB_INTEGRITY_LOG WHERE status='missing' AND checked_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~); | |
| 74 | } | |
| 75 | } | |
| 76 | ||
| 77 | # Storage | |
| 78 | my $store_row = $db->db_readwrite($dbh, | |
| 79 | "SELECT COUNT(*) AS blobs, COALESCE(SUM(LENGTH(content_gz)),0) AS gz FROM BLOB_STORE", __FILE__, __LINE__); | |
| 80 | my $blob_ct = $store_row ? $store_row->{blobs} : 0; | |
| 81 | my $store_mb = $store_row ? sprintf('%.1f', $store_row->{gz} / 1024 / 1024) : 0; | |
| 82 | ||
| 83 | # Top monitors by activity | |
| 84 | my @top = $db->db_readwrite_multiple($dbh, q~ | |
| 85 | SELECT m.scan_name, COUNT(*) AS n | |
| 86 | FROM FILE_CHANGES fc | |
| 87 | LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id | |
| 88 | WHERE fc.date_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) | |
| 89 | GROUP BY fc.file_monitor_list_id | |
| 90 | ORDER BY n DESC LIMIT 5 | |
| 91 | ~, __FILE__, __LINE__); | |
| 92 | ||
| 93 | $db->db_disconnect($dbh); | |
| 94 | ||
| 95 | # ---- Compose message ---------------------------------------------- | |
| 96 | my $week_start = POSIX::strftime('%b %d', localtime(time - 7*86400)); | |
| 97 | my $week_end = POSIX::strftime('%b %d', localtime()); | |
| 98 | my $subject = "DriftSense weekly digest -- $week_start to $week_end"; | |
| 99 | ||
| 100 | my $top_text = ''; | |
| 101 | foreach my $t (@top) { | |
| 102 | $top_text .= sprintf(" %-30s %d changes\n", $t->{scan_name} // '?', $t->{n}); | |
| 103 | } | |
| 104 | ||
| 105 | my $body = <<"BODY"; | |
| 106 | DriftSense weekly digest | |
| 107 | $week_start - $week_end | |
| 108 | ||
| 109 | --- Activity --- | |
| 110 | File changes: $ch_total (added $ch_added / modified $ch_modified / deleted $ch_deleted) | |
| 111 | Schema changes: $sc_total | |
| 112 | Named releases cut: $releases | |
| 113 | Restores performed: $restores (success $rest_ok / failed $rest_fail) | |
| 114 | ||
| 115 | --- Auto-promote decisions --- | |
| 116 | Promoted to stable: $ap_promoted | |
| 117 | Held for review: $ap_held | |
| 118 | ||
| 119 | --- Blob integrity checks --- | |
| 120 | OK: $iv_ok | |
| 121 | Corrupt: $iv_corrupt | |
| 122 | Missing: $iv_missing | |
| 123 | ||
| 124 | --- Storage snapshot --- | |
| 125 | Total blobs: $blob_ct | |
| 126 | On-disk (gzip): $store_mb MB | |
| 127 | ||
| 128 | --- Top 5 active monitors --- | |
| 129 | $top_text | |
| 130 | ||
| 131 | --- Live console --- | |
| 132 | $public_url/dashboard.cgi | |
| 133 | ||
| 134 | Silent safety net for code + schema drift. | |
| 135 | BODY | |
| 136 | ||
| 137 | # ---- Sendmail ------------------------------------------------------ | |
| 138 | my $sendmail = '/usr/sbin/sendmail'; | |
| 139 | if (-x $sendmail) { | |
| 140 | open(my $M, "|-", $sendmail, '-t', '-i') or die "cannot pipe to sendmail: $!"; | |
| 141 | print $M "From: DriftSense <$from>\n"; | |
| 142 | print $M "To: $to\n"; | |
| 143 | print $M "Subject: $subject\n"; | |
| 144 | print $M "Content-Type: text/plain; charset=utf-8\n"; | |
| 145 | print $M "\n"; | |
| 146 | print $M $body; | |
| 147 | close $M; | |
| 148 | print "[$now_ts] weekly_digest sent to $to\n"; | |
| 149 | } else { | |
| 150 | warn "[$now_ts] no sendmail at $sendmail -- weekly digest NOT delivered\n"; | |
| 151 | print $body; | |
| 152 | } | |
| 153 | exit 0; |