This imports Let's Encrypt certificate metadata from Certificate Transparency into MySQL using the tools from https://github.com/jcjones/certificatetransparency/tree/master/tools/lesql.
By default it stores the Cert.ly CT log in ~/certificate-transparency-log-certly and assumes the existance of a MySQL DB at mysql+tcp://root@localhost:3306/ct without a password; obviously intended for local analysis.
You'll want to edit both the update_ct_mysql.sh and stats_gen.conf to change DB locations, user/password combinations, or the like.
I use these tools to scratch my itch for analyzing usage of Let's Encrypt.
There are some example queries available in this repository, as well as a Python script that determines the currently-valid population of certificates for Let's Encrypt -- an operation that's more difficult to do in pure SQL.
Ensure you have a few GB of disk space to store the CT Log.
Make sure MySQL is up and running, and create a DB named ct to store metadata.
echo "CREATE SCHEMA \`ct\` CHARACTER SET utf8;" | mysql -u root
Now run update_ct_mysql.sh. Re-run whenever you want to update the DB with the latest entries from CT.
→ ./update_ct_mysql.sh
Exporting CT Log data into MySQL...
Counting existing entries... 431546
Fetching signed tree head... 431656 total entries at Mon Jan 11 10:47:00 2016
Hashing tree
Importing entries into DB...
Done.
Then you can query the data:
→ mysql -u root ct < certsIssuedPerName.sql
numIssued count
1 288816
2 60555
3 22637
4 11390
[...]
These queries aren't perfect; particularly mostCommonNames.sql has flaws in how I'm tracking reissuance, but it's a general picture.
→ python stats_gen.py
* Active Certificates
Date Issued Unexpired Expired rate/min
[...]
2015-12-22 5608 175959 0 3.894
2015-12-23 8767 184726 0 6.088
2015-12-24 5916 190642 0 4.108
2015-12-25 4102 194744 0 2.849
2015-12-26 3351 198095 0 2.327
2015-12-27 3473 201568 0 2.412
2015-12-28 3970 205537 1 2.757
2015-12-29 5498 211034 2 3.818
2015-12-30 6036 217067 5 4.192
2015-12-31 5279 222343 8 3.666
2016-01-01 5774 228116 9 4.010
2016-01-02 5726 233841 10 3.976
2016-01-03 4652 238492 11 3.231
2016-01-04 4695 243185 13 3.260
2016-01-05 6410 249593 15 4.451
2016-01-06 6291 255883 16 4.369
2016-01-07 6345 262224 20 4.406
2016-01-08 6452 268675 21 4.481
2016-01-09 6275 274947 24 4.358
2016-01-10 4372 279318 25 3.036
2016-01-11 5472 284786 29 3.800
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent