import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from scipy import stats
import os

# Load data (from /content)
# =========================
DATA_DIR = "/content"
def load_csv(filename, data_dir=DATA_DIR):
    """
    Loads a CSV from Colab filesystem (e.g., /content).
    If missing, raises a clear error for the grader.
    """
    path = os.path.join(data_dir, filename)
    if not os.path.exists(path):
        raise FileNotFoundError(
            f"Missing file: {path}\n"
            f"Please upload '{filename}' to Colab (Files pane) before running."
        )
    return pd.read_csv(path)

# all input files
assets  = load_csv("assets.csv")
vulns   = load_csv("vulnerabilities.csv")
mapping = load_csv("asset_vuln_mapping.csv")
devices = load_csv("devices.csv")
cps_models = load_csv("50_cps_models.csv")

print("assets shape:", assets.shape)
print("vulns shape:", vulns.shape)
print("mapping shape:", mapping.shape)

display(assets.head())
display(vulns.head())
display(mapping.head())

# Basic validation
expected_assets_cols  = {"asset_id", "asset_type", "asset_category"}
expected_vulns_cols   = {"vuln_id", "cvss_base_score", "exploit_available"}
expected_mapping_cols = {"asset_id", "vuln_id"}

assert expected_assets_cols.issubset(set(assets.columns)),  f"assets missing cols: {expected_assets_cols - set(assets.columns)}"
assert expected_vulns_cols.issubset(set(vulns.columns)),   f"vulns missing cols: {expected_vulns_cols - set(vulns.columns)}"
assert expected_mapping_cols.issubset(set(mapping.columns)), f"mapping missing cols: {expected_mapping_cols - set(mapping.columns)}"

# Cleaning / typing
vulns["cvss_base_score"] = pd.to_numeric(vulns["cvss_base_score"], errors="coerce")

# exploit_available -> boolean
if vulns["exploit_available"].dtype != bool:
    vulns["exploit_available"] = (
        vulns["exploit_available"]
        .astype(str).str.strip().str.lower()
        .map({"true": True, "false": False})
        .fillna(False)
    )

# Merge: assets -> mapping -> vulnerabilities
df = (
    assets.merge(mapping, on="asset_id", how="left")
          .merge(vulns,   on="vuln_id",  how="left")
)

print("merged df shape:", df.shape)
display(df.head())

assets shape: (10468, 3)
vulns shape: (800, 3)
mapping shape: (78336, 2)

merged df shape: (78336, 6)

# Compute Legacy Risk Score
# Legacy = min(100, sum(CVSS vulnerabilities per asset))
legacy_scores = (
    df.groupby("asset_id", as_index=False)["cvss_base_score"]
      .sum(min_count=1)  # if all NaN -> NaN
      .rename(columns={"cvss_base_score": "cvss_sum"})
)

legacy_scores["cvss_sum"] = legacy_scores["cvss_sum"].fillna(0.0)
legacy_scores["legacy_risk_score"] = np.minimum(100.0, legacy_scores["cvss_sum"])

# Join metadata
legacy_scores = legacy_scores.merge(assets, on="asset_id", how="left")

display(legacy_scores.head())

# Save output CSV
legacy_scores.to_csv("asset_legacy_risk_scores.csv", index=False)
print("Saved: asset_legacy_risk_scores.csv")

Saved: asset_legacy_risk_scores.csv

# Descriptive stats + saturation
scores = legacy_scores["legacy_risk_score"].to_numpy()

sat_count = int((scores == 100).sum())
sat_pct = 100 * sat_count / len(scores)

mean_ = float(np.mean(scores))
median_ = float(np.median(scores))
p90 = float(np.percentile(scores, 90))
p95 = float(np.percentile(scores, 95))
p99 = float(np.percentile(scores, 99))

skew_ = float(stats.skew(scores))
kurt_ = float(stats.kurtosis(scores, fisher=True))

print(f"Saturation @100: {sat_count} / {len(scores)} assets ({sat_pct:.2f}%)")
print("Descriptive stats:")
print(f"  mean   = {mean_:.3f}")
print(f"  median = {median_:.3f}")
print(f"  p90    = {p90:.3f}")
print(f"  p95    = {p95:.3f}")
print(f"  p99    = {p99:.3f}")
print(f"  skewness  = {skew_:.3f}")
print(f"  kurtosis  = {kurt_:.3f} (Fisher)")

# % above high-risk thresholds
for thr in [50, 80, 90, 100]:
    pct = 100 * (scores >= thr).mean()
    print(f"% assets with score >= {thr}: {pct:.2f}%")

# Plot distribution
plt.figure(figsize=(10, 5))
plt.hist(scores, bins=50)
plt.title("Legacy Risk Score Distribution (0-100)")
plt.xlabel("Legacy Risk Score")
plt.ylabel("Number of Assets")
plt.show()

# Boxplot
plt.figure(figsize=(8, 2.5))
plt.boxplot(scores, vert=False)
plt.title("Legacy Risk Score - Boxplot")
plt.xlabel("Legacy Risk Score")
plt.show()

Saturation @100: 844 / 10468 assets (8.06%)
Descriptive stats:
  mean   = 25.662
  median = 11.550
  p90    = 84.300
  p95    = 100.000
  p99    = 100.000
  skewness  = 1.507
  kurtosis  = 1.021 (Fisher)
% assets with score >= 50: 17.46%
% assets with score >= 80: 10.60%
% assets with score >= 90: 9.32%
% assets with score >= 100: 8.06%

# Normality tests
np.random.seed(42)
sample_size = min(5000, len(scores))
sample = np.random.choice(scores, size=sample_size, replace=False)

shapiro_stat, shapiro_p = stats.shapiro(sample)
dag_stat, dag_p = stats.normaltest(scores)
anderson_res = stats.anderson(scores, dist="norm")

print("\nNormality tests:")
print(f"Shapiro-Wilk (n={sample_size}) -> W={shapiro_stat:.4f}, p-value={shapiro_p:.3e}")
print(f"D'Agostino K^2 (n={len(scores)}) -> stat={dag_stat:.4f}, p-value={dag_p:.3e}")
print(f"Anderson-Darling -> A2={anderson_res.statistic:.4f}")
print("  Critical values:", anderson_res.critical_values)
print("  Significance levels:", anderson_res.significance_level)

# QQ plot (standardized)
z = (scores - np.mean(scores)) / np.std(scores, ddof=1)
plt.figure(figsize=(6, 6))
stats.probplot(z, dist="norm", plot=plt)
plt.title("QQ Plot (Legacy Risk Score standardized)")
plt.show()

# Mean vs robust metrics
trimmed_mean_10 = float(stats.trim_mean(scores, 0.10))
winsorized = stats.mstats.winsorize(scores, limits=[0.05, 0.05])
winsor_mean = float(np.mean(winsorized))

print("\nMean vs robust metrics:")
print(f"  mean           = {mean_:.3f}")
print(f"  median         = {median_:.3f}")
print(f"  trimmed mean10 = {trimmed_mean_10:.3f}")
print(f"  winsor mean5   = {winsor_mean:.3f}")

# Mean/Median lines on histogram
plt.figure(figsize=(10, 5))
plt.hist(scores, bins=50)
plt.axvline(mean_, linewidth=2, label=f"Mean={mean_:.2f}")
plt.axvline(median_, linewidth=2, label=f"Median={median_:.2f}")
plt.title("Legacy Risk Score Distribution + Mean/Median")
plt.xlabel("Legacy Risk Score")
plt.ylabel("Number of Assets")
plt.legend()
plt.show()

Normality tests:
Shapiro-Wilk (n=5000) -> W=0.7345, p-value=4.624e-67
D'Agostino K^2 (n=10468) -> stat=2404.8651, p-value=0.000e+00
Anderson-Darling -> A2=1048.6330
  Critical values: [0.576 0.656 0.787 0.918 1.092]
  Significance levels: [15.  10.   5.   2.5  1. ]

Mean vs robust metrics:
  mean           = 25.662
  median         = 11.550
  trimmed mean10 = 19.444
  winsor mean5   = 25.692

import numpy as np

# Calibrate scale 's' so that score at p90(cvss_sum) is ~80
p90_sum = np.percentile(legacy_scores["cvss_sum"], 90)
s = p90_sum / (-np.log(0.2))  # solves: 80 = 100*(1-exp(-p90/s))

print(f"p90(cvss_sum)={p90_sum:.3f} -> scale s={s:.3f}")

# New score: smooth bounded transform of cvss_sum
legacy_scores["new_risk_score"] = 100 * (1 - np.exp(-legacy_scores["cvss_sum"] / s))
legacy_scores["new_risk_score"] = legacy_scores["new_risk_score"].clip(0, 100).round(3)

# Summary stats
new_scores = legacy_scores["new_risk_score"].to_numpy()
legacy_scores_arr = legacy_scores["legacy_risk_score"].to_numpy()

print("\nNew score stats:")
print(
    f"min={new_scores.min():.3f}, "
    f"median={np.median(new_scores):.3f}, "
    f"mean={new_scores.mean():.3f}, "
    f"max={new_scores.max():.3f}"
)
print(
    f"p90={np.percentile(new_scores,90):.3f}, "
    f"p95={np.percentile(new_scores,95):.3f}, "
    f"p99={np.percentile(new_scores,99):.3f}"
)

# Compare saturation (legacy hits 100 exactly; new approaches 100 asymptotically)
near_max_thr = 99.5
legacy_max_count = int((legacy_scores_arr == 100).sum())
near_max_count = int((new_scores >= near_max_thr).sum())

print("\nSaturation:")
print(f"Legacy == 100: {legacy_max_count}/{len(legacy_scores_arr)} ({100*legacy_max_count/len(legacy_scores_arr):.2f}%)")
print(f"New >= {near_max_thr}: {near_max_count}/{len(new_scores)} ({100*near_max_count/len(new_scores):.2f}%)")

display(
    legacy_scores[["asset_id","cvss_sum","legacy_risk_score","new_risk_score","asset_type","asset_category"]].head()
)

p90(cvss_sum)=84.300 -> scale s=52.379

New score stats:
min=1.139, median=19.789, mean=31.586, max=99.995
p90=80.000, p95=93.755, p99=99.982

Saturation:
Legacy == 100: 844/10468 (8.06%)
New >= 99.5: 227/10468 (2.17%)

import numpy as np
import matplotlib.pyplot as plt

# Total number of assets
n_assets = len(legacy_scores)

# Define "max" condition
# Legacy score is exactly 100
legacy_max_mask = legacy_scores["legacy_risk_score"] >= 100

# New score is a float; due to rounding/numerical issues, treat values extremely close to 100 as "max"
tol = 1e-6
new_max_mask = legacy_scores["new_risk_score"] >= (100 - tol)

# Counts and percentages
legacy_max_count = int(legacy_max_mask.sum())
new_max_count = int(new_max_mask.sum())

legacy_max_pct = 100 * legacy_max_count / n_assets
new_max_pct = 100 * new_max_count / n_assets

print("=== Saturation / Maximum Score Comparison ===")
print(f"Total assets: {n_assets}")
print(f"Legacy: max score assets = {legacy_max_count} ({legacy_max_pct:.2f}%)")
print(f"New:    max score assets = {new_max_count} ({new_max_pct:.2f}%)")

# compare the upper tail more clearly
# How many are very close to max (e.g., >= 95)?
legacy_95 = int((legacy_scores["legacy_risk_score"] >= 95).sum())
new_95 = int((legacy_scores["new_risk_score"] >= 95).sum())

print("\n=== Upper Tail (>=95) Comparison ===")
print(f"Legacy: assets >=95 = {legacy_95} ({100*legacy_95/n_assets:.2f}%)")
print(f"New:    assets >=95 = {new_95} ({100*new_95/n_assets:.2f}%)")

# Plot legacy distribution
plt.figure(figsize=(10, 4))
plt.hist(legacy_scores["legacy_risk_score"], bins=50)
plt.title("Legacy Risk Score Distribution (0–100)")
plt.xlabel("Legacy Risk Score")
plt.ylabel("Number of Assets")
plt.show()

# Plot new distribution
plt.figure(figsize=(10, 4))
plt.hist(legacy_scores["new_risk_score"], bins=50)
plt.title("New Risk Score Distribution (0–100)")
plt.xlabel("New Risk Score")
plt.ylabel("Number of Assets")
plt.show()

=== Saturation / Maximum Score Comparison ===
Total assets: 10468
Legacy: max score assets = 844 (8.06%)
New:    max score assets = 0 (0.00%)

=== Upper Tail (>=95) Comparison ===
Legacy: assets >=95 = 909 (8.68%)
New:    assets >=95 = 473 (4.52%)

import numpy as np
import pandas as pd

# sanity check
required_cols = {"asset_id", "cvss_sum", "legacy_risk_score", "new_risk_score", "asset_type", "asset_category"}
missing = required_cols - set(legacy_scores.columns)
assert len(missing) == 0, f"legacy_scores is missing required columns: {missing}"

# (A) Top 10 by Legacy (score only)

top10_legacy = (
    legacy_scores
    .sort_values(by=["legacy_risk_score", "asset_id"], ascending=[False, True])  # stable, but does not add extra info
    .head(10)
    .loc[:, ["asset_id", "asset_type", "asset_category", "cvss_sum", "legacy_risk_score", "new_risk_score"]]
    .reset_index(drop=True)
)


