Switch Open Data .ch and .li Domains

Andres Bohren
Hi All,

Switch the Swiss Registrar for .ch and .li Domains has published the Domains on a Open Data Policy.
You can download the top 1'000 Domains or use a Zone Transfer

Switch Open Data

You need to create the TSIG Key

# filename ch_zonedata.key
key tsig-zonedata-ch-public-21-01 {     algorithm hmac-sha512;     secret "stZwEGApYumtXkh73qMLPqfbIDozWKZLkqRvcjKSpRnsor6A6MxixRL6C2HeSVBQNfMW4wer+qjS0ZSfiWiJ3Q=="; };
then use dig to initiate the Zone Transfer

dig -k ch_zonedata.key @zonedata.switch.ch +noall +answer +noidnout +onesoa AXFR ch. > ch.txt
Looks like this


I have used psftp.exe to transfer the ch.txt File to my Computer

open 172.21.175.80
cd /var/tmp
lcd E:\
get ch.txt


The Zone File looks like "TAB" separated. But is not consistent in any way.



It took me quite a while to write a performant PowerShell Script that fixes all these "issues" in the .ch Zone File.

###############################################################################
# Fix Tab in .ch Zone File
# 30.08.2022 Andres Bohren / https://blog.icewolf.ch
###############################################################################
$INfile = "E:\ch.txt"
$OUTfile = "E:\ch_psedit.txt"
$reader = new-object system.IO.StreamReader($INfile)
$writer = new-object system.IO.StreamWriter($OUTfile, 0)

$Start = Get-Date
$ArrayDomains = New-Object System.Collections.ArrayList
#$ArrayDomains = @()
$Int = 0
while ($line = $reader.ReadLine())
{
    # do your thing
    $Int = $Int + 1
    $Line = $Line.Replace("`t`t`t","`t")
    $Line = $Line.Replace("`t`t","`t")
    $Line = $Line.Replace(".ch. 3600",".ch.`t3600")
    $Line = $Line.Replace(".ch. 3600",".ch.`t3600")
    $Line = $Line.Replace("3600 IN RRSIG","3600`tIN`tRRSIG")
    $Line = $Line.Replace("3600 IN DS","3600`tIN`tDS")
    $Line = $Line.Replace("3600 IN NS","3600`tIN`tNS")
    $Line = $Line.Replace("3600 IN","3600`tIN")
    $Line = $Line.Replace("IN`tDS ","IN`tDS`t")
    $Line = $Line.Replace("IN`tNS ","IN`tNS`t")
    $Line = $Line.Replace("IN RRSIG","IN`tRRSIG")
    $Line = $Line.Replace("IN DS","IN`tDS")
    $Line = $Line.Replace("IN NS","IN`tNS")
    $Line = $Line.Replace("IN`tDS ","IN`tDS`t")
    $Line = $Line.Replace(".ch. 900 IN NSEC3",".ch.`t900`tIN`tNSEC3")
    $Line = $Line.Replace(".ch. 900 IN RRSIG",".ch.`t900`tIN`tRRSIG")
    $Line = $Line.Replace("IN`tNSEC3 ","IN`tNSEC3`t")
    $Line = $Line.Replace("IN`tRRSIG ","IN`tRRSIG`t")
    $Line = $Line.Replace(".ch. 900 IN",".ch.`t900`tIN")
    $Line = $Line.Replace("IN`tRRSIG DS","IN`tRRSIG`tDS")
    $Writer.WriteLine("$Line")
    
    $Domain = $Line.Split("`t")[0]
    $Domain = $Domain.Substring(0,$Domain.Length -1)
    If ($Int%10000 -eq 0)
    {
    Write-Host "Domain: $Domain [$int]"
    }
}
$End = Get-Date
$Timespan = New-TimeSpan -Start $Start -End $End
$Timespan
Write-Host "Lines processed: $INT" -ForegroundColor Green
$reader.close()
$writer.Close()


This Script converts the File in TAB Separated Columns and adds CRLF at the end of the Line.


The File still has over 10'000'000 Lines. As you can see there are multiple Lines for the same Domain.
We need to find a way to make them Unique.

I use the "SQL Server Import and Export Wizard" to import the File into a SQL Database


Source: Flat File Source


Set the Names of the Columns



Preview looks fine


Destination: SQL Server Express


Create new Database: db_domain_raw









Now i can use the "Select Distinct" to find out Unique Domains

USE db_domain_raw
GO
SELECT DISTINCT RTRIM(Domain) as Domain FROM ch_psedit WHERE RecordType = 'NS'
ORDER BY Domain ASC

After two Minutes it's clear that there are about 2.4 Mio .ch Domains



Regards
Andres Bohren