#!/usr/bin/env python3 r""" Q2 RESUME v2 — исправлены баги v1: 1. ping ch12: был '%%{http_code}' (f-string) -> curl печатал литерал; исправлен на '%{http_code}' (PING_QUERY — обычная строка, без f-string). 2. hash-кэш q2_hash_cache.json: уже посчитанные SHA256 (PIGOTT 57GB, Palmdale01 93GB) НЕ пересчитываются. 3. Q2-C отмонтированные mdf/ldf: NTFS ACL блокирует сервисный аккаунт SQL (даже icacls -> Access denied, SeTakeOwnership/SeBackup нет в токене). Одна попытка certutil; при ACCESS_DENIED -> FAIL_ACL_DENIED, без бесполезных ретраев. (Подтверждено диагностикой 15.09 12:5x.) Режим оператора: без скачивания к нам; sha256 -> q2_checksums.tsv; сверка на ch12. Чужие файлы (Q2-A) после upload НЕ удаляются; свой временный .bak (Q2-B) — удаляется после URL. """ import json 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" HASH_CACHE = SI / "q2_hash_cache.json" # ФИКС бага v1: обычная строка (не f-string), одинарный % -> curl печатает код PING_QUERY = ("SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -o nul -m 20 " "-w \"%{http_code}\" " + CH12 + "/' 2>&1") # уже посчитанные полные хеши (из q2_checksums.tsv первого запуска) — кэш CACHE_SEED = { BAKDIR + "\\2210PIGOTTOILUAP01_09152026.bak": {"size": "61295883776", "sha": "2d0023e83ce2dd84494c65d3a88e632a65412f5eb69287b6b08ec866f6100193"}, BAKDIR + "\\Jenkins4_2430Palmdale01_01292026.bak": {"size": "100253564416", "sha": "7f36b39f87e0b449fcf55679567a4a247507e9ff2e72a5375b30ee41e702f9fa"}, } TASKS = [ # (kind, phase, name, source_path, delete_after, note) ("resend", "Q2-B", "2210PIGOTTOILUAP01", BAKDIR + r"\2210PIGOTTOILUAP01_09152026.bak", True, "готовый .bak 57.09GB, hash в кэше — только upload+del"), ("resend", "Q2-A", "Jenkins4_2430Palmdale01_01292026.bak", BAKDIR + r"\Jenkins4_2430Palmdale01_01292026.bak", False, "93.37GB, hash в кэше — только upload, их файл НЕ удаляем"), ("upload", "Q2-A", "2510PALMDALEOILUAP01_backup_11172025.bak", BAKDIR + r"\2510PALMDALEOILUAP01_backup_11172025.bak", False, "44.66GB, hash потерян при kill — переснять (13 мин)"), ("upload", "Q2-A", "Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", BAKDIR + r"\Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", False, "44.45GB, hash прерван kill — переснять"), ("upload", "Q2-A", "Jenkins4_CHERRYENERGYUAP01_04062026.bak", BAKDIR + r"\Jenkins4_CHERRYENERGYUAP01_04062026.bak", False, "16.2GB предок 2610CHERRYENERGY"), ("upload", "Q2-A", "Jenkins4_PALMDALEOILUAP01_08052026.bak", BAKDIR + r"\Jenkins4_PALMDALEOILUAP01_08052026.bak", False, "3.8GB предыдущая версия PALMDALEOILUAP01"), ("upload", "Q2-C", "BERRYOILUAP101.mdf", SQLDATA + r"\BERRYOILUAP101.mdf", False, "Q2-C: ACL-тест (ожидаем FAIL_ACL_DENIED по диагностике)"), ("upload", "Q2-C", "BERRYOILUAP101.LDF", SQLDATA + r"\BERRYOILUAP101.LDF", False, ""), ("upload", "Q2-C", "2310DallmyrUAP101.mdf", SQLDATA + r"\2310DallmyrUAP101.mdf", False, ""), ("upload", "Q2-C", "2310DallmyrUAP101.ldf", SQLDATA + r"\2310DallmyrUAP101.ldf", False, ""), ("upload", "Q2-C", "PIGOTTOILUAP01_Primary.mdf", SQLDATA + r"\PIGOTTOILUAP01_Primary.mdf", False, ""), ("upload", "Q2-C", "PIGOTTOILUAP01_log.ldf", SQLDATA + r"\PIGOTTOILUAP01_log.ldf", False, ""), ("backup", "Q2-B", "2610DALLMYRUAP01", None, True, "предок 2710DALLMYRUAP01, 7.2GB live"), ("backup", "Q2-B", "2620Palmdale01", None, True, "предок 2610PAMDALEUAP01, 1.5GB live"), ("skip", "Q2-A", "Jenkins4_2710PALMDALEOILUAP011_09112026.bak", None, False, "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 load_cache(): if HASH_CACHE.exists(): c = json.loads(HASH_CACHE.read_text()) else: c = {} for k, v in CACHE_SEED.items(): c.setdefault(k, v) return c def save_cache(cache): HASH_CACHE.write_text(json.dumps(cache, indent=1)) def ch12_alive(wait_rounds=6, wait_s=300): """Ping ch12; при недоступности ждать до wait_rounds*wait_s. True/False.""" for i in range(wait_rounds): out = sql(PING_QUERY, timeout=120) if "200" in out: return True log(f" ch12 недоступен (попытка {i+1}/{wait_rounds}): {out[:80].strip()} — жду {wait_s//60} мин") time.sleep(wait_s) return False 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 get_hash(cf, phase, item, path, cache): """size+sha256 файла на сервере (кэш -> certutil). Возвращает (size, sha, status).""" if path in cache: c = cache[path] # сверка размера с фактическим (файл не должен был измениться) out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'for %F in (\"{path}\") do @echo SIZE=%~zF'", timeout=590) ms = re.search(r"SIZE=(\d+)", out) if ms and ms.group(1) == c["size"]: log(f" {item}: hash из кэша (size ok {c['size']})") return c["size"], c["sha"], "OK" log(f" {item}: размер изменился ({ms.group(1) if ms else '?'} != {c['size']}) — пересчитываю hash") 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: return "", "", "FAIL_SOURCE_MISSING" size = ms.group(1) 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) if "ERROR_ACCESS_DENIED" in out or "Access is denied" in out: log(f" {item}: ACL DENIED (сервисный аккаунт SQL не может читать файл)") return size, "", "FAIL_ACL_DENIED" mh = re.search(r"\b([0-9a-f]{64})\b", out) if not mh: log(f" {item}: не получил hash: {out[:150]}") return size, "", "FAIL_NO_HASH" sha = mh.group(1) log(f" {item}: sha256={sha[:16]}... ({time.time()-t0:.0f}s)") cache[path] = {"size": size, "sha": sha} save_cache(cache) return size, sha, "OK" def upload_file(cf, phase, item, path, size, sha, delete_after, note): url = "" for attempt in (1, 2, 3): if not ch12_alive(): log(f" {item}: ch12 так и не ожил — останавливаю upload (файл остаётся на сервере)") break 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[:150]}") time.sleep(60) if not url: write_row(cf, phase, item, path, size, sha, "", "FAIL_UPLOAD_KEPT_ON_SERVER") return "FAIL_UPLOAD_KEPT_ON_SERVER" 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, cache): 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[-600:]}") 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 sha and size: cache[bakpath] = {"size": size, "sha": sha} save_cache(cache) else: write_row(cf, phase, db, bakpath, size, sha, "", "FAIL_NO_HASH_OR_SIZE") return "FAIL_NO_HASH_OR_SIZE" return upload_file(cf, phase, db, bakpath, size, sha, delete_after=True, note=note) # ---------- main ---------- def main(): log(f"PING_QUERY = {PING_QUERY}") out = sql(PING_QUERY, timeout=120) if "200" not in out: log(f"FATAL: ch12 недоступен на старте: {out[:100]}") sys.exit(1) log(f"ch12 жив: {out.strip()[:60]}") cache = load_cache() save_cache(cache) results = [] with CHECKSUMS.open("a") as cf: for kind, phase, name, path, delete_after, 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, cache) results.append((phase, name, st)) else: # resend / upload log(f"--- {phase} {'RESEND' if kind=='resend' else 'UPLOAD'} {name} ---") size, sha, hst = get_hash(cf, phase, name, path, cache) if hst != "OK": write_row(cf, phase, name, path, size, sha, "", hst) results.append((phase, name, hst)) continue st = upload_file(cf, phase, name, path, size, sha, delete_after, 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 v2 ЗАВЕРШЕНА ===") 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}") if __name__ == "__main__": main()