# Top 10 by New
# New score provides ordering in the upper tail (equivalent to ordering by cvss_sum).
top10_new = (
    legacy_scores
    .sort_values(by=["new_risk_score", "cvss_sum", "asset_id"], ascending=[False, False, True])
    .head(10)
    .loc[:, ["asset_id", "asset_type", "asset_category", "cvss_sum", "legacy_risk_score", "new_risk_score"]]
    .reset_index(drop=True)
)

print("=== Top 10 by Legacy Risk Score (score only) ===")
display(top10_legacy)

print("\n=== Top 10 by New Risk Score ===")
display(top10_new)

# Re-rank within the saturated legacy group (Legacy == 100)
sat = legacy_scores[legacy_scores["legacy_risk_score"] >= 100].copy()

top10_within_legacy100_by_new = (
    sat.sort_values(by=["new_risk_score", "cvss_sum", "asset_id"], ascending=[False, False, True])
       .head(10)
       .loc[:, ["asset_id", "asset_type", "asset_category", "cvss_sum", "legacy_risk_score", "new_risk_score"]]
       .reset_index(drop=True)
)

print(f"\nLegacy saturation group size (Legacy==100): {len(sat)} assets")
print("\n=== Top 10 within Legacy==100, ranked by New Risk Score ===")
display(top10_within_legacy100_by_new)

# overlap summary
legacy_set = set(top10_legacy["asset_id"])
new_set = set(top10_new["asset_id"])
overlap = sorted(list(legacy_set & new_set))

print("\n=== Overlap between Top 10 lists ===")
print(f"Overlap count: {len(overlap)} / 10")
print("Overlapping asset_ids:", overlap if overlap else "None")

# xport
top10_legacy.to_csv("top10_legacy_riskiest.csv", index=False)
top10_new.to_csv("top10_new_riskiest.csv", index=False)
top10_within_legacy100_by_new.to_csv("top10_within_legacy100_by_new.csv", index=False)

print("\nSaved:")
print("  - top10_legacy_riskiest.csv")
print("  - top10_new_riskiest.csv")
print("  - top10_within_legacy100_by_new.csv")

# Visual proof: score vs CVSS sum (Saturation vs Smooth ranking)
fig, axes = plt.subplots(1, 2, figsize=(14, 5), sharex=True)

# Legacy scatter
axes[0].scatter(
    legacy_scores["cvss_sum"],
    legacy_scores["legacy_risk_score"],
    alpha=0.25,
    s=10
)
axes[0].set_title("Legacy Risk Score vs CVSS Sum")
axes[0].set_xlabel("Cumulative CVSS Score")
axes[0].set_ylabel("Legacy Risk Score")
axes[0].set_ylim(0, 105)
axes[0].axhline(100, color="red", linestyle="--", alpha=0.7)
axes[0].text(legacy_scores["cvss_sum"].max()*0.55, 101, "Hard cap / saturation at 100", color="red")

# New scatter
axes[1].scatter(
    legacy_scores["cvss_sum"],
    legacy_scores["new_risk_score"],
    alpha=0.25,
    s=10
)
axes[1].set_title("New Risk Score vs CVSS Sum")
axes[1].set_xlabel("Cumulative CVSS Score")
axes[1].set_ylabel("New Risk Score")
axes[1].set_ylim(0, 105)

plt.tight_layout()
plt.show()

=== Top 10 by Legacy Risk Score (score only) ===

=== Top 10 by New Risk Score ===

Legacy saturation group size (Legacy==100): 844 assets

=== Top 10 within Legacy==100, ranked by New Risk Score ===

=== Overlap between Top 10 lists ===
Overlap count: 1 / 10
Overlapping asset_ids: ['asset_10036']

Saved:
  - top10_legacy_riskiest.csv
  - top10_new_riskiest.csv
  - top10_within_legacy100_by_new.csv

import numpy as np
import pandas as pd

#  helper:
def load_from_uploaded(uploaded_dict, filename):
    """
    If you're using Google Colab "files.upload()", pass the 'uploaded' dict here.
    Otherwise, you can ignore this and load with pd.read_csv directly.
    """
    import io
    return pd.read_csv(io.BytesIO(uploaded_dict[filename]))

#  Helpers:
def ensure_bool(series: pd.Series) -> pd.Series:
    if series.dtype == bool:
        return series
    return (
        series.astype(str).str.strip().str.lower()
        .map({"true": True, "false": False})
        .fillna(False)
        .astype(bool)
    )

def new_risk_score(cvss_sum: pd.Series, s: float) -> pd.Series:
    return 100 * (1 - np.exp(-cvss_sum / s))

need_rebuild = any(name not in globals() for name in ["df", "legacy_scores", "s"])

if need_rebuild:
    # If running locally / notebook with files in working dir:
    assets  = pd.read_csv("assets.csv")
    vulns   = pd.read_csv("vulnerabilities.csv")
    mapping = pd.read_csv("asset_vuln_mapping.csv")

    # Clean / normalize
    vulns["cvss_base_score"] = pd.to_numeric(vulns["cvss_base_score"], errors="coerce").fillna(0.0)
    vulns["exploit_available"] = ensure_bool(vulns["exploit_available"])

    df = (
        assets.merge(mapping, on="asset_id", how="left")
              .merge(vulns, on="vuln_id", how="left")
    )

    df["cvss_base_score"] = pd.to_numeric(df["cvss_base_score"], errors="coerce").fillna(0.0)
    df["exploit_available"] = ensure_bool(df["exploit_available"])
    df["asset_category"] = df["asset_category"].astype(str).str.strip()

    #  Before (legacy + new risk) at asset level
    legacy_scores = (
        df.groupby("asset_id", as_index=False)["cvss_base_score"]
          .sum()
          .rename(columns={"cvss_base_score": "cvss_sum"})
          .merge(assets, on="asset_id", how="left")
    )
    legacy_scores["cvss_sum"] = legacy_scores["cvss_sum"].fillna(0.0)
    legacy_scores["legacy_risk_score"] = np.minimum(100.0, legacy_scores["cvss_sum"])

    # Calibrate s: set p90(cvss_sum) -> NewRisk ≈ 80
    p90_sum = np.percentile(legacy_scores["cvss_sum"], 90)
    s = p90_sum / (-np.log(0.2))

    legacy_scores["new_risk_score"] = new_risk_score(legacy_scores["cvss_sum"], s).clip(0, 100)

#  IT: patch only high priority (CVSS>=9 OR exploit_available=True) -> 0
#  Medical: cannot patch -> reduce ALL vulnerabilities by 50%
df_q3 = df.copy()
df_q3["asset_category"] = df_q3["asset_category"].astype(str).str.strip()

is_it  = df_q3["asset_category"].str.lower().eq("it")
is_med = df_q3["asset_category"].str.lower().eq("medical")

high_priority = (df_q3["cvss_base_score"] >= 9.0) | (df_q3["exploit_available"] == True)

df_q3["cvss_after"] = df_q3["cvss_base_score"].astype(float)

# IT: patch high-priority vulns => 0
df_q3.loc[is_it & high_priority, "cvss_after"] = 0.0

# Medical: reduce all vulns by 50%
df_q3.loc[is_med, "cvss_after"] = 0.5 * df_q3.loc[is_med, "cvss_base_score"]

# Aggregate AFTER at asset level + merge before/after
residual_cvss = (
    df_q3.groupby("asset_id", as_index=False)["cvss_after"]
         .sum()
         .rename(columns={"cvss_after": "cvss_sum_after"})
)
residual_cvss["cvss_sum_after"] = residual_cvss["cvss_sum_after"].fillna(0.0)
residual_cvss["new_risk_score_after"] = new_risk_score(residual_cvss["cvss_sum_after"], s).clip(0, 100)

risk_before_after = legacy_scores.merge(residual_cvss, on="asset_id", how="left")
risk_before_after["cvss_sum_after"] = risk_before_after["cvss_sum_after"].fillna(0.0)
risk_before_after["new_risk_score_after"] = risk_before_after["new_risk_score_after"].fillna(
    new_risk_score(risk_before_after["cvss_sum_after"], s).clip(0, 100)
)

risk_before_after["delta_new_score"] = risk_before_after["new_risk_score_after"] - risk_before_after["new_risk_score"]
risk_before_after["delta_cvss"] = risk_before_after["cvss_sum_after"] - risk_before_after["cvss_sum"]

# Rounding for presentation
for c in ["cvss_sum", "cvss_sum_after", "new_risk_score", "new_risk_score_after", "delta_new_score", "delta_cvss"]:
    risk_before_after[c] = risk_before_after[c].astype(float).round(3)

# Illustrative table: examples
top10_residual = (
    risk_before_after.sort_values("new_risk_score_after", ascending=False)
    .head(10)[
        ["asset_id","asset_type","asset_category",
         "cvss_sum","cvss_sum_after",
         "new_risk_score","new_risk_score_after","delta_new_score"]
    ]
)

top10_improvements = (
    risk_before_after.sort_values("delta_new_score", ascending=True)
    .head(10)[
        ["asset_id","asset_type","asset_category",
         "cvss_sum","cvss_sum_after",
         "new_risk_score","new_risk_score_after","delta_new_score"]
    ]
)

print("\n=== Top 10 Residual Risk (AFTER remediation) [illustrative] ===")
display(top10_residual)

print("\n=== Top 10 Improvements (largest drops) [illustrative] ===")
display(top10_improvements)

# Core numeric summaries (before vs after)
mean_before   = float(risk_before_after["new_risk_score"].mean())
mean_after    = float(risk_before_after["new_risk_score_after"].mean())
median_before = float(risk_before_after["new_risk_score"].median())
median_after  = float(risk_before_after["new_risk_score_after"].median())

print("\n=== Overall Summary (New Risk Score) ===")
print(f"Mean   before: {mean_before:.3f} | after: {mean_after:.3f} | Δ: {mean_after-mean_before:.3f}")
print(f"Median before: {median_before:.3f} | after: {median_after:.3f} | Δ: {median_after-median_before:.3f}")

print("\n=== Overall Summary (CVSS Sum) ===")
print(f"Mean CVSS before: {risk_before_after['cvss_sum'].mean():.3f}")
print(f"Mean CVSS after : {risk_before_after['cvss_sum_after'].mean():.3f}")
print(f"Mean ΔCVSS      : {risk_before_after['delta_cvss'].mean():.3f}")
print(f"Median ΔCVSS    : {risk_before_after['delta_cvss'].median():.3f}")

pct_improved = 100 * (risk_before_after["delta_cvss"] < 0).mean()
print(f"\nAssets improved (by CVSS decrease): {pct_improved:.2f}%")

# Save full before/after table
risk_before_after.to_csv("asset_risk_before_after_q3.csv", index=False)
print("\nSaved: asset_risk_before_after_q3.csv")

=== Top 10 Residual Risk (AFTER remediation) [illustrative] ===

=== Top 10 Improvements (largest drops) [illustrative] ===

=== Overall Summary (New Risk Score) ===
Mean   before: 31.586 | after: 25.830 | Δ: -5.757
Median before: 19.789 | after: 14.328 | Δ: -5.461

=== Overall Summary (CVSS Sum) ===
Mean CVSS before: 35.645
Mean CVSS after : 25.609
Mean ΔCVSS      : -10.036
Median ΔCVSS    : -2.600

Assets improved (by CVSS decrease): 58.31%

Saved: asset_risk_before_after_q3.csv

import pandas as pd
from IPython.display import display, HTML

cat = df_q3["asset_category"].astype(str).str.strip().str.lower()
is_it  = cat.eq("it")
is_med = cat.eq("medical")

high_priority = (df_q3["cvss_base_score"] >= 9.0) | (df_q3["exploit_available"] == True)

# IT: asset is addressable if it has at least 1 high priority vulnerability
it_addressable_assets = (
    df_q3.loc[is_it, ["asset_id"]]
         .assign(high_priority=high_priority.loc[is_it].values)
         .groupby("asset_id")["high_priority"]
         .any()
)

total_assets = int(df_q3["asset_id"].nunique())
it_assets = int(df_q3.loc[is_it, "asset_id"].nunique())
med_assets = int(df_q3.loc[is_med, "asset_id"].nunique())

it_addressable = int(it_addressable_assets.sum())
total_addressable = it_addressable + med_assets

pct_it = 100 * it_addressable / it_assets
pct_med = 100.0
pct_total = 100 * total_addressable / total_assets

