git:20260522.736a01e to git:20260609.a0be1e4

+ ---

+ name: pihole-csv-analyzer

+ description: Analyze CSV exports from Pi-hole such as query logs, top domains, top clients, or adlist exports and produce actionable recommendations. Trigger immediately when the user uploads or mentions a Pi-hole CSV file, types "pihole" or "ph analyze", or asks to analyze DNS query data, blocklist hits, or Pi-hole exports. Produce an in-chat analysis plus candidate blocklist and allowlist outputs for review.

+ ---

+

+ # Pi-hole CSV Analyzer

+

+ ## Intent

+

+ Analyze Pi-hole CSV exports to identify query patterns, noisy clients, allowlist candidates, blocklist candidates, and cleanup actions.

+

+ ## Use When

+

+ - The user explicitly asks for `pihole-csv-analyzer`.

+ - The request matches the triggers or workflow described below.

+ - The task benefits from a reusable, structured output instead of a one-off answer.

+

+ ## Do Not Use When

+

+ - Do not use without CSV or equivalent query data, for generic Pi-hole setup, or for changes that cannot be traced to export evidence.

+ - Required context is missing and cannot be reasonably inferred.

+ - A more specific skill in this repo is a better match.

+

+ ## Workflow

+

+ 1. Identify the exact task and available source material.

+ 2. Apply the domain rules and output format in this skill.

+ 3. State assumptions, uncertainty, and missing inputs clearly.

+ 4. Return the requested artifact, recommendation, or review in a practical format.

+ 5. Check the result against the validation checklist before finishing.

+

+ ## Constraints

+

+ - Do not fabricate missing facts, measurements, dates, sources, or user context.

+ - Keep output aligned with Mick's direct, practical communication style unless the skill says otherwise.

+ - Preserve safety, legal, medical, financial, and operational boundaries stated in this file.

+ - Prefer concise, usable output over broad explanation.

+

+ ## Validation Checklist

+

+ - [ ] The output matches the skill's intended task and platform.

+ - [ ] Required inputs, assumptions, and uncertainty are explicit.

+ - [ ] Safety, scope, and source limits are respected.

+ - [ ] The response follows the requested format or the skill's default output format.

+ - [ ] The result is practical enough to use without another cleanup pass.

+

+ Analyze Pi-hole CSV exports and turn them into practical, reviewable cleanup actions.

+

+ ## Supported Inputs

+

+ This skill should handle:

+

+ - query log exports

+ - top domains exports

+ - top clients exports

+ - blocklist or adlist exports

+

+ If the format is ambiguous, inspect the header row and the first few rows before proceeding.

+

+ ## Export Type Detection

+

+ Use the CSV header to identify the likely export type.

+

+ | Export Type | Typical Columns | Typical Source |

+ | --- | --- | --- |

+ | Query Log | `time`, `type`, `domain`, `client`, `status`, `reply_type`, `reply_time`, `dnssec` | Pi-hole web UI query log export |

+ | Top Domains | `domain`, `count` or `hits` | Dashboard top permitted or blocked domains |

+ | Top Clients | `client`, `count` or `hits` | Dashboard top clients |

+ | Blocklist or Adlist | `address`, `enabled`, `comment`, `group`, `date_added` | Adlist or domain export |

+

+ Pi-hole v6 exports can vary slightly. Normalize column names before applying logic.

+

+ ## Query Status Mapping

+

+ When analyzing query-log exports, use Pi-hole's documented query-database status meanings.

+

+ Common blocked status values:

+

+ - `1` blocked by gravity

+ - `4` blocked by regex denylist

+ - `5` blocked by exact denylist

+ - `6` blocked by upstream known blocking IP

+ - `7` blocked by upstream null response

+ - `8` blocked by upstream `NXDOMAIN` with `RA` bit unset

+ - `9` blocked by gravity during deep CNAME inspection

+ - `10` blocked by regex during deep CNAME inspection

+ - `11` blocked by exact denylist during deep CNAME inspection

+ - `15` blocked while the database is busy

+ - `16` blocked special domains

+ - `18` blocked by upstream `EDE 15`

+

+ Common allowed status values:

