ip-cluster-analysis

# IP Cluster Analysis Detect fraud rings by finding IPs/subnets shared by many distinct free AI users. ## Schema config Table/project/database names are **logical**, resolved from `config/schema.json` (see `SCHEMA.md`). SQL uses placeholders like `{{ tables.int_enriched_client_events }}`. Run via `api_client.py query --database warehouse --file <path>` (or `--sql`) to resolve them automatically; substitute physical names yourself for raw `curl`/`bq`. ## Data Source `{{ tables.int_enriched_client_events }}` contains client telemetry enriched with: - `ip_address` — client IP as seen by RudderStack (from `context_request_ip`) - `geo_country`, `geo_region`, `geo_city`, `geo_postal_code` — geo derived from that IP - `user_plan_type` — plan at time of event (free, pro, build, etc.) - `event_name` — filter to `agentmode.createdaiblock` for AI usage This table lives in the **`warehouse`** database (see `config/schema.json`). IP address is **not** available in server-side AI request tables (`int_llm_requests`). The connection to AI spend is through `user_id` — match users from IP clusters to their server-side costs. ## Analysis Workflow ### Step 1: IP-Level Concentration Find IPs with multiple distinct free users creating AI blocks. See `references/queries.md` § "IP-level concentration." Run for the day(s) showing the usage spike. Start with a threshold of `distinct_free_users >= 3`. ### Step 2: Subnet-Level Concentration Group by /24 subnet (first 3 octets) to catch fraud rings that rotate IPs within a block. See `references/queries.md` § "Subnet-level concentration." Use threshold of `distinct_free_users >= 5` at subnet level. ### Step 3: Temporal Trending For suspicious subnets found in steps 1-2, check whether they're new (the current spike) or persistent. See `references/queries.md` § "Temporal trending." A subnet jumping from ~5 users/day to 25+ users/day is a new fraud activation. A subnet consistently at 20+ users/day is persistent abuse that may already be known. ### Step 4: Classify and Prioritize Use these heuristics to triage clusters: **Strong fraud signals:** - **No city in geo resolution** — legitimate residential IPs almost always resolve to a city. Missing city strongly suggests datacenter/VPN. - **High blocks-per-user ratio** (>20/day) — indicates automated or heavy programmatic abuse. - **Many users from a single IP** (>10 distinct users on one IP) — residential NAT rarely exceeds a few users on Warp. - **Known datacenter/cloud IP ranges** — ranges belonging to major cloud/hosting providers, or IPs that resolve to a region without company context. - **Adjacent /24 subnets lighting up together** — e.g., two neighbouring /24s (like `192.0.2.x` and `192.0.3.x`) both spiking on the same day. **Ambiguous (investigate further):** - University/corporate campuses (many users, legitimate shared IP) — check if usage patterns look organic. - Popular VPN exit nodes (many users, but not necessarily coordinated) — look for shared conversation IDs or email domain patterns. **Likely legitimate:** - Low blocks-per-user (<10/day) with city geo resolving normally. - `distinct_ips ≈ distinct_users` within a subnet (each user has their own IP, just same ISP). ### Step 5: Cross-Reference For suspicious IP clusters, extract user_ids (see `references/queries.md` § "Users in a suspicious cluster") and investigate: - **Reputation status** — filter out users with `reputation_status = reputable` or `reputable_from_override` from `core_user_facts`; they've already been cleared. Focus only on `unreputable` or null. - **Email domains** — are they disposable/gibberish? - **Account age** — brand new accounts burning credits immediately? - **Shared conversations** — strongest fraud signal (see conversation_analysis.sql) - **Verisoul status** — if Verisoul already flagged them, they're blocked; focus on unflagged users. - **AI spend** — join to `{{ tables.int_llm_requests }}` via `user_id` to quantify cost (see `references/queries.md` § "Cost attribution"). ### Step 6: Report Include in Slack report: - Table of suspicious subnets with user counts, block counts, blocks-per-user, geo, and whether new or persistent - Specific user_ids from the worst clusters for manual review - Metabase question with the subnet trending query for ongoing monitoring - Recommended action (block IPs at RudderStack level, disable accounts, add email domains to blocklist) ## Important Notes - **IP spoofing is not a concern here.** TCP requires completing a handshake, so the IPs RudderStack sees are real. The evasion vector is VPNs/proxies, not spoofing — but VPNs actually *help* detection by concentrating many accounts on shared exit nodes. - **Residential proxy rotation** is the hardest to detect via IP alone. If you see suspicious patterns but no IP clustering, fall back to email domain / conversation / behavioral analysis. - **RudderStack does not provide ASN/ISP data** in the geo enrichment. To identify datacenter vs residential IPs, rely on the heuristics above (no city, known ranges) rather than ASN lookups.