2013 DNS Census data overview
This section documents the data format of http://dnscensus2013.neocities.org/ which was previously mentioned at https://opendata.stackexchange.com/a/2122/25882
File structure:
- 2nd-level-domains
- ac.txt.xz (3.6 KB)
- ad.txt.xz (2.2 KB)
- ae.txt.xz (36 KB)
- aero.txt.xz (6.8 KB)
- ...
- com.txt.xz (187 MB, 1.1 GB unpacked, ~61 M lines)
- ...
- records
- a.csv.xz (4.6 GB, 37 GB unpacked, ~750 M lines, ~191 M distinct domains, see also: https://dnscensus2013.neocities.org/statistics)
- aaaa.csv.xz (87 MB)
- cname.csv.xz (465 MB)
- dname.csv.xz (48 KB)
- mx.csv.xz (1.9 GB)
- ns.csv.xz (3.9 GB)
- soa.csv.xz (2.9 GB)
- txt.csv.xz (610 MB)
- key.asc
First lines of 2nd-level-domains/ac.txt:
0.ac
022.ac
091.ac
1.ac
10.ac
101domain.ac
12.ac
120v.ac
138.ac
14.ac
So it is a domain list without extra metadata.
Some sampe lines from records/a.csv (first lines, Amazon, Google, last lines, some manual line breaks to help understanding):
name,isotime,ip4address
0-------------------------------------------------------------0.com,2013-01-27T12:19:45,216.195.78.98
0-------------------------------------------------------------0.com,2013-08-11T19:27:02,216.195.78.98
0-------------------------------------------------------------0.com,2013-08-17T10:00:47,216.195.78.98
0-------------------------------------------------------------0.dk,2012-01-30T23:16:56,78.46.30.149
amazon.com,2012-02-01T21:33:36,72.21.194.1
amazon.com,2012-02-01T21:33:36,72.21.211.176
...
amazon.com,2013-10-02T19:03:39,72.21.194.212
amazon.com,2013-10-02T19:03:39,72.21.215.232
amazon.com.au,2012-02-10T08:03:38,207.171.166.22
amazon.com.au,2012-02-10T08:03:38,72.21.206.80
google.com,2012-01-28T05:33:40,74.125.159.103
google.com,2012-01-28T05:33:40,74.125.159.104
...
google.com,2013-10-02T19:02:35,74.125.239.41
google.com,2013-10-02T19:02:35,74.125.239.46
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz.ru,2013-08-13T10:11:28,176.9.147.82
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz.ru,2013-09-11T06:22:08,176.9.147.82
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz.ru,2013-09-20T12:40:44,176.9.147.82
Believe me when I say this, Google has A LOT of IPs, so the date range is almost certainly from Jan 2012 to Oct 2013.
So from this we understand that there is some historical data, but only on the range that was covered by the census.
Index it with SQLite
If you are going to query this dataset several times, you will definitely want to import it into SQLite to make prefix queries instantaneous. We can import the data as per: https://stackoverflow.com/questions/14947916/import-csv-to-sqlite
time sqlite3 a.sqlite 'create table t (d text, t text, i text)'
time sqlite3 a.sqlite '.import --csv --skip 1 /mnt/sda3/torrent/dnscensus2013/records/a.csv t'
time sqlite3 a.sqlite 'create index td on t(d)'
time sqlite3 a.sqlite 'create index tt on t(t)'
time sqlite3 a.sqlite 'create index ti on t(i)'
time sqlite3 a.sqlite "delete from t where t = 'isotime'"
This took one or two hours, and the resulting database size was ~98 GB due to indexes up from the 37 GB of the unpacked CSV. But it is very much worth it.
Then as per: https://stackoverflow.com/questions/8584499/sqlite-should-like-searchstr-use-an-index/76512019#76512019 we can do prefix queries such as:
sqlite a.sqlite "select * from t where i glob '192.186.174.*'"
which would find all lines where the IP starts with the prefix 192.186.174.
More serious users of the dataset would also want to use a TIMESTAMP column for the time, and more importantly, a proper storage for the IPs as 32-bit integers, which would allow proper range queries not possible with this simple TEXT approach. I ended up making a quick C extension helper for it at: https://stackoverflow.com/questions/7638238/sqlite-ip-address-storage/76520885#76520885
2013 DNS Census Data completeness analysis
The 2013 DNS Census data is extremely useful, but it is by no means complete. Other sources that contained useful information that was not present in the 2013 DNS Census, and which I could verify via other means:
- https://viewdns.info/ Information very disjoint from the 2013 census, suggesting very different methodology (both undocumented)
- https://dnshistory.org/ hit and miss. Sometimes has hits that none of the others had however
Related questions:
Common Crawl
https://commoncrawl.org/
This awesome open source crawl data is a bit like Wayback Machine, but it makes an extra effort to be more querryable.
In particular, they have their data on Amazon Athena, which you can query with SQL and mass extract domains from at very reasonable prices: https://commoncrawl.org/2018/03/index-to-warc-files-and-urls-in-columnar-format/
Unfortunately, the IPs are not exposed on Athena currently, even though they seem to be present on the raw crawl data, my feature request that you should upvote: https://github.com/commoncrawl/cc-index-table/issues/30
I think you can download the CDXes locally with:
aws s3 cp s3://commoncrawl/cc-index/collections/CC-MAIN-2013-20/indexes/cdx-00000.gz .
For 2013 for example it should be around 70 GiB zipped, which is large but just barely manageable on a personal computer. Unfortunately whenever I run:
aws s3 cp s3://commoncrawl/cc-index/collections/CC-MAIN-2013-20/indexes/cdx-00000.gz .
it fails after a few seconds with:
download failed: s3://commoncrawl/cc-index/collections/CC-MAIN-2013-20/indexes/cdx-00000.gz to ./cdx-00000.gz An error occurred (SlowDown) when calling the GetObject operation (reached max r
etries: 2): Please reduce your request rate.
presumably because it throttles across all downloaders and not just myself. I managed to work around by firs copying to my bucket, downloading it from my bucket, and then deleting it form my bucket:
aws s3 cp s3://commoncrawl/cc-index/collections/CC-MAIN-2013-20/indexes/cdx-00000.gz s3://cirosantilli/cdx-00000.gz
aws s3 cp ss3://cirosantilli/cdx-00000.gz .
aws s3 rm s3://cirosantilli/cdx-00000.gz
This will presumably incur some small charge, but it will likely be OK. No IPs there however unfortunately.
Internet Census of 2012
https://census2012.sourceforge.net/paper.html
This data was apparently illegally obtained with the Carna Botnet making it ethically gray.
But since it is out there and it is never coming back, we might as well.
The data is a bit huge, so you can just open the Torrent file and download only the files you need: https://census2012.sourceforge.net/download.html
Folder structure I've explored so far:
- data
- rdns: reverse DNS, i.e. IP to domains
1.zpaq
2.zpaq
...
66.zpaq: 88 MB packed, 2.1 GB unpacked, 46 M lines. To unpack on Ubuntu 23.04:
sudo apt install zpaq
zpaq x 66.zpaq
which produces a file named 66.
Sample lines:
66.0.0.0 1336986900 (3)
66.0.0.1 1336767300 1-0-0-66.deltacom.net
66.0.0.1 1346418900 1-0-0-66.deltacom.net
66.0.0.1 1347660900 1-0-0-66.deltacom.net
66.0.0.1 1348530300 (2)
66.0.0.2 1336995900 (2)
66.0.0.2 1346314500 2-0-0-66.deltacom.net
66.0.0.3 1336985100 3-0-0-66.deltacom.net
66.0.0.3 1346433300 3-0-0-66.deltacom.net
66.0.0.3 1347642900 3-0-0-66.deltacom.net
So we understand that each file contains IPs with that start with the byte of the filename e.g. 66.zpaq contains IPs of the form 66.*.
Then e.g. that file tells us that IP 66.0.0.1 is associated with domain 1-0-0-66.deltacom.net.
(2) and (3) appear to be error codes from: https://monkey.org/~provos/libevent/doxygen/evdns_8h.html
I believe that this data contains domains only for IPs that actually advertise their associated domains through Reverse DNS lookup. This appears to be unlike the 2013 DNS Census data which seems to have actually crawled the Internet and noted down which IP domains found resolve to. As a result, it contains a relatively small fraction of the total domains.
Scraping the Chinese expired domain trackers
At https://webmasters.stackexchange.com/questions/33806/expired-domains-database/143542#143542 I document my scraping of pages such as http://static.hupo.com/expdomain_myadmin/2013-01-01（国际域名）.txt and http://domain.webmasterhome.cn/com/2012-03-06.asp which turned to contain valuable domains for my project, so it is also worth a look if your domains of interest may have expired, which is often the case for clandestine domains that have seen been discovered and taken down by the attacker.