#  HTML "flow" card
html = f"""
<div style="font-family: Arial; max-width: 900px; border:1px solid #e5e7eb; border-radius:14px; padding:16px;">
  <div style="font-size:18px; font-weight:700; margin-bottom:8px;">Sanity Check — Where Remediation Applies</div>
  <div style="color:#6b7280; margin-bottom:14px;">
    Here I check which assets are actually affected by the remediation rules.
  </div>

  <div style="display:flex; gap:14px; flex-wrap:wrap;">
    <div style="flex:1; min-width:240px; background:#f9fafb; border:1px solid #e5e7eb; border-radius:12px; padding:12px;">
      <div style="font-weight:700; margin-bottom:6px;">Total Environment</div>
      <div style="font-size:28px; font-weight:800;">{total_assets:,}</div>
      <div style="color:#6b7280;">assets in total</div>
    </div>

    <div style="flex:1; min-width:240px; background:#f9fafb; border:1px solid #e5e7eb; border-radius:12px; padding:12px;">
      <div style="font-weight:700; margin-bottom:6px;">Medical Assets</div>
      <div style="font-size:28px; font-weight:800;">{med_assets:,}</div>
      <div style="color:#6b7280;">all of them are affected (50% reduction applies)</div>
    </div>

    <div style="flex:1; min-width:240px; background:#f9fafb; border:1px solid #e5e7eb; border-radius:12px; padding:12px;">
      <div style="font-weight:700; margin-bottom:6px;">IT Assets</div>
      <div style="font-size:28px; font-weight:800;">{it_assets:,}</div>
      <div style="color:#6b7280;">{pct_it:.2f}% have at least one high-priority vulnerability</div>
    </div>
  </div>

  <div style="margin:16px 0 10px; display:flex; align-items:center; gap:10px;">
    <div style="flex:1; height:2px; background:#e5e7eb;"></div>
    <div style="color:#6b7280; font-weight:700;">⇒ Overall impact</div>
    <div style="flex:1; height:2px; background:#e5e7eb;"></div>
  </div>

  <div style="background:#eef2ff; border:1px solid #c7d2fe; border-radius:12px; padding:12px;">
    <div style="font-weight:800; font-size:16px;">Assets affected by the policy</div>
    <div style="display:flex; gap:14px; flex-wrap:wrap; align-items:baseline; margin-top:6px;">
      <div style="font-size:28px; font-weight:900;">{pct_total:.2f}%</div>
      <div style="color:#6b7280;">({total_addressable:,} out of {total_assets:,} assets)</div>
    </div>
  </div>

  <div style="color:#6b7280; margin-top:12px; font-size:13px;">
    In practice, this means that all Medical devices are impacted by the mitigation,
    while only IT devices that contain at least one high-priority vulnerability are actually changed.
    This explains why we observe improvement in about {pct_total:.2f}% of the assets in Task 1.
  </div>
</div>
"""
display(HTML(html))

import numpy as np

print("=== Output sanity checks (Task 1) ===")

# bounds
assert ((risk_before_after["new_risk_score"].between(0,100)).all())
assert ((risk_before_after["new_risk_score_after"].between(0,100)).all())
assert ((risk_before_after["cvss_sum"] >= 0).all())
assert ((risk_before_after["cvss_sum_after"] >= 0).all())

# deltas consistent
assert np.allclose(
    risk_before_after["delta_cvss"],
    risk_before_after["cvss_sum_after"] - risk_before_after["cvss_sum"]
)
assert np.allclose(
    risk_before_after["delta_new_score"],
    risk_before_after["new_risk_score_after"] - risk_before_after["new_risk_score"]
)

# monotonicity (should never increase)
n_worse = int((risk_before_after["delta_cvss"] > 0).sum())
print(f"Assets that got worse (delta_cvss > 0): {n_worse}")

# quick counts
n = len(risk_before_after)
n_improved = int((risk_before_after["delta_cvss"] < 0).sum())
n_same = int((risk_before_after["delta_cvss"] == 0).sum())

print(f"Total assets: {n}")
print(f"Improved: {n_improved} ({100*n_improved/n:.2f}%)")
print(f"Unchanged: {n_same} ({100*n_same/n:.2f}%)")

print("All checks passed ")

=== Output sanity checks (Task 1) ===
Assets that got worse (delta_cvss > 0): 0
Total assets: 10468
Improved: 6104 (58.31%)
Unchanged: 4364 (41.69%)
All checks passed

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

# Guard
required_cols = {"new_risk_score", "new_risk_score_after"}
missing = required_cols - set(risk_before_after.columns)
assert not missing, f"risk_before_after is missing columns: {missing}. Run Task 1 first."

before = risk_before_after["new_risk_score"].astype(float).clip(0, 100)
after  = risk_before_after["new_risk_score_after"].astype(float).clip(0, 100)

# Updated scatter: colored by category (IT vs Medical)
plt.figure(figsize=(7,6))

r_scatter = risk_before_after.copy()
r_scatter["asset_category"] = r_scatter["asset_category"].astype(str).str.strip().str.lower()

mask = r_scatter["new_risk_score"] < 80

# IT
it = r_scatter[mask & (r_scatter["asset_category"] == "it")]
plt.scatter(
    it["new_risk_score"],
    it["new_risk_score_after"],
    alpha=0.35,
    s=12,
    label="IT"
)

# Medical
med = r_scatter[mask & (r_scatter["asset_category"] == "medical")]
plt.scatter(
    med["new_risk_score"],
    med["new_risk_score_after"],
    alpha=0.35,
    s=12,
    label="Medical"
)

plt.plot([0,80], [0,80], linestyle="--", color="black", linewidth=1)  # clear diagonal
plt.xlabel("New Risk Score (Before)")
plt.ylabel("New Risk Score (After)")
plt.title("Residual Risk (Zoomed: Before < 80)")
plt.legend()
plt.tight_layout()
plt.show()

# Histogram overlay
plt.figure(figsize=(7,5))
bins = np.linspace(0, 100, 41)  # 40 bins
plt.hist(before, bins=bins, alpha=0.55, label="Before")
plt.hist(after,  bins=bins, alpha=0.55, label="After")
plt.xlabel("New Risk Score")
plt.ylabel("Number of Assets")
plt.title("Distribution of Risk Scores: Before vs After")
plt.legend()
plt.tight_layout()
plt.show()

# CDF + Boxplot
def ecdf(x):
    x = np.sort(np.asarray(x))
    y = np.arange(1, len(x)+1) / len(x)
    return x, y

x1, y1 = ecdf(before.values)
x2, y2 = ecdf(after.values)

plt.figure(figsize=(9,5))
plt.plot(x1, y1, label="Before")
plt.plot(x2, y2, label="After")
plt.title("CDF of New Risk Scores: Before vs After")
plt.xlabel("New Risk Score")
plt.ylabel("Fraction of Assets ≤ score")
plt.legend()
plt.grid(True, alpha=0.25)
plt.tight_layout()
plt.show()

plt.figure(figsize=(7,5))
plt.boxplot([before, after], tick_labels=["Before", "After"], showfliers=False)
plt.title("New Risk Score: Before vs After (Boxplot)")
plt.ylabel("New Risk Score")
plt.grid(True, axis="y", alpha=0.25)
plt.tight_layout()
plt.show()

# Numeric distribution summaries
summary = pd.DataFrame({
    "metric": ["mean", "median", "p10", "p25", "p50", "p75", "p90", "p95"],
    "before": [
        before.mean(), before.median(),
        np.percentile(before, 10), np.percentile(before, 25),
        np.percentile(before, 50), np.percentile(before, 75),
        np.percentile(before, 90), np.percentile(before, 95),
    ],
    "after": [
        after.mean(), after.median(),
        np.percentile(after, 10), np.percentile(after, 25),
        np.percentile(after, 50), np.percentile(after, 75),
        np.percentile(after, 90), np.percentile(after, 95),
    ],
})
summary["delta (after-before)"] = summary["after"] - summary["before"]
summary[["before","after","delta (after-before)"]] = summary[["before","after","delta (after-before)"]].round(3)

print("\n=== Distribution Summary (New Risk Score) ===")
display(summary)

thresholds = [20, 40, 60, 80]
rows = []
for t in thresholds:
    pct_before = 100 * (before >= t).mean()
    pct_after  = 100 * (after  >= t).mean()
    rows.append([t, round(pct_before, 2), round(pct_after, 2), round(pct_after - pct_before, 2)])

thr_df = pd.DataFrame(rows, columns=["threshold", "% before >= t", "% after >= t", "Δ (after-before)"])

print("\n=== Threshold Shifts (operational view) ===")
display(thr_df)

# Category-level breakdown (IT vs Medical)
r = risk_before_after.copy()

# flags
r["is_IT"] = r["asset_category"].astype(str).str.lower().eq("it")
r["is_Medical"] = r["asset_category"].astype(str).str.lower().eq("medical")

# category summary
cat_summary = (
    r.groupby("asset_category")
     .agg(
        n_assets=("asset_id","count"),
        mean_before=("new_risk_score","mean"),
        mean_after=("new_risk_score_after","mean"),
        median_before=("new_risk_score","median"),
        median_after=("new_risk_score_after","median"),
        pct_improved=("delta_cvss", lambda x: 100*(x < 0).mean()),
        pct_high_before=("new_risk_score", lambda x: 100*(x >= 80).mean()),
        pct_high_after=("new_risk_score_after", lambda x: 100*(x >= 80).mean()),
     )
)
cat_summary["mean_delta"] = cat_summary["mean_after"] - cat_summary["mean_before"]
cat_summary["median_delta"] = cat_summary["median_after"] - cat_summary["median_before"]
cat_summary["high_delta"] = cat_summary["pct_high_after"] - cat_summary["pct_high_before"]

print("\n=== Category Breakdown (IT vs Medical) ===")
display(cat_summary.round(3))

=== Distribution Summary (New Risk Score) ===

=== Threshold Shifts (operational view) ===

=== Category Breakdown (IT vs Medical) ===

import pandas as pd
import re

# Load
models = pd.read_csv("50_cps_models.csv")
models.head()

# Pre-processing
# Keep only the model column (and ensure it's string)
df = models.copy()
df["model"] = df["model"].astype(str)

# Clean names (strip whitespace + normalize spaces)
df["model_clean"] = (
    df["model"]
    .str.strip()
    .str.replace(r"\s+", " ", regex=True)
)

# Remove duplicates (keep first occurrence)
df = df.drop_duplicates(subset=["model_clean"]).reset_index(drop=True)

# Vendor pattern detection
# (Rule: vendor = first token OR first two tokens if needed like "B.Braun" / "Johnson Controls" / "Rockwell Automation")
KNOWN_TWO_WORD_VENDORS = {
    "johnson controls",
    "rockwell automation",
    "schneider electric",
    "nova biomedical",
    "becton dickinson",
    "rauland-borg",
}
# Also handle dotted vendors like B.Braun
DOTTED_VENDOR_REGEX = r"^[A-Za-z]\.[A-Za-z][A-Za-z]*\b"

def extract_vendor(model_name: str) -> str:
    s = model_name.strip()
    if not s:
        return "Unknown"

    # Handle dotted vendor (e.g., "B.Braun ...")
    if re.search(DOTTED_VENDOR_REGEX, s):
        return s.split()[0]  # "B.Braun"

    parts = s.split()
    if len(parts) == 1:
        return parts[0]

    first_two = f"{parts[0]} {parts[1]}".lower()
    if first_two in KNOWN_TWO_WORD_VENDORS:
        return f"{parts[0]} {parts[1]}"

    # Default: first token vendor (e.g., APC, Philips, Siemens)
    return parts[0]

df["vendor"] = df["model_clean"].apply(extract_vendor)

# show vendor distribution
vendor_counts = df["vendor"].value_counts().reset_index()
vendor_counts.columns = ["vendor", "n_models"]

print(f"Original rows: {len(models)}")
print(f"After de-dup:  {len(df)}")
display(vendor_counts)

# cleaned table
df.head(10)

Original rows: 50
After de-dup:  50

# Build batch prompts (10 models per prompt)
BATCH_SIZE = 10

def make_batches(series, batch_size=10):
    items = series.tolist()
    return [items[i:i+batch_size] for i in range(0, len(items), batch_size)]

models_list = df["model_clean"]  # this is the clean column we want to send
batches = make_batches(models_list, BATCH_SIZE)

def prompt_device_type(batch):
    models_block = "\n".join([f"- {m}" for m in batch])
    return f"""You are a cybersecurity analyst specializing in industrial and medical CPS devices.

For each device model below, return its device type.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "device_type".
- "device_type" should be a short category name (e.g., "Infusion Pump", "UPS", "Patient Monitor", "PLC", "Thin Client").
- If unknown, return "Unknown".
- Do not guess. Only return a category if reasonably confident.
- JSON only. No extra text.

Device models:
{models_block}
"""

def prompt_operating_system(batch):
    models_block = "\n".join([f"- {m}" for m in batch])
    return f"""You are a cybersecurity analyst evaluating CPS devices.

For each device model below, identify the operating system if it is publicly documented.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "operating_system", "confidence".
- If not publicly known, return "Unknown".
- Do NOT speculate.
- If multiple firmware generations exist, return the most common known OS.
- confidence must be one of: High / Medium / Low
- JSON only. No extra text.

Device models:
{models_block}
"""

# Print prompts ready for copy/paste
print("========== SECTION (a): Device Type prompts ==========\n")
for i, batch in enumerate(batches, 1):
    print(f"\n--- Prompt A{i} (models {((i-1)*BATCH_SIZE)+1}-{min(i*BATCH_SIZE, len(df))}) ---\n")
    print(prompt_device_type(batch))

print("\n\n========== SECTION (b): Operating System prompts ==========\n")
for i, batch in enumerate(batches, 1):
    print(f"\n--- Prompt B{i} (models {((i-1)*BATCH_SIZE)+1}-{min(i*BATCH_SIZE, len(df))}) ---\n")
    print(prompt_operating_system(batch))

========== SECTION (a): Device Type prompts ==========


--- Prompt A1 (models 1-10) ---

You are a cybersecurity analyst specializing in industrial and medical CPS devices.

