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

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