search

crtsh / certwatch_db

Database schema
https://crt.sh/
GNU General Public License v3.0
199 stars 36 forks source link

SCT timestamps 1hr fast #51

Closed robstradling closed 6 years ago

robstradling commented 6 years ago

Since deploying the new ct_monitor application (a few weeks ago) until just now, SCT timestamps were recorded on the crt.sh database 1hr fast.

The problem was that ct_monitor was assuming that the TimestampToTime() function in github.com/google/certificate-transparency-go was returning timestamps in UTC, when in fact they were in local time. Local time on the crt.sh servers is BST (i.e., GMT+1).

I just fixed ct_monitor (by adding an explicit conversion to UTC). This ticket is to track the additional task of fixing all of the (1hr fast) SCT timestamps on the database.

testtlnls commented 6 years ago

This report is about Comodo Mammoth Log (I removed raw data). I will write other logs like Mammoth in 6 hours.(Argon2018, Icarus, Rocketeer, Nimbus2018, Sabre, Yeti2018 and Nessie2018)

Comodo Mammoth

2018-09-03 14:00:00 UTC:Right time

2018-09-03 13:00:00 UTC:1hour+

2018-09-02 05:00:00 UTC:1hour+

2018-08-31 20:52:14 UTC:1hour+

2018-08-28 12:00:00 UTC:1hour+

2018-08-08 17:45:02 UTC:1hour+

2018-08-05 20:10:00 UTC:Right time

testtlnls commented 6 years ago

This report is about Comodo Mammoth(additional) and Comodo Sabre Log. Next report will be about Cloudflare Nimbus2018 and Google Icarus Log.

-----Comodo Mammoth-----

2018-08-07 12:18:05 UTC Mammoth:Right time

2018-08-07 12:18:05 UTC crt.sh ID:636311365 Mammoth Log Entry Index:83550163 Mammoth:1hour+

-----Comodo Sabre-----

2018-09-03 14:30:00 UTC Sabre:1hour+

2018-09-03 12:00:00 UTC Sabre:1hour+

2018-08-20 14:00:00 UTC Sabre:1hour+

2018-08-19 21:12:07 UTC Sabre:1hour+

2018-08-19 13:00:00 UTC Sabre:1hour+

2018-08-16 01:10:34 UTC Sabre:1hour+

2018-08-10 17:45:02 UTC Sabre:1hour+

2018-08-09 13:00:00 UTC Sabre:1hour+

2018-08-05 17:45:03 UTC Sabre:1hour+

2018-07-31 01:00:05 UTC crt.sh ID:656113055 Sabre Log Entry Index:82676366 Sabre:1hour+

2018-07-30 23:26:12 UTC Sabre:1hour+

2018-07-30 23:26:10 UTC Sabre:Right time

2018-07-30 16:36:51 UTC Sabre:Right time

2018-07-29 23:01:23 UTC Sabre:Right time

2018-07-28 16:07:43 UTC Sabre:Right time

2018-07-27 20:51:35 UTC Sabre:Right time

2018-07-13 22:32:57 UTC crt.sh ID:602732249 Sabre Log Entry Index:75947847 Sabre:Right time

testtlnls commented 6 years ago

This report is about Cloudflare Nimbus2018 and Google Icarus Log. I will write a report about DigiCert Yeti2018 and DigiCert Nessie2018 Log in an hour. I will write other logs like this in 4 hours.(Argon2018, Rocketeer, Pilot)

-----Cloudflare Nimbus2018-----

2018-08-19 21:12:07 UTC Nimbus2018:1hour+

2018-08-06 18:30:07 UTC crt.sh ID:665077543 Nimbus2018 Log Entry Index:217721956 Nimbus2018:1hour+

2018-08-05 20:40:11 UTC crt.sh ID:672190104 Nimbus2018 Log Entry Index:217163237 Nimbus2018:1hour+

2018-08-04 17:45:03 UTC Nimbus2018:1hour+