For each device model below, return its device type.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "device_type".
- "device_type" should be a short category name (e.g., "Infusion Pump", "UPS", "Patient Monitor", "PLC", "Thin Client").
- If unknown, return "Unknown".
- Do not guess. Only return a category if reasonably confident.
- JSON only. No extra text.

Device models:
- APC 0N-9582PANTERA
- APC AP7811B
- APC AP9630
- APC AP9631
- APC ATS
- APC MasterSwitchrPDU
- APC MasterSwitchrPDU2
- APC Smart-UPS 1500
- APC Smart-UPS SMT 1500
- APC Smart-UPS SRT 2200


--- Prompt A2 (models 11-20) ---

You are a cybersecurity analyst specializing in industrial and medical CPS devices.

For each device model below, return its device type.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "device_type".
- "device_type" should be a short category name (e.g., "Infusion Pump", "UPS", "Patient Monitor", "PLC", "Thin Client").
- If unknown, return "Unknown".
- Do not guess. Only return a category if reasonably confident.
- JSON only. No extra text.

Device models:
- APC Smart-UPS SRT 5000
- Alaris 8015 PC Unit
- Alaris 8100 Pump Module
- Alaris 8110 Syringe Module
- B.Braun Infusomat Space
- B.Braun SmartBattery with Wifi
- Baxter Novum IQ
- Baxter Sigma Spectrum
- Baxter Sigma Spectrum Wireless Module
- Baxter Spectrum IQ


--- Prompt A3 (models 21-30) ---

You are a cybersecurity analyst specializing in industrial and medical CPS devices.

For each device model below, return its device type.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "device_type".
- "device_type" should be a short category name (e.g., "Infusion Pump", "UPS", "Patient Monitor", "PLC", "Thin Client").
- If unknown, return "Unknown".
- Do not guess. Only return a category if reasonably confident.
- JSON only. No extra text.

Device models:
- Becton Dickinson Pyxis ES System
- Carel PCO1000WB0
- Carel pCOWeb Card
- Cerner Connectivity Engine
- Eaton Tripp Lite Series
- Hill-Rom NaviCare Room Control Board
- Honeywell ComfortPoint Open CPO-VAV2A
- Hospira Plum 360
- IGEL IGEL Thin Client
- Johnson Controls NAE


--- Prompt A4 (models 31-40) ---

You are a cybersecurity analyst specializing in industrial and medical CPS devices.

For each device model below, return its device type.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "device_type".
- "device_type" should be a short category name (e.g., "Infusion Pump", "UPS", "Patient Monitor", "PLC", "Thin Client").
- If unknown, return "Unknown".
- Do not guess. Only return a category if reasonably confident.
- JSON only. No extra text.

Device models:
- Lantronix MatchPort AR
- Microsystems SPM Client
- Nova Biomedical StatStrip
- Omnicell Automated Dispensing Cabinet
- Philips EarlyVue VS30
- Philips IntelliVue
- Philips IntelliVue MX40
- Philips IntelliVue X3
- Philips PIIC
- Rauland-Borg Responder


--- Prompt A5 (models 41-50) ---

You are a cybersecurity analyst specializing in industrial and medical CPS devices.

For each device model below, return its device type.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "device_type".
- "device_type" should be a short category name (e.g., "Infusion Pump", "UPS", "Patient Monitor", "PLC", "Thin Client").
- If unknown, return "Unknown".
- Do not guess. Only return a category if reasonably confident.
- JSON only. No extra text.

Device models:
- Roche Accu-Chek Inform II
- Rockwell Automation PanelView Plus
- Rockwell Automation PanelView Plus 7 Performance 1000
- Schneider Electric AS series
- Schneider Electric AS-P
- Siemens PXC Modular
- Siemens SIMATIC HMI
- Siemens SIMATIC IPC
- Smiths Medical MedFusion 4000
- Swisslog Translogic Tube Station


========== SECTION (b): Operating System prompts ==========


--- Prompt B1 (models 1-10) ---

You are a cybersecurity analyst evaluating CPS devices.

For each device model below, identify the operating system if it is publicly documented.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "operating_system", "confidence".
- If not publicly known, return "Unknown".
- Do NOT speculate.
- If multiple firmware generations exist, return the most common known OS.
- confidence must be one of: High / Medium / Low
- JSON only. No extra text.

Device models:
- APC 0N-9582PANTERA
- APC AP7811B
- APC AP9630
- APC AP9631
- APC ATS
- APC MasterSwitchrPDU
- APC MasterSwitchrPDU2
- APC Smart-UPS 1500
- APC Smart-UPS SMT 1500
- APC Smart-UPS SRT 2200


--- Prompt B2 (models 11-20) ---

You are a cybersecurity analyst evaluating CPS devices.

For each device model below, identify the operating system if it is publicly documented.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "operating_system", "confidence".
- If not publicly known, return "Unknown".
- Do NOT speculate.
- If multiple firmware generations exist, return the most common known OS.
- confidence must be one of: High / Medium / Low
- JSON only. No extra text.

Device models:
- APC Smart-UPS SRT 5000
- Alaris 8015 PC Unit
- Alaris 8100 Pump Module
- Alaris 8110 Syringe Module
- B.Braun Infusomat Space
- B.Braun SmartBattery with Wifi
- Baxter Novum IQ
- Baxter Sigma Spectrum
- Baxter Sigma Spectrum Wireless Module
- Baxter Spectrum IQ


--- Prompt B3 (models 21-30) ---

You are a cybersecurity analyst evaluating CPS devices.

For each device model below, identify the operating system if it is publicly documented.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "operating_system", "confidence".
- If not publicly known, return "Unknown".
- Do NOT speculate.
- If multiple firmware generations exist, return the most common known OS.
- confidence must be one of: High / Medium / Low
- JSON only. No extra text.

Device models:
- Becton Dickinson Pyxis ES System
- Carel PCO1000WB0
- Carel pCOWeb Card
- Cerner Connectivity Engine
- Eaton Tripp Lite Series
- Hill-Rom NaviCare Room Control Board
- Honeywell ComfortPoint Open CPO-VAV2A
- Hospira Plum 360
- IGEL IGEL Thin Client
- Johnson Controls NAE


--- Prompt B4 (models 31-40) ---

You are a cybersecurity analyst evaluating CPS devices.

For each device model below, identify the operating system if it is publicly documented.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "operating_system", "confidence".
- If not publicly known, return "Unknown".
- Do NOT speculate.
- If multiple firmware generations exist, return the most common known OS.
- confidence must be one of: High / Medium / Low
- JSON only. No extra text.

Device models:
- Lantronix MatchPort AR
- Microsystems SPM Client
- Nova Biomedical StatStrip
- Omnicell Automated Dispensing Cabinet
- Philips EarlyVue VS30
- Philips IntelliVue
- Philips IntelliVue MX40
- Philips IntelliVue X3
- Philips PIIC
- Rauland-Borg Responder


--- Prompt B5 (models 41-50) ---

You are a cybersecurity analyst evaluating CPS devices.

For each device model below, identify the operating system if it is publicly documented.

Rules:
- Output must be a JSON array of objects.
- Each object must contain the keys: "model", "operating_system", "confidence".
- If not publicly known, return "Unknown".
- Do NOT speculate.
- If multiple firmware generations exist, return the most common known OS.
- confidence must be one of: High / Medium / Low
- JSON only. No extra text.

Device models:
- Roche Accu-Chek Inform II
- Rockwell Automation PanelView Plus
- Rockwell Automation PanelView Plus 7 Performance 1000
- Schneider Electric AS series
- Schneider Electric AS-P
- Siemens PXC Modular
- Siemens SIMATIC HMI
- Siemens SIMATIC IPC
- Smiths Medical MedFusion 4000
- Swisslog Translogic Tube Station

# Paste JSON -> DataFrame -> accumulate results
import json
import pandas as pd

# Helpers
def json_to_df(json_text: str, expected_keys: list[str]) -> pd.DataFrame:
    """
    Parse a JSON array (list of dicts) from LLM output and validate schema.
    """
    json_text = json_text.strip()

    # Sometimes models wrap JSON in ```json ... ```
    if json_text.startswith("```"):
        json_text = json_text.split("```", 2)[1]  # take inside block
        json_text = json_text.replace("json", "", 1).strip()

    data = json.loads(json_text)

    if not isinstance(data, list):
        raise ValueError("Expected a JSON array (list of objects).")

    # Validate keys
    for i, obj in enumerate(data):
        if not isinstance(obj, dict):
            raise ValueError(f"Item {i} is not an object/dict.")
        missing = set(expected_keys) - set(obj.keys())
        if missing:
            raise ValueError(f"Item {i} missing keys: {missing}. Got keys: {list(obj.keys())}")

    return pd.DataFrame(data)

# Device Type accumulator
if "device_type_results" not in globals():
    device_type_results = pd.DataFrame(columns=["model", "device_type"])

def add_device_type_batch(json_text: str):
    global device_type_results
    batch_df = json_to_df(json_text, expected_keys=["model", "device_type"])

    # normalize
    batch_df["model"] = batch_df["model"].astype(str).str.strip()
    batch_df["device_type"] = batch_df["device_type"].astype(str).str.strip()

    # append + de-dup by model (keep last in case you re-run a batch)
    device_type_results = pd.concat([device_type_results, batch_df], ignore_index=True)
    device_type_results = device_type_results.drop_duplicates(subset=["model"], keep="last").reset_index(drop=True)

    print(f"Added {len(batch_df)} rows. Total device_type_results = {len(device_type_results)}")
    display(device_type_results.tail(10))

# Operating System accumulator
if "os_results" not in globals():
    os_results = pd.DataFrame(columns=["model", "operating_system", "confidence"])

def add_os_batch(json_text: str):
    global os_results
    batch_df = json_to_df(json_text, expected_keys=["model", "operating_system", "confidence"])

    # normalize
    batch_df["model"] = batch_df["model"].astype(str).str.strip()
    batch_df["operating_system"] = batch_df["operating_system"].astype(str).str.strip()
    batch_df["confidence"] = batch_df["confidence"].astype(str).str.strip()

    # append + de-dup by model
    os_results = pd.concat([os_results, batch_df], ignore_index=True)
    os_results = os_results.drop_duplicates(subset=["model"], keep="last").reset_index(drop=True)

    print(f"Added {len(batch_df)} rows. Total os_results = {len(os_results)}")
    display(os_results.tail(10))

# Progress helper
def progress(expected_total=50):
    print("Device types:", len(device_type_results), "/", expected_total)
    print("OS results  :", len(os_results), "/", expected_total)


# Save to CSV
def save_results():
    device_type_results.to_csv("device_type_results.csv", index=False)
    os_results.to_csv("os_results.csv", index=False)
    print("Saved: device_type_results.csv, os_results.csv")

json_text_a1 = """
 [
{"model": "APC 0N-9582PANTERA", "device_type": "Unknown"},
{"model": "APC AP7811B", "device_type": "PDU"},
{"model": "APC AP9630", "device_type": "Network Management Card"},
{"model": "APC AP9631", "device_type": "Network Management Card"},
{"model": "APC ATS", "device_type": "Automatic Transfer Switch"},
{"model": "APC MasterSwitchrPDU", "device_type": "PDU"},
{"model": "APC MasterSwitchrPDU2", "device_type": "PDU"},
{"model": "APC Smart-UPS 1500", "device_type": "UPS"},
{"model": "APC Smart-UPS SMT 1500", "device_type": "UPS"},
{"model": "APC Smart-UPS SRT 2200", "device_type": "UPS"}
]
"""
progress()

json_text_a2 = """

[
{
"model": "APC Smart-UPS SRT 5000",
"device_type": "UPS"
},
{
"model": "Alaris 8015 PC Unit",
"device_type": "Infusion Pump"
},
{
"model": "Alaris 8100 Pump Module",
"device_type": "Infusion Pump"
},
{
"model": "Alaris 8110 Syringe Module",
"device_type": "Syringe Pump"
},
{
"model": "B.Braun Infusomat Space",
"device_type": "Infusion Pump"
},
{
"model": "B.Braun SmartBattery with Wifi",
"device_type": "Battery Module"
},
{
"model": "Baxter Novum IQ",
"device_type": "Infusion Pump"
},
{
"model": "Baxter Sigma Spectrum",
"device_type": "Infusion Pump"
},
{
"model": "Baxter Sigma Spectrum Wireless Module",
"device_type": "Communication Module"
},
{
"model": "Baxter Spectrum IQ",
"device_type": "Infusion Pump"
}
]
"""
progress()

json_text_a3 = """

[
{
"model": "Becton Dickinson Pyxis ES System",
"device_type": "Automated Dispensing Cabinet"
},
{
"model": "Carel PCO1000WB0",
"device_type": "HVAC Controller"
},
{
"model": "Carel pCOWeb Card",
"device_type": "Communication Module"
},
{
"model": "Cerner Connectivity Engine",
"device_type": "Integration Engine"
},
{
"model": "Eaton Tripp Lite Series",
"device_type": "UPS"
},
{
"model": "Hill-Rom NaviCare Room Control Board",
"device_type": "Room Control System"
},
{
"model": "Honeywell ComfortPoint Open CPO-VAV2A",
"device_type": "VAV Controller"
},
{
"model": "Hospira Plum 360",
"device_type": "Infusion Pump"
},
{
"model": "IGEL IGEL Thin Client",
"device_type": "Thin Client"
},
{
"model": "Johnson Controls NAE",
"device_type": "Building Automation Controller"
}
]
"""
progress()

