skills/osint-autopilot/scripts/build_xlsx.py

#!/usr/bin/env python3
"""osint-autopilot workbook builder. Usage: build_xlsx.py <domain>
Reads findings/findings.csv + evidence/* -> consolidated multi-tab .xlsx."""
import os, sys, re, csv, glob
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

DOMAIN_RE = re.compile(r"[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)+")

D = sys.argv[1] if len(sys.argv) > 1 else sys.exit("usage: build_xlsx.py <domain>")
if not DOMAIN_RE.fullmatch(D): sys.exit(f"error: invalid domain {D!r} (expected a dotted hostname, e.g. example.com)")
ENG = os.path.expanduser(f"~/Research/engagements/{D}")
EV = f"{ENG}/evidence"
OUT = f"{ENG}/{D}-osint-consolidated.xlsx"

wb = Workbook()
HDR = Font(bold=True, color="FFFFFF"); HDRFILL = PatternFill("solid", fgColor="1F3864")
SEV = {"critical":"C00000","high":"E06666","medium":"F4B400","low":"93C47D","info":"9FC5E8"}
WRAP = Alignment(wrap_text=True, vertical="top")

def rl(p):
    try: return [l.rstrip("\n") for l in open(p) if l.strip()]
    except FileNotFoundError: return []
def sheet(title, headers, rows, widths=None, wrapcols=()):
    ws = wb.create_sheet(title); ws.append(headers)
    for c in range(1, len(headers)+1):
        ws.cell(1,c).font=HDR; ws.cell(1,c).fill=HDRFILL
    for r in rows: ws.append(r)
    ws.freeze_panes="A2"; ws.auto_filter.ref=f"A1:{get_column_letter(len(headers))}1"
    for i in range(1,len(headers)+1):
        ws.column_dimensions[get_column_letter(i)].width = widths[i-1] if widths else 22
    for col in wrapcols:
        for row in range(2, ws.max_row+1): ws.cell(row,col).alignment=WRAP
    return ws

resolved = {}
for l in rl(f"{EV}/stage2-expansion/resolved.txt"):
    if " -> " in l: h,i=l.split(" -> ",1); resolved[h]=i

# 1. Summary
ws = wb.active; ws.title="Summary"; ws.append(["Field","Value"])
for c in (1,2): ws.cell(1,c).font=HDR; ws.cell(1,c).fill=HDRFILL
shots = glob.glob(f"{EV}/stage6-screens/*.jpeg")
probe = rl(f"{EV}/stage3-enrichment/http-probe.txt")[1:]
live = [p for p in probe if len(p.split("|"))>=2 and p.split("|")[1] not in ("000","")]
findings = list(csv.reader(open(f"{ENG}/findings/findings.csv"))) if os.path.exists(f"{ENG}/findings/findings.csv") else [[]]
fbody = findings[1:] if len(findings)>1 else []
sevcount = {}
for f in fbody:
    if len(f)>=3: sevcount[f[2]] = sevcount.get(f[2],0)+1
for k,v in [("Engagement",f"{D} — External Red-Team OSINT"),("Authorization","Signed SOW (autopilot: passive+authorized-active; Stage 6 NOT run)"),
    ("Subdomains (union)",str(len(rl(f"{EV}/stage2-expansion/subs-all.txt")))),("Live resolving",str(len(resolved))),
    ("Responsive HTTP hosts",str(len(live))),("Screenshots",str(len(shots))),("Findings",str(len(fbody))),
    ("Severity counts",", ".join(f"{k2}:{v2}" for k2,v2 in sorted(sevcount.items()))),
    ("Generated by","osint-autopilot skill")]:
    ws.append([k,v])
ws.column_dimensions["A"].width=28; ws.column_dimensions["B"].width=95
for row in range(2,ws.max_row+1): ws.cell(row,2).alignment=WRAP

# 2. Findings (from findings.csv)
if fbody:
    wsf = sheet("Findings", findings[0], fbody, widths=[8,42,11,11,15,34,60,50,44], wrapcols=(2,7,8,9))
    for row in range(2, wsf.max_row+1):
        sv=str(wsf.cell(row,3).value).lower()
        if sv in SEV: wsf.cell(row,3).fill=PatternFill("solid",fgColor=SEV[sv]); wsf.cell(row,3).font=Font(bold=True)

# 3. Subdomains
subs=sorted(rl(f"{EV}/stage2-expansion/subs-all.txt"))
sheet("Subdomains",["Subdomain","Resolves","IPs"],
      [[s,"yes" if s in resolved else "no",resolved.get(s,"")] for s in subs],
      widths=[44,10,50],wrapcols=(3,))

# 4. Live Hosts
lh=[]
for l in probe:
    p=l.split("|")
    if len(p)>=4 and p[1] not in ("000",""): lh.append([p[0],p[1],p[2],resolved.get(p[0],""),p[3]])
lh.sort()
sheet("Live Hosts",["Host","HTTP","Server","IPs","Redirect"],lh,widths=[42,8,18,44,40],wrapcols=(4,5))

# 5. API Endpoints (JS + gau-derived + workflow synthesis if present)
eps=set(rl(f"{EV}/js/js-endpoints.txt"))
syn=txt="\n".join(rl(f"{EV}/stage6-content/SYNTHESIS.md")) if os.path.exists(f"{EV}/stage6-content/SYNTHESIS.md") else ""
eps |= set(re.findall(r'^(/[A-Za-z0-9_./{}:-]+)$', syn, re.M))
sheet("API Endpoints",["Endpoint"],[[e] for e in sorted(eps)],widths=[75])

# 6. Secrets
sec=[]
for l in rl(f"{EV}/js/js-secrets.txt"):
    if " :: " in l: h,v=l.split(" :: ",1); sec.append([h,v,"REVIEW — triage functional vs public-SPA"])
sheet("Secrets & Keys",["Host","Match","Note"],sec or [["(none matched)","",""]],widths=[34,60,40],wrapcols=(2,3))

# 7. Ports
prows=[]
for l in rl(f"{EV}/ports/nmap-public.gnmap"):
    if "Ports:" in l:
        ip=re.search(r'Host: ([0-9.]+)',l); op=re.findall(r'(\d+)/open',l)
        if ip and op: prows.append([ip.group(1),", ".join(op)])
sheet("Open Ports",["Public IP","Open Ports"],prows or [["(no open ports captured)",""]],widths=[20,50])

# 8. Internal IP leak
irows=[]
for l in rl(f"{EV}/ports/internal-ip-leak-hosts.txt"):
    if " -> " in l: h,i=l.split(" -> ",1); irows.append([h,i])
sheet("Internal-IP-Leak",["Host (public DNS)","Internal RFC1918 IPs"],irows or [["(none)",""]],widths=[46,60],wrapcols=(2,))

# 9. Screenshots
sheet("Screenshots",["File","Path"],[[os.path.basename(s),s] for s in sorted(shots)] or [["(none)",""]],widths=[60,80])

wb.save(OUT)
print("WROTE", OUT); print("sheets:", wb.sheetnames)