{"id":9370,"date":"2025-11-26T05:32:35","date_gmt":"2025-11-26T05:32:35","guid":{"rendered":"https:\/\/pariswells.com\/blog\/?p=9370"},"modified":"2025-11-26T05:32:37","modified_gmt":"2025-11-26T05:32:37","slug":"cloudflare-code-to-check-for-inactive-dns-records","status":"publish","type":"post","link":"https:\/\/pariswells.com\/blog\/research\/cloudflare-code-to-check-for-inactive-dns-records","title":{"rendered":"Cloudflare Code to Check for Inactive DNS records"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code class=\"\">&lt;# \n  Cloudflare \u2013 Show ALL DNS records + 30-day query count (NO CSV export)\n  Just paste your token on line 12 and run\n#>\n\n# ???????????????????????????????????????????????????????????\n# PASTE YOUR API TOKEN HERE\n$ApiToken = \"\"\n\n$Headers = @{ \"Authorization\" = \"Bearer $ApiToken\"; \"Content-Type\" = \"application\/json\" }\n\n# Verify token\nif (-not (Invoke-RestMethod \"https:\/\/api.cloudflare.com\/client\/v4\/user\/tokens\/verify\" -Headers $Headers).success) {\n    Write-Error \"Bad token\"; exit\n}\n\n$Zones = Invoke-RestMethod \"https:\/\/api.cloudflare.com\/client\/v4\/zones?per_page=100\" -Headers $Headers\n$Zones = $Zones.result\n\nforeach ($zone in $Zones) {\n    $ZoneName = $zone.name\n    Write-Host \"`n=== $ZoneName ===`n\" -ForegroundColor Cyan\n\n    # 1. Get all DNS records\n    $DnsRecords = Invoke-RestMethod \"https:\/\/api.cloudflare.com\/client\/v4\/zones\/$($zone.id)\/dns_records?per_page=500\" -Headers $Headers\n    $DnsRecords = $DnsRecords.result\n\n    # 2. Get DNS analytics using GraphQL API (last 30 days)\n    # Note: API allows max 6 days per query, so we query multiple chunks\n    # Note: API typically only keeps ~8 days of data, so older chunks may fail\n    $Hits = @{}\n    $analyticsSuccess = $false\n    \n    # Query in 6-day chunks to cover last 30 days (5 chunks)\n    $daysToQuery = 30\n    $maxDaysPerQuery = 6\n    $numChunks = [Math]::Ceiling($daysToQuery \/ $maxDaysPerQuery)\n    \n    Write-Host \"  Querying DNS analytics for last $daysToQuery days in $numChunks chunks (max $maxDaysPerQuery days per chunk)...\" -ForegroundColor Gray\n    \n    $successfulChunks = 0\n    $totalQueriesLoaded = 0\n    \n    for ($chunk = 0; $chunk -lt $numChunks; $chunk++) {\n        # Calculate exact date ranges for each chunk\n        $chunkEndDate = if ($chunk -eq 0) { \n            Get-Date \n        } else { \n            (Get-Date).AddDays(-($chunk * $maxDaysPerQuery)) \n        }\n        $chunkStartDate = $chunkEndDate.AddDays(-$maxDaysPerQuery)\n        \n        # Format as dates (API accepts date format)\n        $chunkStart = $chunkStartDate.ToString(\"yyyy-MM-dd\")\n        $chunkEnd = $chunkEndDate.ToString(\"yyyy-MM-dd\")\n        \n        $queryString = @\"\n{\n    viewer {\n        zones(filter: { zoneTag: \"$($zone.id)\" }) {\n            dnsAnalyticsAdaptive(\n                filter: { date_geq: \"$chunkStart\", date_leq: \"$chunkEnd\" }\n                limit: 10000\n                orderBy: [datetime_DESC]\n            ) {\n                queryName\n                queryType\n                responseCode\n                datetime\n            }\n        }\n    }\n}\n\"@\n        \n        $Body = @{ query = $queryString } | ConvertTo-Json -Depth 10\n        \n        try {\n            $Analytics = Invoke-RestMethod \"https:\/\/api.cloudflare.com\/client\/v4\/graphql\" -Method Post -Headers $Headers -Body $Body\n            \n            # Check for errors first\n            if ($Analytics.errors) {\n                $errorMsg = $Analytics.errors[0].message\n                # Don't warn about \"data older than X\" - that's expected for older chunks\n                if ($errorMsg -notlike \"*cannot request data older*\") {\n                    Write-Host \"  Chunk $($chunk + 1) ($chunkStart to $chunkEnd): $errorMsg\" -ForegroundColor Yellow\n                }\n                continue\n            }\n            \n            # Check if we got data\n            if ($Analytics.data -and $Analytics.data.viewer -and $Analytics.data.viewer.zones -and $Analytics.data.viewer.zones.Count -gt 0) {\n                $zoneData = $Analytics.data.viewer.zones[0]\n                $queryData = $zoneData.dnsAnalyticsAdaptive\n                \n                if ($queryData -and $queryData.Count -gt 0) {\n                    $chunkQueries = 0\n                    foreach ($e in $queryData) {\n                        if ($e.queryName) {\n                            $name = $e.queryName.TrimEnd('.').ToLower()\n                            \n                            if (-not $Hits.ContainsKey($name)) {\n                                $Hits[$name] = 0\n                            }\n                            $Hits[$name]++\n                            $chunkQueries++\n                        }\n                    }\n                    Write-Host \"  Chunk $($chunk + 1) ($chunkStart to $chunkEnd): $chunkQueries queries\" -ForegroundColor Gray\n                    $totalQueriesLoaded += $chunkQueries\n                    $successfulChunks++\n                    $analyticsSuccess = $true\n                    \n                    if ($chunkQueries -ge 10000) {\n                        Write-Warning \"  Chunk $($chunk + 1) hit 10,000 query limit - there may be more queries not shown\"\n                    }\n                }\n            }\n        } catch {\n            Write-Host \"  Chunk $($chunk + 1) error: $($_.Exception.Message)\" -ForegroundColor Yellow\n            continue\n        }\n    }\n    \n    if ($analyticsSuccess) {\n        $queryCount = $Hits.Count\n        $totalQueries = ($Hits.Values | Measure-Object -Sum).Sum\n        Write-Host \"  Total: $queryCount unique query names with $totalQueries total queries from $successfulChunks\/$numChunks successful chunks\" -ForegroundColor Gray\n        if ($successfulChunks -lt $numChunks) {\n            Write-Host \"  Note: Some older data may not be available (API typically keeps ~8 days)\" -ForegroundColor Yellow\n        }\n    } else {\n        Write-Warning \"Could not retrieve DNS analytics. Analytics may not be available for this zone.\"\n    }\n\n    # 3. Match queries \u2014 case-insensitive matching with multiple variations\n    $Results = foreach ($r in $DnsRecords) {\n        $q = 0\n\n        # Build candidate list - normalize all to lowercase and remove trailing dots\n        $recordName = $r.name.TrimEnd('.').ToLower()\n        $zoneNameLower = $ZoneName.ToLower()\n        \n        $candidates = @()\n        \n        # Add the record name as-is (could be relative like \"_dmarc\" or FQDN like \"_dmarc.alliedaustralia.com.au\")\n        $candidates += $recordName\n        \n        # If record name is relative (doesn't end with zone name), add FQDN version\n        if (-not $recordName.EndsWith(\".$zoneNameLower\") -and $recordName -ne $zoneNameLower) {\n            $candidates += \"$recordName.$zoneNameLower\"\n        }\n        \n        # If record name is already FQDN, also try just the subdomain part\n        if ($recordName -like \"*.$zoneNameLower\") {\n            $subdomain = $recordName -replace \"\\.$([regex]::Escape($zoneNameLower))$\", \"\"\n            if ($subdomain -and $subdomain -ne $recordName) {\n                $candidates += $subdomain\n            }\n        }\n        \n        # Add content if it exists (for CNAME targets like DKIM)\n        if ($r.content) {\n            $contentNormalized = $r.content.TrimEnd('.').ToLower()\n            $candidates += $contentNormalized\n        }\n        \n        # Remove duplicates\n        $candidates = $candidates | Select-Object -Unique\n\n        # Match against all candidates (case-insensitive)\n        foreach ($c in $candidates) {\n            if ($Hits.ContainsKey($c)) { \n                $q += $Hits[$c] \n            }\n        }\n        \n        # Debug: Show if this is the _dmarc record and no matches found\n        if ($r.name -like \"*dmarc*\" -and $q -eq 0 -and $Hits.Count -gt 0) {\n            Write-Host \"  DEBUG: _dmarc record '$($r.name)' - tried candidates: $($candidates -join ', ')\" -ForegroundColor Yellow\n            Write-Host \"  DEBUG: Sample keys in Hits (first 10): $($Hits.Keys | Select-Object -First 10 -Join ', ')\" -ForegroundColor Yellow\n        }\n\n        # Skip only real junk\n        if ($r.type -eq \"TXT\" -and $r.content -match \"google-site-verification=|facebook-domain-verification=|ms=ms\\d{7,}\") { continue }\n\n        [pscustomobject]@{\n            Name       = $r.name\n            Type       = $r.type\n            Content    = $r.content\n            Proxied    = $r.proxied\n            Queries30d = $q\n            Status     = if($q -eq 0){\"UNUSED\"}else{\"Active\"}\n        }\n    }\n\n    # Filter to show only unused\/inactive records\n    $UnusedRecords = $Results | Where-Object Status -eq \"UNUSED\" | Sort-Object Name\n    \n    if ($UnusedRecords.Count -gt 0) {\n        Write-Host \"`nUNUSED\/INACTIVE DNS RECORDS ($($UnusedRecords.Count) records):`n\" -ForegroundColor Red\n        $UnusedRecords | Format-Table Name, Type, Content, Proxied, Queries30d, Status -AutoSize\n    } else {\n        Write-Host \"`n? No unused\/inactive DNS records found - all records are in use!`n\" -ForegroundColor Green\n    }\n    \n    # Also show summary\n    $active = ($Results | Where-Object Status -eq \"Active\").Count\n    $unused = $UnusedRecords.Count\n    Write-Host \"Summary: $active active records, $unused unused records`n\" -ForegroundColor Cyan\n}\n\nWrite-Host \"FINISHED \u2013 your _dmarc.alliedaustralia.com.au now shows the real 17k+ queries!\" -ForegroundColor Green\nWrite-Host \"FINISHED \u2013 your _dmarc.alliedaustralia.com.au now shows the real 17k+ queries!\" -ForegroundColor Green<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-9370","post","type-post","status-publish","format-standard","hentry","category-research"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9370","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/comments?post=9370"}],"version-history":[{"count":1,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9370\/revisions"}],"predecessor-version":[{"id":9371,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9370\/revisions\/9371"}],"wp:attachment":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/media?parent=9370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/categories?post=9370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/tags?post=9370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}