json_text_a4 = """

[
{
"model": "Lantronix MatchPort AR",
"device_type": "Device Server"
},
{
"model": "Microsystems SPM Client",
"device_type": "Unknown"
},
{
"model": "Nova Biomedical StatStrip",
"device_type": "Blood Glucose Meter"
},
{
"model": "Omnicell Automated Dispensing Cabinet",
"device_type": "Automated Dispensing Cabinet"
},
{
"model": "Philips EarlyVue VS30",
"device_type": "Vital Signs Monitor"
},
{
"model": "Philips IntelliVue",
"device_type": "Patient Monitor"
},
{
"model": "Philips IntelliVue MX40",
"device_type": "Telemetry Monitor"
},
{
"model": "Philips IntelliVue X3",
"device_type": "Patient Monitor"
},
{
"model": "Philips PIIC",
"device_type": "Central Monitoring Station"
},
{
"model": "Rauland-Borg Responder",
"device_type": "Nurse Call System"
}
]
"""
progress()

json_text_a5 = """
[
{
"model": "Roche Accu-Chek Inform II",
"device_type": "Blood Glucose Meter"
},
{
"model": "Rockwell Automation PanelView Plus",
"device_type": "HMI"
},
{
"model": "Rockwell Automation PanelView Plus 7 Performance 1000",
"device_type": "HMI"
},
{
"model": "Schneider Electric AS series",
"device_type": "PLC"
},
{
"model": "Schneider Electric AS-P",
"device_type": "PLC"
},
{
"model": "Siemens PXC Modular",
"device_type": "Building Automation Controller"
},
{
"model": "Siemens SIMATIC HMI",
"device_type": "HMI"
},
{
"model": "Siemens SIMATIC IPC",
"device_type": "Industrial PC"
},
{
"model": "Smiths Medical MedFusion 4000",
"device_type": "Infusion Pump"
},
{
"model": "Swisslog Translogic Tube Station",
"device_type": "Pneumatic Tube System"
}
]
"""
progress()

json_text_b1 = """

[
  {
    "model": "APC 0N-9582PANTERA",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "APC AP7811B",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "APC AP9630",
    "operating_system": "APC Operating System (AOS)",
    "confidence": "High"
  },
  {
    "model": "APC AP9631",
    "operating_system": "APC Operating System (AOS)",
    "confidence": "High"
  },
  {
    "model": "APC ATS",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "APC MasterSwitchrPDU",
    "operating_system": "APC Operating System (AOS)",
    "confidence": "Medium"
  },
  {
    "model": "APC MasterSwitchrPDU2",
    "operating_system": "APC Operating System (AOS)",
    "confidence": "Medium"
  },
  {
    "model": "APC Smart-UPS 1500",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "APC Smart-UPS SMT 1500",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "APC Smart-UPS SRT 2200",
    "operating_system": "Unknown",
    "confidence": "Low"
  }
]
"""
progress()

json_text_b2 = """

[
{
"model": "APC Smart-UPS SRT 5000",
"operating_system": "APC Operating System (AOS)",
"confidence": "High"
},
{
"model": "Alaris 8015 PC Unit",
"operating_system": "Alaris Operating System software (v9.x)",
"confidence": "Medium"
},
{
"model": "Alaris 8100 Pump Module",
"operating_system": "Alaris Operating System software (v9.x)",
"confidence": "Medium"
},
{
"model": "Alaris 8110 Syringe Module",
"operating_system": "Alaris Operating System software (v9.x)",
"confidence": "Medium"
},
{
"model": "B.Braun Infusomat Space",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "B.Braun SmartBattery with Wifi",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Baxter Novum IQ",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Baxter Sigma Spectrum",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Baxter Sigma Spectrum Wireless Module",
"operating_system": "Digi NET+OS",
"confidence": "High"
},
{
"model": "Baxter Spectrum IQ",
"operating_system": "Digi NET+OS",
"confidence": "High"
}
]
"""
progress()

json_text_b3 = """
[
  {
    "model": "Becton Dickinson Pyxis ES System",
    "operating_system": "Windows 10 IoT",
    "confidence": "High"
  },
  {
    "model": "Carel PCO1000WB0",
    "operating_system": "Linux",
    "confidence": "Medium"
  },
  {
    "model": "Carel pCOWeb Card",
    "operating_system": "Linux",
    "confidence": "High"
  },
  {
    "model": "Cerner Connectivity Engine",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Eaton Tripp Lite Series",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Hill-Rom NaviCare Room Control Board",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Honeywell ComfortPoint Open CPO-VAV2A",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Hospira Plum 360",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "IGEL IGEL Thin Client",
    "operating_system": "IGEL OS (Linux-based)",
    "confidence": "High"
  },
  {
    "model": "Johnson Controls NAE",
    "operating_system": "Windows Embedded Standard 7 (WES7) SP1",
    "confidence": "High"
  }
]

"""
progress()

json_text_b4 = """

[
{
"model": "Lantronix MatchPort AR",
"operating_system": "Lantronix Evolution OS ",
"confidence": "High"
},
{
"model": "Microsystems SPM Client",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Nova Biomedical StatStrip",
"operating_system": "Windows CE ",
"confidence": "High"
},
{
"model": "Omnicell Automated Dispensing Cabinet",
"operating_system": "Windows 10 ",
"confidence": "Medium"
},
{
"model": "Philips EarlyVue VS30",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Philips IntelliVue",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Philips IntelliVue MX40",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Philips IntelliVue X3",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Philips PIIC",
"operating_system": "Windows Server 2012 R2 ",
"confidence": "High"
},
{
"model": "Rauland-Borg Responder",
"operating_system": "Windows ",
"confidence": "Medium"
}
]
"""
progress()

json_text_b5 = """

[
{
"model": "Roche Accu-Chek Inform II",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Rockwell Automation PanelView Plus",
"operating_system": "Microsoft Windows CE",
"confidence": "High"
},
{
"model": "Rockwell Automation PanelView Plus 7 Performance 1000",
"operating_system": "Windows 10 IoT Core (LTSC)",
"confidence": "High"
},
{
"model": "Schneider Electric AS series",
"operating_system": "Linux",
"confidence": "High"
},
{
"model": "Schneider Electric AS-P",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Siemens PXC Modular",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Siemens SIMATIC HMI",
"operating_system": "Microsoft Windows CE",
"confidence": "High"
},
{
"model": "Siemens SIMATIC IPC",
"operating_system": "Windows",
"confidence": "High"
},
{
"model": "Smiths Medical MedFusion 4000",
"operating_system": "Unknown",
"confidence": "Low"
},
{
"model": "Swisslog Translogic Tube Station",
"operating_system": "Linux",
"confidence": "Medium"
}
]
"""
progress()

Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50
Device types: 0 / 50
OS results  : 0 / 50

add_device_type_batch(json_text_a1)
add_device_type_batch(json_text_a2)
add_device_type_batch(json_text_a3)
add_device_type_batch(json_text_a4)
add_device_type_batch(json_text_a5)
add_os_batch(json_text_b1)
add_os_batch(json_text_b2)
add_os_batch(json_text_b3)
add_os_batch(json_text_b4)
add_os_batch(json_text_b5)

Added 10 rows. Total device_type_results = 10

Added 10 rows. Total device_type_results = 20

Added 10 rows. Total device_type_results = 30

Added 10 rows. Total device_type_results = 40

Added 10 rows. Total device_type_results = 50

Added 10 rows. Total os_results = 10

Added 10 rows. Total os_results = 20

Added 10 rows. Total os_results = 30

Added 10 rows. Total os_results = 40

Added 10 rows. Total os_results = 50

device_type_results.tail(3)
print("Total rows:", len(device_type_results))
print("Unique models:", device_type_results["model"].nunique())

Total rows: 50
Unique models: 50

def save_results():
    device_type_results.to_csv("/content/device_type_results.csv", index=False)
    os_results.to_csv("/content/os_results.csv", index=False)
    print("Saved inside content")

save_results()

Saved inside content

# Gemini OS results + comparison vs ChatGPT
import json
import pandas as pd

def json_to_df(json_text: str, expected_keys: list[str]) -> pd.DataFrame:
    json_text = json_text.strip()

    # handle ```json ... ```
    if json_text.startswith("```"):
        json_text = json_text.split("```", 2)[1]
        json_text = json_text.replace("json", "", 1).strip()

    data = json.loads(json_text)

    if not isinstance(data, list):
        raise ValueError("Expected a JSON array (list of objects).")

    for i, obj in enumerate(data):
        if not isinstance(obj, dict):
            raise ValueError(f"Item {i} is not an object/dict.")
        missing = set(expected_keys) - set(obj.keys())
        if missing:
            raise ValueError(f"Item {i} missing keys: {missing}. Got keys: {list(obj.keys())}")

    return pd.DataFrame(data)

# Gemini Operating System accumulator
if "os_results_gemini" not in globals():
    os_results_gemini = pd.DataFrame(columns=["model", "operating_system", "confidence"])

def add_os_batch_gemini(json_text: str):
    global os_results_gemini
    batch_df = json_to_df(json_text, expected_keys=["model", "operating_system", "confidence"])

    # normalize
    batch_df["model"] = batch_df["model"].astype(str).str.strip()
    batch_df["operating_system"] = batch_df["operating_system"].astype(str).str.strip()
    batch_df["confidence"] = batch_df["confidence"].astype(str).str.strip()

    # append + de-dup by model (keep last)
    os_results_gemini = pd.concat([os_results_gemini, batch_df], ignore_index=True)
    os_results_gemini = os_results_gemini.drop_duplicates(subset=["model"], keep="last").reset_index(drop=True)

    print(f"Added {len(batch_df)} rows. Total os_results_gemini = {len(os_results_gemini)}")
    display(os_results_gemini.tail(10))

# Comparison: ChatGPT vs Gemini (Section b)
def compare_os_chatgpt_vs_gemini():
    if "os_results" not in globals():
        raise ValueError("os_results (ChatGPT) not found. Run your ChatGPT section (b) first.")
    if "os_results_gemini" not in globals() or len(os_results_gemini) == 0:
        raise ValueError("os_results_gemini is empty. Paste Gemini batches first.")

    a = os_results.rename(columns={
        "operating_system": "os_chatgpt",
        "confidence": "conf_chatgpt"
    }).copy()

    b = os_results_gemini.rename(columns={
        "operating_system": "os_gemini",
        "confidence": "conf_gemini"
    }).copy()

    comp = a.merge(b, on="model", how="outer")

    # flags
    comp["both_unknown"] = (comp["os_chatgpt"].fillna("Unknown") == "Unknown") & (comp["os_gemini"].fillna("Unknown") == "Unknown")
    comp["agree_exact"] = (comp["os_chatgpt"].fillna("") == comp["os_gemini"].fillna(""))
    comp["disagree"] = (~comp["agree_exact"]) & (~comp["both_unknown"])

    # simple summary counts
    summary = {
        "total_models_union": int(len(comp)),
        "exact_agreement": int(comp["agree_exact"].sum()),
        "both_unknown": int(comp["both_unknown"].sum()),
        "disagreements_non_unknown": int(comp["disagree"].sum()),
        "chatgpt_known_gemini_unknown": int(((comp["os_chatgpt"].fillna("Unknown") != "Unknown") & (comp["os_gemini"].fillna("Unknown") == "Unknown")).sum()),
        "gemini_known_chatgpt_unknown": int(((comp["os_gemini"].fillna("Unknown") != "Unknown") & (comp["os_chatgpt"].fillna("Unknown") == "Unknown")).sum()),
    }

    # show disagreements table
    disagreements_df = comp[comp["disagree"]].sort_values("model")[["model", "os_chatgpt", "conf_chatgpt", "os_gemini", "conf_gemini"]]

    print("=== Comparison summary (Section b) ===")
    for k, v in summary.items():
        print(f"{k}: {v}")

    display(disagreements_df)
    return comp, disagreements_df, summary

