#!/usr/bin/env python3 r""" RESUME Q2 после паузы (ch12 был недоступен). Задача 0: дозалить ГОТОВЫЙ 2210PIGOTTOILUAP01_09152026.bak (hash -> upload -> del своего файла). BACKUP НЕ повторяем — файл уже на сервере и верифицирован ранее (VERIFYONLY ok). Задачи 1-14: остаток TASKS из run_q2.py в том же порядке ценности. Режим оператора: без скачивания к нам; sha256 -> q2_checksums.tsv; сверка на ch12. Чужие файлы (Q2-A/Q2-C) после upload НЕ удаляются; свой временный .bak (Q2-B) — удаляется. """ import re import subprocess import sys import time from datetime import datetime from pathlib import Path SQLCMD = [ "docker", "run", "--rm", "--network", "host", "mcr.microsoft.com/mssql/server:2022-latest", "/opt/mssql-tools18/bin/sqlcmd", "-S", "50.21.183.111", "-U", "irely", "-P", "iRely486", "-C", "-d", "master", "-s", "|", "-W", ] CH12 = "https://ch12.hostserviceapp.com" BAKDIR = r"D:\irelyinstall\backup" SQLDATA = r"D:\i21SQLData" SI = Path("/root/ir-assessment/redteam/irelydata/schema_inventory") CHECKSUMS = SI / "q2_checksums.tsv" # отложенная задача: готовый .bak PIGOTT (hash в фоне мог не досчитаться — переснимем) PENDING_BAK = BAKDIR + r"\2210PIGOTTOILUAP01_09152026.bak" PENDING_DB = "2210PIGOTTOILUAP01" PENDING_EXPECT_SIZE = "61295883776" TASKS = [ ("upload", "Q2-C", "BERRYOILUAP101.mdf", SQLDATA + r"\BERRYOILUAP101.mdf", "отмонтированный предок BerryOil"), ("upload", "Q2-C", "BERRYOILUAP101.LDF", SQLDATA + r"\BERRYOILUAP101.LDF", "лог отмонтированной BerryOil"), ("upload", "Q2-A", "Jenkins4_2430Palmdale01_01292026.bak", BAKDIR + r"\Jenkins4_2430Palmdale01_01292026.bak", "предок Palmdale"), ("upload", "Q2-A", "2510PALMDALEOILUAP01_backup_11172025.bak", BAKDIR + r"\2510PALMDALEOILUAP01_backup_11172025.bak", "предок PALMDALEOIL"), ("upload", "Q2-A", "Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", BAKDIR + r"\Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", "предок JOHNSONPETROLEUM"), ("upload", "Q2-A", "Jenkins4_CHERRYENERGYUAP01_04062026.bak", BAKDIR + r"\Jenkins4_CHERRYENERGYUAP01_04062026.bak", "предок 2610CHERRYENERGY"), ("upload", "Q2-C", "2310DallmyrUAP101.mdf", SQLDATA + r"\2310DallmyrUAP101.mdf", "отмонтированный предок Dallmyr"), ("upload", "Q2-C", "2310DallmyrUAP101.ldf", SQLDATA + r"\2310DallmyrUAP101.ldf", "лог"), ("upload", "Q2-C", "PIGOTTOILUAP01_Primary.mdf", SQLDATA + r"\PIGOTTOILUAP01_Primary.mdf", "старый Pigott без префикса"), ("upload", "Q2-C", "PIGOTTOILUAP01_log.ldf", SQLDATA + r"\PIGOTTOILUAP01_log.ldf", "лог"), ("upload", "Q2-A", "Jenkins4_PALMDALEOILUAP01_08052026.bak", BAKDIR + r"\Jenkins4_PALMDALEOILUAP01_08052026.bak", "предыдущая версия PALMDALEOILUAP01"), ("backup", "Q2-B", "2610DALLMYRUAP01", None, "предок 2710DALLMYRUAP01"), ("backup", "Q2-B", "2620Palmdale01", None, "предок 2610PAMDALEUAP01"), ("skip", "Q2-A", "Jenkins4_2710PALMDALEOILUAP011_09112026.bak", None, "redundant: свежий бэкап взят в Q1"), ] def sql(query: str, timeout: int = 590) -> str: p = subprocess.run(SQLCMD + ["-Q", query], capture_output=True, text=True, timeout=timeout) junk = ("SQL Server 2022 will run", "This container is running", "go.microsoft") return "\n".join(l for l in p.stdout.splitlines() if not any(j in l for j in junk)) def log(msg: str): print(f"[{datetime.now().strftime('%H:%M:%S')}] {msg}", flush=True) def write_row(cf, phase, item, src, size, sha, url, status): cf.write(f"{phase}\t{item}\t{src}\t{size}\t{sha}\t{url}\t{status}\n") cf.flush() def hash_and_upload(cf, phase, item, path, delete_after, expect_size=None, note=""): """hash существующего файла -> upload -> (del если delete_after). Возвращает статус.""" log(f"--- {phase} {'BACKUP-RESEND' if delete_after else 'UPLOAD'} {item} ---") out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{path}\" (for %F in (\"{path}\") do @echo SIZE=%~zF) else (echo MISSING)'", timeout=590) ms = re.search(r"SIZE=(\d+)", out) if "MISSING" in out or not ms: log(f" {item}: MISSING — пропускаю") write_row(cf, phase, item, path, "", "", "", "FAIL_SOURCE_MISSING") return "FAIL_SOURCE_MISSING" size = ms.group(1) if expect_size and size != expect_size: log(f" {item}: РАЗМЕР НЕ ОЖИДАЕМЫЙ {size} != {expect_size} — пропускаю выгрузку") write_row(cf, phase, item, path, size, "", "", "FAIL_SIZE_MISMATCH") return "FAIL_SIZE_MISMATCH" log(f" {item}: size={size} ({int(size)/1024**3:.2f} GB), hashing (certutil)...") t0 = time.time() out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'certutil -hashfile \"{path}\" SHA256'", timeout=3600) mh = re.search(r"\b([0-9a-f]{64})\b", out) if not mh: log(f" {item}: не получил hash: {out[:200]}") write_row(cf, phase, item, path, size, "", "", "FAIL_NO_HASH") return "FAIL_NO_HASH" sha = mh.group(1) log(f" {item}: sha256={sha[:16]}... ({time.time()-t0:.0f}s)") url = "" for attempt in (1, 2): # ch12-проверка перед каждой попыткой ping = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -o nul -m 30 -w \"%%{{http_code}}\" {CH12}/'", timeout=120) if "200" not in ping: log(f" {item}: ch12 недоступен перед попыткой {attempt} ({ping[:60]}) — жду 5 мин") time.sleep(300) continue t0 = time.time() try: out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -T \"{path}\" {CH12}'", timeout=28800) except subprocess.TimeoutExpired: out = "UPLOAD_TIMEOUT" mu = re.search(r"https://ch12\.hostserviceapp\.com/\S+", out) if mu: url = mu.group(0).strip() log(f" {item}: uploaded за {time.time()-t0:.0f}s -> {url}") break log(f" {item}: upload попытка {attempt} неудачна: {out[:200]}") time.sleep(60) if not url: write_row(cf, phase, item, path, size, sha, "", "FAIL_UPLOAD_KEPT_ON_SERVER" if delete_after else "FAIL_UPLOAD") return "FAIL_UPLOAD_KEPT_ON_SERVER" if delete_after else "FAIL_UPLOAD" if delete_after: out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'del \"{path}\"'; EXEC xp_cmdshell 'if exist \"{path}\" (echo STILL_THERE) else (echo GONE)'", timeout=590) status = "OK_PENDING_CH12_CHECKSUM" if "GONE" in out else "WARN_DEL_FAILED" write_row(cf, phase, item, path, size, sha, url, status + " | temp deleted") else: out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{path}\" (echo KEPT) else (echo LOST)'", timeout=590) status = "OK_PENDING_CH12_CHECKSUM" if "KEPT" in out else "WARN_SOURCE_LOST" write_row(cf, phase, item, path, size, sha, url, status + " | source kept") log(f" {item}: {status} {note}") return status def do_backup(cf, phase, db, note): bak = f"{db}_09152026.bak" bakpath = f"{BAKDIR}\\{bak}" chk = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo EXISTS) else (echo FREE)'") if "EXISTS" in chk: log(f" {db}: коллизия имени — пропускаю") write_row(cf, phase, db, bakpath, "", "", "", "SKIP_NAME_COLLISION") return "SKIP_NAME_COLLISION" log(f"--- {phase} BACKUP {db} -> {bak} ---") t0 = time.time() out = sql( f"SET NOCOUNT ON;" f"BACKUP DATABASE [{db}] TO DISK = N'{bakpath}' WITH COMPRESSION, COPY_ONLY, INIT, CHECKSUM, STATS=10;" f"RESTORE VERIFYONLY FROM DISK = N'{bakpath}' WITH CHECKSUM;" f"EXEC xp_cmdshell 'certutil -hashfile \"{bakpath}\" SHA256';" f"EXEC xp_cmdshell 'for %F in (\"{bakpath}\") do @echo SIZE=%~zF';", timeout=14400) if "successfully processed" not in out or "is valid" not in out: log(f" {db}: BACKUP/VERIFY fail:\n{out[-800:]}") write_row(cf, phase, db, bakpath, "", "", "", "FAIL_BACKUP_OR_VERIFY") return "FAIL_BACKUP_OR_VERIFY" mh = re.search(r"\b([0-9a-f]{64})\b", out) ms = re.search(r"SIZE=(\d+)", out) sha = mh.group(1) if mh else "" size = ms.group(1) if ms else "" log(f" {db}: BACKUP+VERIFY ok за {time.time()-t0:.0f}s, size={size}, sha256={sha[:16]}...") if not sha or not size: write_row(cf, phase, db, bakpath, size, sha, "", "FAIL_NO_HASH_OR_SIZE") return "FAIL_NO_HASH_OR_SIZE" url = "" for attempt in (1, 2): t0 = time.time() try: out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -T \"{bakpath}\" {CH12}'", timeout=28800) except subprocess.TimeoutExpired: out = "UPLOAD_TIMEOUT" mu = re.search(r"https://ch12\.hostserviceapp\.com/\S+", out) if mu: url = mu.group(0).strip() log(f" {db}: uploaded за {time.time()-t0:.0f}s -> {url}") break log(f" {db}: upload попытка {attempt} неудачна: {out[:200]}") time.sleep(30) if not url: write_row(cf, phase, db, bakpath, size, sha, "", "FAIL_UPLOAD_BAK_KEPT_ON_SERVER") return "FAIL_UPLOAD_BAK_KEPT_ON_SERVER" out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'del \"{bakpath}\"'; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo STILL_THERE) else (echo GONE)'", timeout=590) status = "OK_PENDING_CH12_CHECKSUM" if "GONE" in out else "WARN_DEL_FAILED" write_row(cf, phase, db, bakpath, size, sha, url, status + " | temp deleted") log(f" {db}: {status} {note}") return status # ---------- основной цикл ---------- results = [] with CHECKSUMS.open("a") as cf: # Задача 0: дозалить готовый PIGOTT .bak try: st = hash_and_upload(cf, "Q2-B", PENDING_DB, PENDING_BAK, delete_after=True, expect_size=PENDING_EXPECT_SIZE, note="(RESUME after ch12 outage)") results.append(("Q2-B", PENDING_DB, st)) except subprocess.TimeoutExpired: log(f" {PENDING_DB}: TIMEOUT — файл не трогаю") write_row(cf, "Q2-B", PENDING_DB, PENDING_BAK, "", "", "", "FAIL_TIMEOUT") results.append(("Q2-B", PENDING_DB, "FAIL_TIMEOUT")) except Exception as e: log(f" {PENDING_DB}: EXC {type(e).__name__}: {e}") results.append(("Q2-B", PENDING_DB, f"FAIL_EXC:{type(e).__name__}")) # Задачи 1-14 for kind, phase, name, path, note in TASKS: try: if kind == "skip": log(f"--- {phase} SKIP {name}: {note} ---") write_row(cf, phase, name, path or "", "", "", "", "SKIPPED_REDUNDANT") results.append((phase, name, "SKIPPED_REDUNDANT")) elif kind == "backup": st = do_backup(cf, phase, name, note) results.append((phase, name, st)) elif kind == "upload": st = hash_and_upload(cf, phase, name, path, delete_after=False, note=note) results.append((phase, name, st)) except subprocess.TimeoutExpired: log(f" {name}: TIMEOUT — файл на сервере не трогаю") write_row(cf, phase, name, path or "", "", "", "", "FAIL_TIMEOUT") results.append((phase, name, "FAIL_TIMEOUT")) except Exception as e: log(f" {name}: EXC {type(e).__name__}: {e}") write_row(cf, phase, name, path or "", "", "", "", f"FAIL_EXC:{type(e).__name__}") results.append((phase, name, f"FAIL_EXC:{type(e).__name__}")) log("=== Q2 RESUME ЗАВЕРШЕН ===") oks = sum(1 for r in results if r[2].startswith("OK")) skips = sum(1 for r in results if r[2].startswith("SKIP")) log(f"OK: {oks}, SKIP: {skips}, всего: {len(results)}") for ph, nm, st in results: if not st.startswith(("OK", "SKIP")): log(f" НЕ-OK: {ph} {nm} -> {st}")