2018-08-03 17:45:03 UTC Nimbus2018:1hour+

2018-07-30 23:26:12 UTC Nimbus2018:1hour+

2018-07-30 23:26:12 UTC Nimbus2018:Right time

2018-07-30 16:36:51 UTC Nimbus2018:Right time

2018-07-29 12:07:56 UTC Nimbus2018:Right time

2018-07-27 18:31:27 UTC crt.sh ID:647529271 Nimbus2018 Log Entry Index:211394521 Nimbus2018:Right time

2018-07-16 06:44:45 UTC Nimbus2018:Right time

-----Google Icarus-----

2018-08-28 12:00:00 UTC Icarus:Right time

2018-08-20 15:00:00 UTC Icarus:1hour+

2018-08-20 14:00:00 UTC Icarus:1hour+

2018-08-19 13:00:00 UTC Icarus:1hour+

2018-08-17 01:10:44 UTC Icarus:1hour+

2018-08-15 18:56:17 UTC crt.sh ID:680528527 Icarus Log Entry Index:336855870 Icarus:1hour+

2018-08-12 17:45:02 UTC Icarus:1hour+

2018-08-11 17:45:02 UTC Icarus:1hour+

2018-08-10 17:45:02 UTC Icarus:1hour+

2018-08-09 16:10:45 UTC crt.sh ID:670386381 Icarus Log Entry Index:330942053 Icarus:1hour+

2018-08-08 17:45:02 UTC Icarus:1hour+

2018-08-07 17:30:56 UTC crt.sh ID:666726344 Icarus Log Entry Index:328893954 Icarus:1hour+

2018-08-06 18:30:07 UTC crt.sh ID:665077543 Icarus Log Entry Index:327933598 Icarus:1hour+

2018-08-05 17:45:03 UTC Icarus:1hour+

2018-08-04 17:45:03 UTC Icarus:1hour+

2018-08-02 17:45:03 UTC Icarus:1hour+

2018-08-02 07:41:51 UTC crt.sh ID:659260474 Icarus Log Entry Index:324628156 Icarus:1hour+

2018-07-31 01:00:05 UTC crt.sh ID:656113055 Icarus Log Entry Index:322705790 Icarus:1hour+

2018-07-31 00:11:31 UTC Icarus:1hour+

2018-07-30 23:26:12 UTC Icarus:1hour+

2018-07-30 23:26:12 UTC Icarus:Right time

2018-07-30 16:36:51 UTC Icarus:Right time

2018-07-29 23:01:23 UTC Icarus:Right time

2018-07-29 12:07:56 UTC Icarus:Right time

2018-07-29 01:56:47 UTC Icarus:Right time

2018-07-28 16:07:43 UTC Icarus:Right time

2018-07-27 20:51:35 UTC Icarus:Right time

2018-07-27 18:31:27 UTC crt.sh ID:647529271 Icarus Log Entry Index:320443252 Icarus:Right time

testtlnls commented 6 years ago

This report is about DigiCert Yeti2018 and DigiCert Nessie2018 Log.

-----DigiCert Yeti2018-----

2018-09-02 05:00:00 UTC Yeti2018:1hour+

2018-08-31 20:52:14 UTC Yeti2018:1hour+

2018-08-20 15:00:00 UTC Yeti2018:1hour+

2018-08-19 15:00:00 UTC Yeti2018:1hour+

2018-08-16 01:10:34 UTC Yeti2018:1hour+

2018-08-15 18:56:17 UTC crt.sh ID:680528527 Yeti2018 Log Entry Index:69996205 Yeti2018:1hour+

2018-08-11 17:45:02 UTC Yeti2018:1hour+

2018-08-09 16:10:45 UTC crt.sh ID:670386381 Yeti2018 Log Entry Index:67367679 Yeti2018:1hour+

2018-08-08 17:45:02 UTC Yeti2018:1hour+

2018-08-07 17:30:56 UTC crt.sh ID:666726344 Yeti2018 Log Entry Index:66501016 Yeti2018:1hour+