json_text_g1 = """

 [
  {
    "model": "APC 0N-9582PANTERA",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "APC AP7811B",
    "operating_system": "APC OS (AOS)",
    "confidence": "High"
  },
  {
    "model": "APC AP9630",
    "operating_system": "APC OS (AOS)",
    "confidence": "High"
  },
  {
    "model": "APC AP9631",
    "operating_system": "APC OS (AOS)",
    "confidence": "High"
  },
  {
    "model": "APC ATS",
    "operating_system": "APC OS (AOS)",
    "confidence": "High"
  },
  {
    "model": "APC MasterSwitchrPDU",
    "operating_system": "APC OS (AOS)",
    "confidence": "Medium"
  },
  {
    "model": "APC MasterSwitchrPDU2",
    "operating_system": "APC OS (AOS)",
    "confidence": "Medium"
  },
  {
    "model": "APC Smart-UPS 1500",
    "operating_system": "Proprietary Firmware",
    "confidence": "High"
  },
  {
    "model": "APC Smart-UPS SMT 1500",
    "operating_system": "Proprietary Firmware",
    "confidence": "High"
  },
  {
    "model": "APC Smart-UPS SRT 2200",
    "operating_system": "Proprietary Firmware",
    "confidence": "High"
  }
]
"""
json_text_g2 = """
[
  {
    "model": "APC Smart-UPS SRT 5000",
    "operating_system": "APC OS (Proprietary)",
    "confidence": "High"
  },
  {
    "model": "Alaris 8015 PC Unit",
    "operating_system": "Windows CE",
    "confidence": "High"
  },
  {
    "model": "Alaris 8100 Pump Module",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Alaris 8110 Syringe Module",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "B.Braun Infusomat Space",
    "operating_system": "Embedded Linux",
    "confidence": "High"
  },
  {
    "model": "B.Braun SmartBattery with Wifi",
    "operating_system": "Embedded Linux",
    "confidence": "Medium"
  },
  {
    "model": "Baxter Novum IQ",
    "operating_system": "Embedded Linux",
    "confidence": "High"
  },
  {
    "model": "Baxter Sigma Spectrum",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Baxter Sigma Spectrum Wireless Module",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Baxter Spectrum IQ",
    "operating_system": "Embedded Linux",
    "confidence": "High"
  }
]
"""
json_text_g3 = """

[
  {
    "model": "Becton Dickinson Pyxis ES System",
    "operating_system": "Windows 10 IoT Enterprise",
    "confidence": "High"
  },
  {
    "model": "Carel PCO1000WB0",
    "operating_system": "FreeRTOS",
    "confidence": "Medium"
  },
  {
    "model": "Carel pCOWeb Card",
    "operating_system": "Linux",
    "confidence": "High"
  },
  {
    "model": "Cerner Connectivity Engine",
    "operating_system": "Linux",
    "confidence": "Medium"
  },
  {
    "model": "Eaton Tripp Lite Series",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Hill-Rom NaviCare Room Control Board",
    "operating_system": "Windows Embedded Compact",
    "confidence": "Medium"
  },
  {
    "model": "Honeywell ComfortPoint Open CPO-VAV2A",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Hospira Plum 360",
    "operating_system": "VxWorks",
    "confidence": "High"
  },
  {
    "model": "IGEL IGEL Thin Client",
    "operating_system": "IGEL OS (Linux-based)",
    "confidence": "High"
  },
  {
    "model": "Johnson Controls NAE",
    "operating_system": "Windows Embedded",
    "confidence": "High"
  }
]
"""
json_text_g4 = """

[
  {
    "model": "Lantronix MatchPort AR",
    "operating_system": "Evolution OS",
    "confidence": "High"
  },
  {
    "model": "Microsystems SPM Client",
    "operating_system": "Windows",
    "confidence": "High"
  },
  {
    "model": "Nova Biomedical StatStrip",
    "operating_system": "Unknown",
    "confidence": "High"
  },
  {
    "model": "Omnicell Automated Dispensing Cabinet",
    "operating_system": "Windows 10 IoT Enterprise",
    "confidence": "High"
  },
  {
    "model": "Philips EarlyVue VS30",
    "operating_system": "Linux",
    "confidence": "High"
  },
  {
    "model": "Philips IntelliVue",
    "operating_system": "Proprietary RTOS",
    "confidence": "Medium"
  },
  {
    "model": "Philips IntelliVue MX40",
    "operating_system": "Unknown",
    "confidence": "High"
  },
  {
    "model": "Philips IntelliVue X3",
    "operating_system": "Unknown",
    "confidence": "High"
  },
  {
    "model": "Philips PIIC",
    "operating_system": "Windows Server",
    "confidence": "High"
  },
  {
    "model": "Rauland-Borg Responder",
    "operating_system": "Unknown",
    "confidence": "High"
  }
]
"""
json_text_g5 = """
[
  {
    "model": "Roche Accu-Chek Inform II",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Rockwell Automation PanelView Plus",
    "operating_system": "Windows CE",
    "confidence": "High"
  },
  {
    "model": "Rockwell Automation PanelView Plus 7 Performance 1000",
    "operating_system": "Windows 10 IoT Core",
    "confidence": "High"
  },
  {
    "model": "Schneider Electric AS series",
    "operating_system": "Linux",
    "confidence": "Medium"
  },
  {
    "model": "Schneider Electric AS-P",
    "operating_system": "Linux",
    "confidence": "High"
  },
  {
    "model": "Siemens PXC Modular",
    "operating_system": "Nucleus RTOS",
    "confidence": "High"
  },
  {
    "model": "Siemens SIMATIC HMI",
    "operating_system": "Windows CE",
    "confidence": "Medium"
  },
  {
    "model": "Siemens SIMATIC IPC",
    "operating_system": "Windows 10 Enterprise LTSC",
    "confidence": "High"
  },
  {
    "model": "Smiths Medical MedFusion 4000",
    "operating_system": "Unknown",
    "confidence": "Low"
  },
  {
    "model": "Swisslog Translogic Tube Station",
    "operating_system": "Linux",
    "confidence": "High"
  }
]
"""

add_os_batch_gemini(json_text_g1)
add_os_batch_gemini(json_text_g2)
add_os_batch_gemini(json_text_g3)
add_os_batch_gemini(json_text_g4)
add_os_batch_gemini(json_text_g5)

Added 10 rows. Total os_results_gemini = 10

Added 10 rows. Total os_results_gemini = 20

Added 10 rows. Total os_results_gemini = 30

Added 10 rows. Total os_results_gemini = 40

Added 10 rows. Total os_results_gemini = 50

os_results_gemini.tail()

# Save Gemini OS table
os_results_gemini.to_csv("/content/os_results_gemini.csv", index=False)
print("Saved: /content/os_results_gemini.csv")

Saved: /content/os_results_gemini.csv

# Comparison + Export CSVs

import pandas as pd

def normalize_os_name(x: str) -> str:
    """
    Light normalization ONLY for trivial naming variants.
    Keeps OS family/version differences as real disagreements.
    """
    if pd.isna(x):
        return "unknown"

    s = str(x).strip().lower()

    # canonical unknown
    if s in {"unknown", "unk", "n/a", "na", ""}:
        return "unknown"

    # trivial vendor-prefix normalizations
    s = s.replace("microsoft ", "")

    # APC naming variants
    s = s.replace("apc os", "apc operating system")

    # common abbreviation (minimal + safe)
    s = s.replace("wes7", "windows embedded standard 7")

    # collapse whitespace
    s = " ".join(s.split())
    return s

def compare_os_chatgpt_vs_gemini(save_csv=True):
    # safety checks
    if "os_results" not in globals():
        raise ValueError("os_results (ChatGPT) not found. Run Section (b) first.")
    if "os_results_gemini" not in globals() or len(os_results_gemini) == 0:
        raise ValueError("os_results_gemini is empty. Paste Gemini batches first.")

    # rename columns
    a = os_results.rename(columns={"operating_system": "os_chatgpt", "confidence": "conf_chatgpt"}).copy()
    b = os_results_gemini.rename(columns={"operating_system": "os_gemini", "confidence": "conf_gemini"}).copy()

    # merge
    comp = a.merge(b, on="model", how="outer")

    # normalize for a "soft" comparison
    comp["os_chatgpt_norm"] = comp["os_chatgpt"].apply(normalize_os_name)
    comp["os_gemini_norm"] = comp["os_gemini"].apply(normalize_os_name)

    # flags
    comp["both_unknown"] = (comp["os_chatgpt_norm"] == "unknown") & (comp["os_gemini_norm"] == "unknown")
    comp["agree_soft"] = comp["os_chatgpt_norm"] == comp["os_gemini_norm"]
    comp["disagree_non_unknown"] = (~comp["agree_soft"]) & (~comp["both_unknown"])

    # summary
    summary = pd.DataFrame([{
        "total_models": int(len(comp)),
        "agree_soft": int(comp["agree_soft"].sum()),
        "both_unknown": int(comp["both_unknown"].sum()),
        "disagree_non_unknown": int(comp["disagree_non_unknown"].sum()),
        "chatgpt_known_gemini_unknown": int(((comp["os_chatgpt_norm"] != "unknown") & (comp["os_gemini_norm"] == "unknown")).sum()),
        "gemini_known_chatgpt_unknown": int(((comp["os_gemini_norm"] != "unknown") & (comp["os_chatgpt_norm"] == "unknown")).sum()),
    }])

    # disagreements table
    disagreements = comp[comp["disagree_non_unknown"]].copy()
    disagreements = disagreements[["model", "os_chatgpt", "conf_chatgpt", "os_gemini", "conf_gemini"]].sort_values("model")

    # agreements where both known
    agreements_known = comp[(comp["agree_soft"]) & (~comp["both_unknown"])].copy()
    agreements_known = agreements_known[["model", "os_chatgpt", "conf_chatgpt", "os_gemini", "conf_gemini"]].sort_values("model")

    print("=== Summary ===")
    display(summary)

    print("=== Disagreements (non-Unknown) ===")
    display(disagreements)

    if save_csv:
        comp.to_csv("/content/os_compare_full.csv", index=False)
        disagreements.to_csv("/content/os_compare_disagreements.csv", index=False)
        agreements_known.to_csv("/content/os_compare_agreements_known.csv", index=False)
        print("Saved: os_compare_full.csv, os_compare_disagreements.csv, os_compare_agreements_known.csv")

    return comp, disagreements, summary, agreements_known

# run comparison + export
comp_df, disagreements_df, summary_df, agreements_known_df = compare_os_chatgpt_vs_gemini(save_csv=True)

=== Summary ===

=== Disagreements (non-Unknown) ===

Saved: os_compare_full.csv, os_compare_disagreements.csv, os_compare_agreements_known.csv

# Statistics + significance tests (ChatGPT vs Gemini)
import numpy as np
import pandas as pd

def conf_to_score(c):
    if pd.isna(c):
        return np.nan
    c = str(c).strip().lower()
    return {"low": 1, "medium": 2, "high": 3}.get(c, np.nan)

def mcnemar_exact(b, c):
    """
    Exact McNemar (two-sided) using Binomial(n=b+c, p=0.5) on min(b,c).
    No scipy needed.
    """
    n = b + c
    if n == 0:
        return np.nan
    k = min(b, c)

    # compute 2*P(X <= k) under Bin(n, 0.5)
    # using stable summation of comb * (0.5^n)
    from math import comb
    p_le_k = sum(comb(n, i) for i in range(k + 1)) / (2 ** n)
    p_two = min(1.0, 2 * p_le_k)
    return p_two

def compute_stats(comp_df: pd.DataFrame):
    df = comp_df.copy()

    # normalize missing
    df["os_chatgpt"] = df["os_chatgpt"].fillna("Unknown")
    df["os_gemini"]  = df["os_gemini"].fillna("Unknown")
    df["conf_chatgpt"] = df["conf_chatgpt"].fillna("")
    df["conf_gemini"]  = df["conf_gemini"].fillna("")

    # known/unknown flags
    df["chatgpt_known"] = df["os_chatgpt_norm"].ne("unknown")
    df["gemini_known"]  = df["os_gemini_norm"].ne("unknown")


    # use soft agreement flag if exists; else define
    if "agree_soft" not in df.columns:
        # fall back to exact
        df["agree_soft"] = (df["os_chatgpt"] == df["os_gemini"])

    # core rates
    total = len(df)
    agree_rate = df["agree_soft"].mean()
    both_unknown_rate = ((~df["chatgpt_known"]) & (~df["gemini_known"])).mean()
    disagree_non_unknown_rate = ((~df["agree_soft"]) & (df["chatgpt_known"] | df["gemini_known"])).mean()

    chatgpt_known_rate = df["chatgpt_known"].mean()
    gemini_known_rate  = df["gemini_known"].mean()

    # who provides more "non-Unknown" answers?
    # McNemar on paired binary outcomes: Known vs Unknown
    # b = ChatGPT known & Gemini unknown
    # c = ChatGPT unknown & Gemini known
    b = int((df["chatgpt_known"] & ~df["gemini_known"]).sum())
    c = int((~df["chatgpt_known"] & df["gemini_known"]).sum())
    p_mcnemar = mcnemar_exact(b, c)

    # confidence stats
    df["conf_chatgpt_score"] = df["conf_chatgpt"].apply(conf_to_score)
    df["conf_gemini_score"]  = df["conf_gemini"].apply(conf_to_score)

    # Avg confidence where OS is known
    avg_conf_chatgpt_known = df.loc[df["chatgpt_known"], "conf_chatgpt_score"].mean()
    avg_conf_gemini_known  = df.loc[df["gemini_known"], "conf_gemini_score"].mean()

    # High-confidence coverage (among all models)
    chatgpt_high_rate = (df["conf_chatgpt"].str.strip().str.lower() == "high").mean()
    gemini_high_rate  = (df["conf_gemini"].str.strip().str.lower() == "high").mean()

    # agreement breakdown
    # Agreement only among cases where BOTH give a known OS
    both_known = df[df["chatgpt_known"] & df["gemini_known"]].copy()
    agree_among_both_known = both_known["agree_soft"].mean() if len(both_known) else np.nan

    # output tables
    stats = pd.DataFrame([{
        "total_models": total,
        "agreement_rate_soft": round(agree_rate * 100, 1),
        "both_unknown_rate": round(both_unknown_rate * 100, 1),
        "disagree_non_unknown_rate": round(disagree_non_unknown_rate * 100, 1),
        "chatgpt_known_rate": round(chatgpt_known_rate * 100, 1),
        "gemini_known_rate": round(gemini_known_rate * 100, 1),
        "agreement_rate_among_both_known": (round(agree_among_both_known * 100, 1) if not np.isnan(agree_among_both_known) else np.nan),
        "avg_conf_chatgpt_when_known": round(avg_conf_chatgpt_known, 2),
        "avg_conf_gemini_when_known": round(avg_conf_gemini_known, 2),
        "high_conf_rate_chatgpt": round(chatgpt_high_rate * 100, 1),
        "high_conf_rate_gemini": round(gemini_high_rate * 100, 1),
        "mcnemar_b_chatgpt_known_gemini_unknown": b,
        "mcnemar_c_chatgpt_unknown_gemini_known": c,
        "mcnemar_p_value_two_sided": p_mcnemar
    }])

    # A small “who wins where” table
    coverage_tbl = pd.DataFrame({
        "case": [
            "Both Known",
            "Both Unknown",
            "ChatGPT Known, Gemini Unknown",
            "Gemini Known, ChatGPT Unknown"
        ],
        "count": [
            int((df["chatgpt_known"] & df["gemini_known"]).sum()),
            int((~df["chatgpt_known"] & ~df["gemini_known"]).sum()),
            b,
            c
        ]
    })
    coverage_tbl["percent"] = (coverage_tbl["count"] / total * 100).round(1)

    print("=== Key statistics (percentages + confidence) ===")
    display(stats)

    print("=== Coverage breakdown ===")
    display(coverage_tbl)

    # show top disagreements where one model is High confidence
    df_dis = df[(~df["agree_soft"]) & (df["chatgpt_known"] | df["gemini_known"])].copy()
    df_dis["chatgpt_high"] = df["conf_chatgpt"].str.lower().eq("high")
    df_dis["gemini_high"]  = df["conf_gemini"].str.lower().eq("high")
    interesting = df_dis[(df_dis["chatgpt_high"] | df_dis["gemini_high"])][
        ["model","os_chatgpt","conf_chatgpt","os_gemini","conf_gemini"]
    ].sort_values("model")

    print("=== Disagreements where at least one model is High confidence ===")
    display(interesting)

    return stats, coverage_tbl, interesting

stats_df, coverage_df, interesting_disagreements_df = compute_stats(comp_df)

=== Key statistics (percentages + confidence) ===

=== Coverage breakdown ===

=== Disagreements where at least one model is High confidence ===

def plot_coverage_breakdown(coverage_df):
    plt.figure(figsize=(7,4))
    plt.bar(coverage_df["case"], coverage_df["percent"])
    plt.ylabel("Percent (%)")
    plt.title("Coverage Breakdown: ChatGPT vs Gemini")
    plt.xticks(rotation=25)
    plt.tight_layout()
    plt.show()

plot_coverage_breakdown(coverage_df)

# Load devices.csv
import pandas as pd
import sqlite3

# load csv from content
devices = pd.read_csv("/content/devices.csv")

# create sqlite database in memory
conn = sqlite3.connect(":memory:")

# write table
devices.to_sql("devices", conn, index=False, if_exists="replace")

print("Table loaded successfully")

# quick data preview
display(devices.head())

print("Shape:", devices.shape)
print("Columns:", list(devices.columns))

Table loaded successfully

Shape: (4987, 19)
Columns: ['connection_type', 'ip', 'mac', 'device_id', 'category', 'sub_category', 'manufacturer', 'type', 'model', 'serial_number', 'os', 'sw_fw_version', 'hw_version', 'vlan', 'connector_device_id', 'ap_name', 'ap_location', 'risk_score_points', 'risk_score']

query_q1 = """
SELECT
    model,
    sw_fw_version,
    COUNT(*) AS device_count
FROM devices
WHERE type = 'Infusion Pump Module'
  AND sw_fw_version IS NOT NULL
  AND TRIM(sw_fw_version) != ''
GROUP BY model, sw_fw_version
ORDER BY model, device_count DESC, sw_fw_version;
"""
q1_results = pd.read_sql_query(query_q1, conn)
q1_results

query_obsolete = """
WITH version_counts AS (
    SELECT
        model,
        sw_fw_version,
        COUNT(*) AS device_count
    FROM devices
    WHERE type = 'Infusion Pump Module'
    GROUP BY model, sw_fw_version
),

ranked_versions AS (
    SELECT
        model,
        sw_fw_version,
        device_count,
        RANK() OVER (
            PARTITION BY model
            ORDER BY device_count DESC
        ) AS version_rank
    FROM version_counts
)

SELECT
    model,
    sw_fw_version,
    device_count
FROM ranked_versions
WHERE version_rank > 1
ORDER BY model, device_count DESC;
"""

obsolete_results = pd.read_sql_query(query_obsolete, conn)
obsolete_results

# SQL query – VLAN IoT vs IT ratio
query_vlan_ratio = """
SELECT
    vlan,
    SUM(CASE WHEN category = 'IoT' THEN 1 ELSE 0 END) AS iot_count,
    SUM(CASE WHEN category = 'IT' THEN 1 ELSE 0 END) AS it_count,
    ROUND(
        CAST(SUM(CASE WHEN category = 'IoT' THEN 1 ELSE 0 END) AS FLOAT) /
        NULLIF(SUM(CASE WHEN category = 'IT' THEN 1 ELSE 0 END), 0),
        2
    ) AS iot_to_it_ratio
FROM devices
GROUP BY vlan
ORDER BY iot_to_it_ratio DESC;
"""

vlan_ratio_results = pd.read_sql_query(query_vlan_ratio, conn)
vlan_ratio_results

	vuln_id	cvss_base_score	exploit_available
0	CVE-2024-1000	2.8	False
1	CVE-2024-1001	7.7	False
2	CVE-2024-1002	3.9	False
3	CVE-2024-1003	5.2	True
4	CVE-2024-1004	3.3	False

	asset_id	asset_type	asset_category	cvss_sum	cvss_sum_after	new_risk_score	new_risk_score_after	delta_new_score
8239	asset_7993	PC	IT	509.9	422.4	99.994	99.969	-0.025
2603	asset_292	Laptop	IT	495.3	415.7	99.992	99.964	-0.028
8342	asset_8085	Server	IT	464.2	411.9	99.986	99.962	-0.024
9456	asset_9088	Laptop	IT	453.6	410.5	99.983	99.961	-0.022
1133	asset_1597	PC	IT	498.3	410.4	99.993	99.960	-0.033
5017	asset_5092	Laptop	IT	485.9	407.9	99.991	99.959	-0.032
624	asset_1138	Laptop	IT	471.9	407.1	99.988	99.958	-0.030
4236	asset_439	Server	IT	462.1	403.9	99.985	99.955	-0.030
2077	asset_2446	Laptop	IT	468.2	404.2	99.987	99.955	-0.032
9674	asset_9284	Server	IT	490.4	402.2	99.991	99.954	-0.037

	asset_id	asset_type	asset_category	cvss_sum	cvss_sum_after	new_risk_score	new_risk_score_after	delta_new_score
7119	asset_6985	Server	IT	44.1	5.6	56.913	10.140	-46.773
480	asset_10429	PC	IT	42.5	10.5	55.576	18.165	-37.411
6317	asset_6262	PC	IT	36.9	7.9	50.564	14.000	-36.564
2490	asset_2818	PC	IT	29.4	4.2	42.953	7.705	-35.248
2206	asset_2562	Server	IT	22.1	0.0	34.422	0.000	-34.422
2545	asset_2868	PC	IT	39.4	11.0	52.868	18.942	-33.926
3615	asset_3830	PC	IT	20.3	0.0	32.129	0.000	-32.129
3572	asset_3792	Laptop	IT	19.8	0.0	31.478	0.000	-31.478
3803	asset_40	Laptop	IT	35.1	10.4	48.835	18.009	-30.826
5002	asset_5079	Laptop	IT	34.3	10.0	48.048	17.380	-30.668

	threshold	% before >= t	% after >= t	Δ (after-before)
0	20	49.77	41.62	-8.15
1	40	29.94	22.94	-7.00
2	60	18.24	12.98	-5.25
3	80	10.01	6.77	-3.24

	n_assets	mean_before	mean_after	median_before	median_after	pct_improved	pct_high_before	pct_high_after	mean_delta	median_delta	high_delta
asset_category
IT	7663	31.498	27.627	19.866	16.428	43.051	9.709	7.386	-3.871	-3.438	-2.323
Medical	2805	31.828	20.920	19.251	10.140	100.000	10.838	5.098	-10.907	-9.111	-5.740

Task 1 – Q1: Current Risk Framework (Legacy Risk)¶

Q1.1 – Calculate Legacy Risk Score¶

Q1.2 – Plot distribution & identify distribution shape¶

Q1.3 – Normality Test & Is the Mean Representative?¶

Q2: New Risk Formula¶

1. Proposed Risk Formula¶

2. How many assets reach the maximum risk score?¶

Q3: Top 10 Riskiest Devices — Legacy vs. New Ranking¶

Analysis and Interpretation¶

Q3 – Simulation (Task 1): Residual Risk After Remediation¶

Results¶

Sanity Check: Validation of Remediation Coverage¶

Q3 – Simulation (Task 2): Distributional Impact of Remediation¶

1. Overall Shift in the Distribution¶

2. Effect on High-Risk Assets¶

3. Scatter Plot Interpretation (Before vs After)¶

4. Distribution-Level View (CDF)¶

5. Operational Threshold Impact¶

6. Conclusion¶

Task 2 – LLM-based CPS Model Enrichment¶

(a) Device Type and OS Identification – Workflow Setup¶

Section (c): Comparing Gemini vs ChatGPT on OS identification¶

Interpretation of Results¶

Task 3 – SQL (Question 1)¶

Interpretation¶

Interpretation¶

Answer to the Question¶

VLAN Analysis – IoT vs IT Ratio¶

Interpretation of the Results¶

	asset_id	asset_type	asset_category
0	asset_0	PC	IT
1	asset_1	Laptop	IT
2	asset_2	PC	IT
3	asset_3	Infusion Pump	Medical
4	asset_4	Laptop	IT

	asset_id	vuln_id
0	asset_0	CVE-2024-1251
1	asset_1	CVE-2024-1074
2	asset_1	CVE-2024-1473
3	asset_1	CVE-2024-1513
4	asset_1	CVE-2024-1361

	asset_id	cvss_sum	legacy_risk_score	asset_type	asset_category
0	asset_0	5.6	5.6	PC	IT
1	asset_1	51.1	51.1	Laptop	IT
2	asset_10	10.0	10.0	Laptop	IT
3	asset_100	6.6	6.6	PC	IT
4	asset_1000	3.4	3.4	Server	IT

	asset_id	asset_type	asset_category	cvss_sum	legacy_risk_score	new_risk_score
0	asset_10000	Laptop	IT	132.5	100.0	92.031
1	asset_10005	Laptop	IT	102.1	100.0	85.762
2	asset_10028	PC	IT	341.2	100.0	99.852
3	asset_10036	PC	IT	509.6	100.0	99.994
4	asset_10045	Laptop	IT	178.7	100.0	96.702
5	asset_10056	PC	IT	133.0	100.0	92.107
6	asset_10058	ECG	Medical	183.1	100.0	96.967
7	asset_10063	Server	IT	108.0	100.0	87.279
8	asset_10073	Server	IT	124.5	100.0	90.716
9	asset_10075	Server	IT	364.3	100.0	99.905

	asset_id	asset_type	asset_category	cvss_sum	legacy_risk_score	new_risk_score
0	asset_5818	PC	IT	522.0	100.0	99.995
1	asset_7817	PC	IT	518.2	100.0	99.995
2	asset_7993	PC	IT	509.9	100.0	99.994
3	asset_10036	PC	IT	509.6	100.0	99.994
4	asset_3138	Server	IT	509.0	100.0	99.994
5	asset_5850	PC	IT	506.2	100.0	99.994
6	asset_3444	PC	IT	506.1	100.0	99.994
7	asset_9947	PC	IT	503.2	100.0	99.993
8	asset_9166	ECG	Medical	501.9	100.0	99.993
9	asset_10344	MRI Scanner	Medical	501.7	100.0	99.993

	metric	before	after	delta (after-before)
0	mean	31.586	25.830	-5.757
1	median	19.789	14.328	-5.461
2	p10	6.285	3.930	-2.355
3	p25	9.451	7.175	-2.276
4	p50	19.789	14.328	-5.461
5	p75	47.449	37.239	-10.210
6	p90	80.000	68.616	-11.384
7	p95	93.755	86.443	-7.312

	vendor	n_models
0	APC	11
1	Philips	5
2	Baxter	4
3	Siemens	3
4	Alaris	3
5	Schneider Electric	2
6	Rockwell Automation	2
7	B.Braun	2
8	Carel	2
9	Hill-Rom	1
10	Eaton	1
11	Cerner	1
12	Becton Dickinson	1
13	Honeywell	1
14	Hospira	1
15	IGEL	1
16	Johnson Controls	1
17	Omnicell	1
18	Nova Biomedical	1
19	Microsystems	1
20	Lantronix	1
21	Roche	1
22	Rauland-Borg	1
23	Smiths	1
24	Swisslog	1

	model	model_clean	vendor