+

+ - `2` allowed and forwarded

+ - `3` allowed and replied from cache

+ - `12` allowed retried query

+ - `13` allowed retried but ignored query

+ - `14` allowed already forwarded

+ - `17` allowed and replied from stale cache

+

+ Treat unknown or new status values carefully and flag them instead of guessing.

+

+ ## Analysis Workflow

+

+ ### 1. Normalize the data

+

+ - normalize column names to lowercase

+ - strip extra whitespace

+ - detect whether status values are numeric or text

+

+ ### 2. Compute the high-value summary

+

+ For query-log exports, compute:

+

+ - total query volume

+ - blocked versus allowed volume

+ - top allowed domains

+ - top blocked domains

+ - top clients by volume

+ - unusually noisy clients

+ - suspicious allowed domains that may deserve review

+

+ For top domains or top clients exports, normalize the columns and rank by count.

+

+ For blocklist or adlist exports, summarize enabled versus disabled state and obvious cleanup opportunities.

+

+ ### 3. Generate review candidates

+

+ #### Blocklist candidates

+

+ Flag allowed domains that look suspicious based on signals such as:

+

+ - telemetry or tracking patterns

+ - free-TLD abuse indicators such as `.tk`, `.cf`, `.gq`, `.ml`

+ - IP-style subdomain patterns

+ - long random-looking subdomains

+ - repeated high-volume domains tied to a single client with no obvious user-facing purpose

+

+ Avoid recommending major infrastructure or obviously essential domains without a strong reason. Be conservative.

+

+ #### Allowlist candidates

+

+ Flag blocked domains that may be legitimate when they:

+

+ - are tied to sign-in, updates, streaming, or other common core functionality

+ - appear frequently among blocked queries

+ - clearly belong to major legitimate platforms

+

+ ### 4. Output

+

+ Always provide an in-chat summary with:

+

+ - overview metrics

+ - top allowed and blocked domains

+ - top clients

+ - noisy-client callouts

+ - suspicious allowed domains

+ - possible allowlist candidates

+ - practical next-step recommendations

+

+ Also produce reviewable candidate outputs named:

+

+ - `blocklist_candidates.txt`

+ - `allowlist_candidates.txt`

+

+ If the environment supports file output, create those files. Otherwise, present their contents inline in clearly separated sections.

+

+ ## Reference Logic

+

+ Use Pi-hole's documented status values as the source of truth. For example, a basic numeric mapping can start like this:

+

+ ```python

+ BLOCKED_STATUSES = {1, 4, 5, 6, 7, 8, 9, 10, 11, 15, 16, 18}

+ ALLOWED_STATUSES = {2, 3, 12, 13, 14, 17}

+ ```

+

+ When string-style status values appear instead of integers, use cautious text matching and keep uncertainty visible.

+

+ ## Edge Cases

+

+ - Empty or malformed CSV files: explain what is wrong and what is expected

+ - No blocked queries: say so directly

+ - No suspicious domains: say so directly

+ - IPv6 clients: display as-is

+ - Large files: summarize and sample pattern analysis if needed

+ - Unknown status values: flag them as unknown instead of forcing a blocked or allowed label

+

+ ## Style

+

+ - Lead with the most actionable findings

+ - Keep the output concise and practical

+ - Do not invent recommendations

+ - Be explicit when confidence is low

+ - Treat generated blocklist and allowlist entries as review queues, not final policy

+

+ ## Help And Examples

+

+ If the user is not sure how to use this skill, asks what it needs, or asks for examples:

+

+ - Explain in plain language what this skill can do.

+ - Tell the user the minimum input needed for a useful first pass.

+ - Show the example prompts below.

+ - Offer the fastest next prompt the user can send.

+

+ Minimum useful input:

+

+ - A Pi-hole CSV export or a summary of what you want analyzed.

+

+ Example prompts:

+

+ - `Use pihole-csv-analyzer to review this Pi-hole export and tell me what cleanup actions make sense.`

+ - `Analyze this Pi-hole CSV and surface the top noisy domains, trends, and practical next steps.`

+ - `Show me an example prompt for using this skill with a pasted CSV summary.`

+