2018-08-06 18:30:07 UTC crt.sh ID:665077543 Yeti2018 Log Entry Index:66100142 Yeti2018:1hour+

2018-08-05 20:10:00 UTC Yeti2018:1hour+

2018-08-02 17:45:03 UTC Yeti2018:1hour+

2018-08-02 07:41:51 UTC crt.sh ID:659260474 Yeti2018 Log Entry Index:64719307 Yeti2018:1hour+

2018-08-01 17:47:05 UTC Yeti2018:1hour+

2018-07-30 23:26:12 UTC Yeti2018:1hour+

2018-07-30 23:26:12 UTC Yeti2018:Right time

2018-07-29 23:01:23 UTC Yeti2018:Right time

2018-07-29 01:56:47 UTC Yeti2018:Right time

2018-07-27 18:31:27 UTC crt.sh ID:647529271 Yeti2018 Log Entry Index:62955457 Yeti2018:Right time

2018-07-12 16:19:27 UTC Yeti2018:Right time

-----DigiCert Nessie2018-----

2018-08-20 15:00:00 UTC Nessie2018:1hour+

2018-08-19 13:00:00 UTC Nessie2018:1hour+

2018-08-17 01:10:44 UTC Nessie2018:1hour+

2018-08-16 01:10:34 UTC Nessie2018:1hour+

2018-08-15 18:56:17 UTC crt.sh ID:680528527 Nessie2018 Log Entry Index:55376929 Nessie2018:1hour+

2018-08-12 17:45:02 UTC Nessie2018:1hour+

2018-08-11 17:45:02 UTC Nessie2018:1hour+

2018-08-10 17:45:02 UTC Nessie2018:1hour+

2018-08-09 16:10:45 UTC crt.sh ID:670386381 Nessie2018 Log Entry Index:48928470 Nessie2018:1hour+

2018-08-08 17:45:02 UTC Nessie2018:1hour+

2018-08-07 17:30:56 UTC crt.sh ID:666726344 Nessie2018 Log Entry Index:46671439 Nessie2018:1hour+

2018-08-06 18:30:07 UTC crt.sh ID:665077543 Nessie2018 Log Entry Index:45609034 Nessie2018:1hour+

2018-08-05 17:45:03 UTC Nessie2018:1hour+

2018-08-04 17:45:03 UTC Nessie2018:1hour+

2018-08-03 17:45:03 UTC Nessie2018:1hour+

2018-08-02 07:41:51 UTC crt.sh ID:659260474 Nessie2018 Log Entry Index:41994859 Nessie2018:1hour+

2018-08-01 17:47:05 UTC Nessie2018:1hour+

2018-07-31 01:00:05 UTC crt.sh ID:656113055 Nessie2018 Log Entry Index:39876256 Nessie2018:1hour+

2018-07-31 00:11:31 UTC Nessie2018:1hour+

2018-07-30 23:26:12 UTC Nessie2018:1hour+

2018-07-30 23:26:12 UTC Nessie2018:Right time

2018-07-30 16:36:51 UTC Nessie2018:Right time

2018-07-29 12:07:56 UTC Nessie2018:Right time

2018-07-28 16:07:43 UTC Nessie2018:Right time

2018-07-27 20:51:35 UTC Nessie2018:Right time

2018-07-27 18:31:27 UTC crt.sh ID:647529271 Nessie2018 Log Entry Index:37387486 Nessie2018:Right time

2018-07-16 06:44:45 UTC Nessie2018:Right time

2018-07-13 22:32:57 UTC crt.sh ID:602732249 Nessie2018 Log Entry Index:25454672 Nessie2018:Right time

robstradling commented 6 years ago

All of the 1hr-fast SCT timestamps on the crt.sh database have now been fixed.

Many thanks to @AGWA for sharing the last few months' worth of SCT timestamp data from his Cert Spotter database, which enabled me to implement this fix.