0	APC 0N-9582PANTERA	APC 0N-9582PANTERA	APC
1	APC AP7811B	APC AP7811B	APC
2	APC AP9630	APC AP9630	APC
3	APC AP9631	APC AP9631	APC
4	APC ATS	APC ATS	APC
5	APC MasterSwitchrPDU	APC MasterSwitchrPDU	APC
6	APC MasterSwitchrPDU2	APC MasterSwitchrPDU2	APC
7	APC Smart-UPS 1500	APC Smart-UPS 1500	APC
8	APC Smart-UPS SMT 1500	APC Smart-UPS SMT 1500	APC
9	APC Smart-UPS SRT 2200	APC Smart-UPS SRT 2200	APC

	model	device_type
0	APC 0N-9582PANTERA	Unknown
1	APC AP7811B	PDU
2	APC AP9630	Network Management Card
3	APC AP9631	Network Management Card
4	APC ATS	Automatic Transfer Switch
5	APC MasterSwitchrPDU	PDU
6	APC MasterSwitchrPDU2	PDU
7	APC Smart-UPS 1500	UPS
8	APC Smart-UPS SMT 1500	UPS
9	APC Smart-UPS SRT 2200	UPS

	model	device_type
10	APC Smart-UPS SRT 5000	UPS
11	Alaris 8015 PC Unit	Infusion Pump
12	Alaris 8100 Pump Module	Infusion Pump
13	Alaris 8110 Syringe Module	Syringe Pump
14	B.Braun Infusomat Space	Infusion Pump
15	B.Braun SmartBattery with Wifi	Battery Module
16	Baxter Novum IQ	Infusion Pump
17	Baxter Sigma Spectrum	Infusion Pump
18	Baxter Sigma Spectrum Wireless Module	Communication Module
19	Baxter Spectrum IQ	Infusion Pump

	connection_type	ip	mac	device_id	category	sub_category	manufacturer	type	model	serial_number	os	sw_fw_version	hw_version	vlan	connector_device_id	ap_name	ap_location	risk_score_points	risk_score
0	Ethernet Connection	10.5.29.222	0A:65:BC:EB:F0:9E	CQPBBPDHSD	Medical	Imaging	GE	PACS	Centricity Enterprise Archive	NaN	NaN	4.120.10324.12154	NaN	205	NaN	NaN	NaN	47	Medium
1	Ethernet Connection	10.5.29.221	0A:65:BC:FC:A5:0B	CQPBCZSDMX	Medical	Imaging	GE	PACS	Centricity Enterprise Archive	NaN	NaN	4.120.10324.12154	NaN	205	NaN	NaN	NaN	49	Medium
2	Ethernet Connection	10.5.29.220	0A:65:BC:00:4E:58	CQPBEKGZHR	Medical	Imaging	GE	PACS	Centricity Enterprise Archive	NaN	NaN	4.120.10324.12154	NaN	205	NaN	NaN	NaN	56	Medium
3	Ethernet Connection	10.5.29.245	0A:65:BC:08:FB:B0	CQPBMYINOH	Medical	Patient Devices	Welch Allyn	Vital Signs Monitor Gateway	Connex	KQTACC2E	Windows	NaN	NaN	205	NaN	NaN	NaN	39	Very Low
4	Ethernet Connection	10.5.29.219	0A:65:BC:CF:FA:FE	CQPBRBKKLV	Medical	Imaging	GE	PACS	Centricity Enterprise Archive	NaN	NaN	4.120.10324.12154	NaN	205	NaN	NaN	NaN	73	Critical

	model	device_type
20	Becton Dickinson Pyxis ES System	Automated Dispensing Cabinet
21	Carel PCO1000WB0	HVAC Controller
22	Carel pCOWeb Card	Communication Module
23	Cerner Connectivity Engine	Integration Engine
24	Eaton Tripp Lite Series	UPS
25	Hill-Rom NaviCare Room Control Board	Room Control System
26	Honeywell ComfortPoint Open CPO-VAV2A	VAV Controller
27	Hospira Plum 360	Infusion Pump
28	IGEL IGEL Thin Client	Thin Client
29	Johnson Controls NAE	Building Automation Controller

	model	device_type
30	Lantronix MatchPort AR	Device Server
31	Microsystems SPM Client	Unknown
32	Nova Biomedical StatStrip	Blood Glucose Meter
33	Omnicell Automated Dispensing Cabinet	Automated Dispensing Cabinet
34	Philips EarlyVue VS30	Vital Signs Monitor
35	Philips IntelliVue	Patient Monitor
36	Philips IntelliVue MX40	Telemetry Monitor
37	Philips IntelliVue X3	Patient Monitor
38	Philips PIIC	Central Monitoring Station
39	Rauland-Borg Responder	Nurse Call System

	model	device_type
40	Roche Accu-Chek Inform II	Blood Glucose Meter
41	Rockwell Automation PanelView Plus	HMI
42	Rockwell Automation PanelView Plus 7 Performan...	HMI
43	Schneider Electric AS series	PLC
44	Schneider Electric AS-P	PLC
45	Siemens PXC Modular	Building Automation Controller
46	Siemens SIMATIC HMI	HMI
47	Siemens SIMATIC IPC	Industrial PC
48	Smiths Medical MedFusion 4000	Infusion Pump
49	Swisslog Translogic Tube Station	Pneumatic Tube System

	model	operating_system	confidence
10	APC Smart-UPS SRT 5000	APC Operating System (AOS)	High
11	Alaris 8015 PC Unit	Alaris Operating System software (v9.x)	Medium
12	Alaris 8100 Pump Module	Alaris Operating System software (v9.x)	Medium
13	Alaris 8110 Syringe Module	Alaris Operating System software (v9.x)	Medium
14	B.Braun Infusomat Space	Unknown	Low
15	B.Braun SmartBattery with Wifi	Unknown	Low
16	Baxter Novum IQ	Unknown	Low
17	Baxter Sigma Spectrum	Unknown	Low
18	Baxter Sigma Spectrum Wireless Module	Digi NET+OS	High
19	Baxter Spectrum IQ	Digi NET+OS	High

	model	operating_system	confidence
20	Becton Dickinson Pyxis ES System	Windows 10 IoT	High
21	Carel PCO1000WB0	Linux	Medium
22	Carel pCOWeb Card	Linux	High
23	Cerner Connectivity Engine	Unknown	Low
24	Eaton Tripp Lite Series	Unknown	Low
25	Hill-Rom NaviCare Room Control Board	Unknown	Low
26	Honeywell ComfortPoint Open CPO-VAV2A	Unknown	Low
27	Hospira Plum 360	Unknown	Low
28	IGEL IGEL Thin Client	IGEL OS (Linux-based)	High
29	Johnson Controls NAE	Windows Embedded Standard 7 (WES7) SP1	High

	model	operating_system	confidence
30	Lantronix MatchPort AR	Lantronix Evolution OS	High
31	Microsystems SPM Client	Unknown	Low
32	Nova Biomedical StatStrip	Windows CE	High
33	Omnicell Automated Dispensing Cabinet	Windows 10	Medium
34	Philips EarlyVue VS30	Unknown	Low
35	Philips IntelliVue	Unknown	Low
36	Philips IntelliVue MX40	Unknown	Low
37	Philips IntelliVue X3	Unknown	Low
38	Philips PIIC	Windows Server 2012 R2	High
39	Rauland-Borg Responder	Windows	Medium

	model	operating_system	confidence
10	APC Smart-UPS SRT 5000	APC OS (Proprietary)	High
11	Alaris 8015 PC Unit	Windows CE	High
12	Alaris 8100 Pump Module	Unknown	Low
13	Alaris 8110 Syringe Module	Unknown	Low
14	B.Braun Infusomat Space	Embedded Linux	High
15	B.Braun SmartBattery with Wifi	Embedded Linux	Medium
16	Baxter Novum IQ	Embedded Linux	High
17	Baxter Sigma Spectrum	Unknown	Low
18	Baxter Sigma Spectrum Wireless Module	Unknown	Low
19	Baxter Spectrum IQ	Embedded Linux	High

	model	operating_system	confidence
20	Becton Dickinson Pyxis ES System	Windows 10 IoT Enterprise	High
21	Carel PCO1000WB0	FreeRTOS	Medium
22	Carel pCOWeb Card	Linux	High
23	Cerner Connectivity Engine	Linux	Medium
24	Eaton Tripp Lite Series	Unknown	Low
25	Hill-Rom NaviCare Room Control Board	Windows Embedded Compact	Medium
26	Honeywell ComfortPoint Open CPO-VAV2A	Unknown	Low
27	Hospira Plum 360	VxWorks	High
28	IGEL IGEL Thin Client	IGEL OS (Linux-based)	High
29	Johnson Controls NAE	Windows Embedded	High

	model	operating_system	confidence
30	Lantronix MatchPort AR	Evolution OS	High
31	Microsystems SPM Client	Windows	High
32	Nova Biomedical StatStrip	Unknown	High
33	Omnicell Automated Dispensing Cabinet	Windows 10 IoT Enterprise	High
34	Philips EarlyVue VS30	Linux	High
35	Philips IntelliVue	Proprietary RTOS	Medium
36	Philips IntelliVue MX40	Unknown	High
37	Philips IntelliVue X3	Unknown	High
38	Philips PIIC	Windows Server	High
39	Rauland-Borg Responder	Unknown	High

	model	operating_system	confidence
45	Siemens PXC Modular	Nucleus RTOS	High
46	Siemens SIMATIC HMI	Windows CE	Medium
47	Siemens SIMATIC IPC	Windows 10 Enterprise LTSC	High
48	Smiths Medical MedFusion 4000	Unknown	Low
49	Swisslog Translogic Tube Station	Linux	High

	model	os_chatgpt	conf_chatgpt	os_gemini	conf_gemini
1	APC AP7811B	Unknown	Low	APC OS (AOS)	High
4	APC ATS	Unknown	Low	APC OS (AOS)	High
7	APC Smart-UPS 1500	Unknown	Low	Proprietary Firmware	High
8	APC Smart-UPS SMT 1500	Unknown	Low	Proprietary Firmware	High
9	APC Smart-UPS SRT 2200	Unknown	Low	Proprietary Firmware	High
10	APC Smart-UPS SRT 5000	APC Operating System (AOS)	High	APC OS (Proprietary)	High
11	Alaris 8015 PC Unit	Alaris Operating System software (v9.x)	Medium	Windows CE	High
12	Alaris 8100 Pump Module	Alaris Operating System software (v9.x)	Medium	Unknown	Low
13	Alaris 8110 Syringe Module	Alaris Operating System software (v9.x)	Medium	Unknown	Low
14	B.Braun Infusomat Space	Unknown	Low	Embedded Linux	High
15	B.Braun SmartBattery with Wifi	Unknown	Low	Embedded Linux	Medium
16	Baxter Novum IQ	Unknown	Low	Embedded Linux	High
18	Baxter Sigma Spectrum Wireless Module	Digi NET+OS	High	Unknown	Low
19	Baxter Spectrum IQ	Digi NET+OS	High	Embedded Linux	High
20	Becton Dickinson Pyxis ES System	Windows 10 IoT	High	Windows 10 IoT Enterprise	High
21	Carel PCO1000WB0	Linux	Medium	FreeRTOS	Medium
23	Cerner Connectivity Engine	Unknown	Low	Linux	Medium
25	Hill-Rom NaviCare Room Control Board	Unknown	Low	Windows Embedded Compact	Medium
27	Hospira Plum 360	Unknown	Low	VxWorks	High
29	Johnson Controls NAE	Windows Embedded Standard 7 (WES7) SP1	High	Windows Embedded	High
30	Lantronix MatchPort AR	Lantronix Evolution OS	High	Evolution OS	High
31	Microsystems SPM Client	Unknown	Low	Windows	High
32	Nova Biomedical StatStrip	Windows CE	High	Unknown	High
33	Omnicell Automated Dispensing Cabinet	Windows 10	Medium	Windows 10 IoT Enterprise	High
34	Philips EarlyVue VS30	Unknown	Low	Linux	High
35	Philips IntelliVue	Unknown	Low	Proprietary RTOS	Medium
38	Philips PIIC	Windows Server 2012 R2	High	Windows Server	High
39	Rauland-Borg Responder	Windows	Medium	Unknown	High
42	Rockwell Automation PanelView Plus 7 Performan...	Windows 10 IoT Core (LTSC)	High	Windows 10 IoT Core	High
44	Schneider Electric AS-P	Unknown	Low	Linux	High
45	Siemens PXC Modular	Unknown	Low	Nucleus RTOS	High
47	Siemens SIMATIC IPC	Windows	High	Windows 10 Enterprise LTSC	High

	case	count	percent
0	Both Known	21	42.0
1	Both Unknown	8	16.0
2	ChatGPT Known, Gemini Unknown	5	10.0
3	Gemini Known, ChatGPT Unknown	16	32.0

	model	sw_fw_version	device_count
0	8100 Pump Module	9.17.0.22	217
1	8110 Syringe Module	9.33.0.50	191
2	8110 Syringe Module	9.15.1.2	36
3	8300 EtCO2 Module	8.4.5.6	153
4	8300 EtCO2 Module	8.4.5.0	42

	vlan	iot_count	it_count	iot_to_it_ratio
0	970	2156	10	215.60
1	307	48	13	3.69
2	305	54	15	3.60
3	309	56	18	3.11
4	306	62	20	3.10
...	...	...	...	...
57	225	0	0	NaN
58	221	0	0	NaN
59	212	0	0	NaN
60	211	0	0	NaN
61	81	0	0	NaN