{"cells":[{"cell_type":"markdown","id":"07c4f280","metadata":{},"source":["## 1 connect to database"]},{"cell_type":"code","execution_count":26,"id":"275a1e94","metadata":{"scrolled":true},"outputs":[{"data":{"text/plain":[""]},"execution_count":26,"metadata":{},"output_type":"execute_result"}],"source":["import pymysql\n","conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='123456',database='CertificateDB',charset='utf8')\n","conn"]},{"cell_type":"markdown","id":"84f61e5c","metadata":{},"source":["## 2 query data"]},{"cell_type":"code","execution_count":27,"id":"a0447bd5","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
Tables_in_CertificateDB
0certificate
\n","
"],"text/plain":[" Tables_in_CertificateDB\n","0 certificate"]},"execution_count":27,"metadata":{},"output_type":"execute_result"}],"source":["import pandas as pd\n","pd.read_sql(\"show tables\",con=conn)"]},{"cell_type":"code","execution_count":28,"id":"6873302e","metadata":{"scrolled":false},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
FieldTypeNullKeyDefaultExtra
0idint(11)NOPRINoneauto_increment
1hostvarchar(256)NONone
2open443varchar(256)YESNone
3errorvarchar(256)YESNone
4ssl_errorvarchar(256)YESNone
5certificate_versionvarchar(10)YESNone
6certificate_algorithmvarchar(256)YESNone
7issuer_countryvarchar(256)YESNone
8issued_organizationvarchar(256)YESNone
9public_key_typevarchar(256)YESNone
10public_key_bitsvarchar(256)YESNone
11expiredvarchar(256)YESNone
12valid_fromvarchar(256)YESNone
13valid_tovarchar(256)YESNone
14validity_daysvarchar(256)YESNone
15valid_days_leftvarchar(256)YESNone
16ocsp_statusvarchar(256)YESNone
17ocsp_errorvarchar(256)YESNone
18crl_statusvarchar(256)YESNone
19crl_reasonvarchar(256)YESNone
\n","
"],"text/plain":[" Field Type Null Key Default Extra\n","0 id int(11) NO PRI None auto_increment\n","1 host varchar(256) NO None \n","2 open443 varchar(256) YES None \n","3 error varchar(256) YES None \n","4 ssl_error varchar(256) YES None \n","5 certificate_version varchar(10) YES None \n","6 certificate_algorithm varchar(256) YES None \n","7 issuer_country varchar(256) YES None \n","8 issued_organization varchar(256) YES None \n","9 public_key_type varchar(256) YES None \n","10 public_key_bits varchar(256) YES None \n","11 expired varchar(256) YES None \n","12 valid_from varchar(256) YES None \n","13 valid_to varchar(256) YES None \n","14 validity_days varchar(256) YES None \n","15 valid_days_left varchar(256) YES None \n","16 ocsp_status varchar(256) YES None \n","17 ocsp_error varchar(256) YES None \n","18 crl_status varchar(256) YES None \n","19 crl_reason varchar(256) YES None "]},"execution_count":28,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"desc certificate\",con=conn)"]},{"cell_type":"code","execution_count":29,"id":"0483819d","metadata":{},"outputs":[],"source":["def exec_sql(conn, sql):\n"," with conn.cursor() as cursor:\n"," cursor.execute(sql)\n"," conn.commit()\n","\n"," \n","def query_sql(conn,sql):\n"," with conn.cursor() as cursor:\n"," cursor.execute(sql)\n"," result = cursor.fetchall()\n"," return result"]},{"cell_type":"markdown","id":"eb7942b6","metadata":{},"source":["## 3 Analysis the data"]},{"cell_type":"markdown","id":"47639159","metadata":{},"source":["### 3.1 check whether host443 open"]},{"cell_type":"code","execution_count":30,"id":"e5f718a7","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
open443num
004318
11112997
2null2685
\n","
"],"text/plain":[" open443 num\n","0 0 4318\n","1 1 112997\n","2 null 2685"]},"execution_count":30,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT open443, COUNT(open443) AS num\n"," FROM certificate\n"," GROUP BY open443\"\"\",con=conn)"]},{"cell_type":"markdown","id":"a3b79523","metadata":{},"source":["## 3.2 analysis error"]},{"cell_type":"code","execution_count":31,"id":"470fe4cc","metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
open443errornum
00[Errno 111] Connection refused3767
10[Errno -5] No address associated with hostname529
20[Errno -2] Name or service not known10
30[Errno -3] Temporary failure in name resolution5
40[('SSL routines', 'tls_process_server_certific...1
50(-1, 'Unexpected EOF')1
61[('SSL routines', 'tls_process_server_certific...3527
71(-1, 'Unexpected EOF')750
81[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...240
91[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...151
101[('SSL routines', 'ssl3_get_record', 'wrong ve...142
111[Errno 111] Connection refused100
121[('SSL routines', 'tls_process_server_certific...63
131[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...49
141[('rsa routines', 'RSA_padding_check_PKCS1_typ...2
151[('SSL routines', 'ssl3_read_bytes', 'invalid ...1
161[('SSL routines', 'tls_process_ske_dhe', 'dh k...1
171[('SSL routines', 'tls_process_server_certific...1
18null[Errno -2] Name or service not known2098
19null[Errno -3] Temporary failure in name resolution533
20null[Errno -5] No address associated with hostname54
\n","
"],"text/plain":[" open443 error num\n","0 0 [Errno 111] Connection refused 3767\n","1 0 [Errno -5] No address associated with hostname 529\n","2 0 [Errno -2] Name or service not known 10\n","3 0 [Errno -3] Temporary failure in name resolution 5\n","4 0 [('SSL routines', 'tls_process_server_certific... 1\n","5 0 (-1, 'Unexpected EOF') 1\n","6 1 [('SSL routines', 'tls_process_server_certific... 3527\n","7 1 (-1, 'Unexpected EOF') 750\n","8 1 [('SSL routines', 'ssl3_read_bytes', 'sslv3 al... 240\n","9 1 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 al... 151\n","10 1 [('SSL routines', 'ssl3_get_record', 'wrong ve... 142\n","11 1 [Errno 111] Connection refused 100\n","12 1 [('SSL routines', 'tls_process_server_certific... 63\n","13 1 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 un... 49\n","14 1 [('rsa routines', 'RSA_padding_check_PKCS1_typ... 2\n","15 1 [('SSL routines', 'ssl3_read_bytes', 'invalid ... 1\n","16 1 [('SSL routines', 'tls_process_ske_dhe', 'dh k... 1\n","17 1 [('SSL routines', 'tls_process_server_certific... 1\n","18 null [Errno -2] Name or service not known 2098\n","19 null [Errno -3] Temporary failure in name resolution 533\n","20 null [Errno -5] No address associated with hostname 54"]},"execution_count":31,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT open443,error,COUNT(*) AS num\n"," FROM certificate\n"," WHERE NOT (error = 'null')\n"," GROUP BY open443,error\n"," ORDER BY open443,num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":32,"id":"d6a09d91","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
errornum
0[Errno 111] Connection refused3867
1[('SSL routines', 'tls_process_server_certific...3528
2[Errno -2] Name or service not known2108
3(-1, 'Unexpected EOF')751
4[Errno -5] No address associated with hostname583
5[Errno -3] Temporary failure in name resolution538
6[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...240
7[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...151
8[('SSL routines', 'ssl3_get_record', 'wrong ve...142
9[('SSL routines', 'tls_process_server_certific...63
10[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...49
11[('rsa routines', 'RSA_padding_check_PKCS1_typ...2
12[('SSL routines', 'tls_process_server_certific...1
13[('SSL routines', 'ssl3_read_bytes', 'invalid ...1
14[('SSL routines', 'tls_process_ske_dhe', 'dh k...1
\n","
"],"text/plain":[" error num\n","0 [Errno 111] Connection refused 3867\n","1 [('SSL routines', 'tls_process_server_certific... 3528\n","2 [Errno -2] Name or service not known 2108\n","3 (-1, 'Unexpected EOF') 751\n","4 [Errno -5] No address associated with hostname 583\n","5 [Errno -3] Temporary failure in name resolution 538\n","6 [('SSL routines', 'ssl3_read_bytes', 'sslv3 al... 240\n","7 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 al... 151\n","8 [('SSL routines', 'ssl3_get_record', 'wrong ve... 142\n","9 [('SSL routines', 'tls_process_server_certific... 63\n","10 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 un... 49\n","11 [('rsa routines', 'RSA_padding_check_PKCS1_typ... 2\n","12 [('SSL routines', 'tls_process_server_certific... 1\n","13 [('SSL routines', 'ssl3_read_bytes', 'invalid ... 1\n","14 [('SSL routines', 'tls_process_ske_dhe', 'dh k... 1"]},"execution_count":32,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT error,COUNT(error) AS num\n"," FROM certificate\n"," WHERE NOT (error = 'null')\n"," GROUP BY error\n"," ORDER BY num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":33,"id":"bb205267","metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
open443errorssl_errornum
00[Errno 111] Connection refused03545
10[Errno -5] No address associated with hostname0495
20[Errno 111] Connection refused1088
30[Errno 111] Connection refused2074
40[Errno 111] Connection refused1859
...............
56null[Errno -3] Temporary failure in name resolution107
57null[Errno -3] Temporary failure in name resolution206
58null[Errno -5] No address associated with hostname182
59null[Errno -5] No address associated with hostname201
60null[Errno -2] Name or service not known191
\n","

61 rows × 4 columns

\n","
"],"text/plain":[" open443 error ssl_error num\n","0 0 [Errno 111] Connection refused 0 3545\n","1 0 [Errno -5] No address associated with hostname 0 495\n","2 0 [Errno 111] Connection refused 10 88\n","3 0 [Errno 111] Connection refused 20 74\n","4 0 [Errno 111] Connection refused 18 59\n",".. ... ... ... ...\n","56 null [Errno -3] Temporary failure in name resolution 10 7\n","57 null [Errno -3] Temporary failure in name resolution 20 6\n","58 null [Errno -5] No address associated with hostname 18 2\n","59 null [Errno -5] No address associated with hostname 20 1\n","60 null [Errno -2] Name or service not known 19 1\n","\n","[61 rows x 4 columns]"]},"execution_count":33,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT open443,error,ssl_error, COUNT(*) AS num\n"," FROM certificate\n"," GROUP BY open443,error,ssl_error\n"," ORDER BY open443, num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":34,"id":"e4139812","metadata":{"scrolled":false},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
ssl_errornum
0201532
1101222
2181193
31950
\n","
"],"text/plain":[" ssl_error num\n","0 20 1532\n","1 10 1222\n","2 18 1193\n","3 19 50"]},"execution_count":34,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT ssl_error,COUNT(ssl_error) AS num\n"," FROM certificate \n"," WHERE NOT (ssl_error = 'null' or ssl_error = '0')\n"," GROUP BY ssl_error\n"," ORDER BY num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":35,"id":"59f73998","metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
errorssl_errornum
0[Errno 111] Connection refused03641
1[Errno -2] Name or service not known02005
2(-1, 'Unexpected EOF')0707
3[Errno -5] No address associated with hostname0546
4[Errno -3] Temporary failure in name resolution0515
5[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...0226
6[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...0146
7[('SSL routines', 'ssl3_get_record', 'wrong ve...0131
8[('SSL routines', 'tls_process_server_certific...059
9[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...047
10[('rsa routines', 'RSA_padding_check_PKCS1_typ...02
11[('SSL routines', 'ssl3_read_bytes', 'invalid ...01
12[('SSL routines', 'tls_process_ske_dhe', 'dh k...01
13[('SSL routines', 'tls_process_server_certific...01
14[('SSL routines', 'tls_process_server_certific...101040
15[Errno 111] Connection refused1089
16[Errno -2] Name or service not known1036
17[Errno -5] No address associated with hostname1018
18(-1, 'Unexpected EOF')1016
19[Errno -3] Temporary failure in name resolution108
20[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...107
21[('SSL routines', 'ssl3_get_record', 'wrong ve...105
22[('SSL routines', 'tls_process_server_certific...101
23[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...101
24[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...101
25[('SSL routines', 'tls_process_server_certific...181053
26[Errno 111] Connection refused1861
27[Errno -2] Name or service not known1839
28(-1, 'Unexpected EOF')1814
29[Errno -3] Temporary failure in name resolution189
30[Errno -5] No address associated with hostname188
31[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...184
32[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...182
33[('SSL routines', 'ssl3_get_record', 'wrong ve...182
34[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...181
35[('SSL routines', 'tls_process_server_certific...1948
36[Errno 111] Connection refused191
37[Errno -2] Name or service not known191
38[('SSL routines', 'tls_process_server_certific...201387
39[Errno 111] Connection refused2075
40[Errno -2] Name or service not known2027
41(-1, 'Unexpected EOF')2014
42[Errno -5] No address associated with hostname2011
43[Errno -3] Temporary failure in name resolution206
44[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...205
45[('SSL routines', 'ssl3_get_record', 'wrong ve...204
46[('SSL routines', 'tls_process_server_certific...203
\n","
"],"text/plain":[" error ssl_error num\n","0 [Errno 111] Connection refused 0 3641\n","1 [Errno -2] Name or service not known 0 2005\n","2 (-1, 'Unexpected EOF') 0 707\n","3 [Errno -5] No address associated with hostname 0 546\n","4 [Errno -3] Temporary failure in name resolution 0 515\n","5 [('SSL routines', 'ssl3_read_bytes', 'sslv3 al... 0 226\n","6 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 al... 0 146\n","7 [('SSL routines', 'ssl3_get_record', 'wrong ve... 0 131\n","8 [('SSL routines', 'tls_process_server_certific... 0 59\n","9 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 un... 0 47\n","10 [('rsa routines', 'RSA_padding_check_PKCS1_typ... 0 2\n","11 [('SSL routines', 'ssl3_read_bytes', 'invalid ... 0 1\n","12 [('SSL routines', 'tls_process_ske_dhe', 'dh k... 0 1\n","13 [('SSL routines', 'tls_process_server_certific... 0 1\n","14 [('SSL routines', 'tls_process_server_certific... 10 1040\n","15 [Errno 111] Connection refused 10 89\n","16 [Errno -2] Name or service not known 10 36\n","17 [Errno -5] No address associated with hostname 10 18\n","18 (-1, 'Unexpected EOF') 10 16\n","19 [Errno -3] Temporary failure in name resolution 10 8\n","20 [('SSL routines', 'ssl3_read_bytes', 'sslv3 al... 10 7\n","21 [('SSL routines', 'ssl3_get_record', 'wrong ve... 10 5\n","22 [('SSL routines', 'tls_process_server_certific... 10 1\n","23 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 un... 10 1\n","24 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 al... 10 1\n","25 [('SSL routines', 'tls_process_server_certific... 18 1053\n","26 [Errno 111] Connection refused 18 61\n","27 [Errno -2] Name or service not known 18 39\n","28 (-1, 'Unexpected EOF') 18 14\n","29 [Errno -3] Temporary failure in name resolution 18 9\n","30 [Errno -5] No address associated with hostname 18 8\n","31 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 al... 18 4\n","32 [('SSL routines', 'ssl3_read_bytes', 'sslv3 al... 18 2\n","33 [('SSL routines', 'ssl3_get_record', 'wrong ve... 18 2\n","34 [('SSL routines', 'ssl3_read_bytes', 'tlsv1 un... 18 1\n","35 [('SSL routines', 'tls_process_server_certific... 19 48\n","36 [Errno 111] Connection refused 19 1\n","37 [Errno -2] Name or service not known 19 1\n","38 [('SSL routines', 'tls_process_server_certific... 20 1387\n","39 [Errno 111] Connection refused 20 75\n","40 [Errno -2] Name or service not known 20 27\n","41 (-1, 'Unexpected EOF') 20 14\n","42 [Errno -5] No address associated with hostname 20 11\n","43 [Errno -3] Temporary failure in name resolution 20 6\n","44 [('SSL routines', 'ssl3_read_bytes', 'sslv3 al... 20 5\n","45 [('SSL routines', 'ssl3_get_record', 'wrong ve... 20 4\n","46 [('SSL routines', 'tls_process_server_certific... 20 3"]},"execution_count":35,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT error, ssl_error,COUNT(*) AS num\n"," FROM certificate\n"," WHERE NOT (error = 'null')\n"," GROUP BY error,ssl_error\n"," ORDER BY ssl_error,num DESC\"\"\",con=conn)"]},{"cell_type":"markdown","id":"1d32956f","metadata":{},"source":["## 3.3 analysis issuers(country and organization)"]},{"cell_type":"code","execution_count":36,"id":"1db9402d","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
issuer_countrynumpercentage
0US8842481.89303
1GB89988.33341
2BE45554.21857
3CN18631.72540
4PL7940.73536
5AT6380.59088
6NL5390.49919
7JP5360.49641
8FR3150.29173
9LV3140.29081
\n","
"],"text/plain":[" issuer_country num percentage\n","0 US 88424 81.89303\n","1 GB 8998 8.33341\n","2 BE 4555 4.21857\n","3 CN 1863 1.72540\n","4 PL 794 0.73536\n","5 AT 638 0.59088\n","6 NL 539 0.49919\n","7 JP 536 0.49641\n","8 FR 315 0.29173\n","9 LV 314 0.29081"]},"execution_count":36,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT issuer_country,\n"," COUNT(issuer_country) AS num,\n"," COUNT(issuer_country)*100.0/(SELECT COUNT(*) \n"," FROM certificate\n"," WHERE NOT (issued_organization = 'null')) AS percentage\n"," FROM certificate\n"," WHERE NOT (issuer_country = 'null')\n"," GROUP BY issuer_country\n"," ORDER BY num DESC\n"," LIMIT 10\"\"\",con=conn)"]},{"cell_type":"code","execution_count":37,"id":"fe4b2b61","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
issued_organizationnumpercentage
0Let's Encrypt3083428.55661
1Cloudflare, Inc.2686024.87613
2DigiCert Inc1365512.64645
3Sectigo Limited88358.18245
4Amazon70006.48298
5GlobalSign nv-sa45554.21857
6GoDaddy.com, Inc.31602.92660
7cPanel, Inc.30642.83769
8Google Trust Services LLC14941.38365
9TrustAsia Technologies, Inc.14511.34383
\n","
"],"text/plain":[" issued_organization num percentage\n","0 Let's Encrypt 30834 28.55661\n","1 Cloudflare, Inc. 26860 24.87613\n","2 DigiCert Inc 13655 12.64645\n","3 Sectigo Limited 8835 8.18245\n","4 Amazon 7000 6.48298\n","5 GlobalSign nv-sa 4555 4.21857\n","6 GoDaddy.com, Inc. 3160 2.92660\n","7 cPanel, Inc. 3064 2.83769\n","8 Google Trust Services LLC 1494 1.38365\n","9 TrustAsia Technologies, Inc. 1451 1.34383"]},"execution_count":37,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT issued_organization,\n"," COUNT(issued_organization) AS num,\n"," COUNT(issued_organization)*100.0/(SELECT COUNT(*) \n"," FROM certificate\n"," WHERE NOT (issued_organization = 'null')) AS percentage\n"," FROM certificate\n"," WHERE NOT (issued_organization = 'null')\n"," GROUP BY issued_organization\n"," ORDER BY num DESC\n"," LIMIT 10\"\"\",con=conn)"]},{"cell_type":"markdown","id":"91303c49","metadata":{},"source":["## 3.4 analysis algorithm and public key & bits"]},{"cell_type":"code","execution_count":38,"id":"36e22b1c","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
certificate_algorithmnumpercentage
0sha256WithRSAEncryption7603270.41630
1ecdsa-with-SHA2562697224.97986
2ecdsa-with-SHA38436033.33688
3sha384WithRSAEncryption13151.21787
4sha512WithRSAEncryption530.04909
\n","
"],"text/plain":[" certificate_algorithm num percentage\n","0 sha256WithRSAEncryption 76032 70.41630\n","1 ecdsa-with-SHA256 26972 24.97986\n","2 ecdsa-with-SHA384 3603 3.33688\n","3 sha384WithRSAEncryption 1315 1.21787\n","4 sha512WithRSAEncryption 53 0.04909"]},"execution_count":38,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT certificate_algorithm,\n"," COUNT(certificate_algorithm) AS num,\n"," COUNT(certificate_algorithm)*100.0/(SELECT COUNT(*) \n"," FROM certificate \n"," WHERE NOT (certificate_algorithm = 'null')) AS percentage\n"," FROM certificate\n"," WHERE NOT (certificate_algorithm = 'null')\n"," GROUP BY certificate_algorithm\n"," ORDER BY num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":39,"id":"b5f9c81e","metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
public_key_typepublic_key_bitsnumpercentage
0620486909163.98796
16409646564.31211
2630723180.29451
36309610.00093
44082563300430.56633
54083849050.83816
\n","
"],"text/plain":[" public_key_type public_key_bits num percentage\n","0 6 2048 69091 63.98796\n","1 6 4096 4656 4.31211\n","2 6 3072 318 0.29451\n","3 6 3096 1 0.00093\n","4 408 256 33004 30.56633\n","5 408 384 905 0.83816"]},"execution_count":39,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT public_key_type,\n"," public_key_bits,\n"," COUNT(*) AS num,\n"," COUNT(*)*100.0/(SELECT COUNT(*) \n"," FROM certificate \n"," WHERE NOT (public_key_bits = 'null')) AS percentage\n"," FROM certificate\n"," WHERE NOT (public_key_bits = 'null')\n"," GROUP BY public_key_type,public_key_bits\n"," ORDER BY public_key_type DESC,num DESC\"\"\",con=conn)"]},{"cell_type":"markdown","id":"34bd5809","metadata":{},"source":["## 3.5 check expire status"]},{"cell_type":"code","execution_count":40,"id":"4858ce81","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
expirednum
00107975
1null12025
\n","
"],"text/plain":[" expired num\n","0 0 107975\n","1 null 12025"]},"execution_count":40,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT expired,COUNT(expired) AS num\n"," FROM certificate\n"," GROUP BY expired\n"," ORDER BY num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":41,"id":"02fc857c","metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
validity_daysnum
08931741
136424674
236513107
33966614
43946006
.........
4366361
4371901
4381201
4391011
440881
\n","

441 rows × 2 columns

\n","
"],"text/plain":[" validity_days num\n","0 89 31741\n","1 364 24674\n","2 365 13107\n","3 396 6614\n","4 394 6006\n",".. ... ...\n","436 636 1\n","437 190 1\n","438 120 1\n","439 101 1\n","440 88 1\n","\n","[441 rows x 2 columns]"]},"execution_count":41,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT validity_days, COUNT(validity_days) AS num\n"," FROM certificate\n"," WHERE NOT (validity_days = 'null')\n"," GROUP BY validity_days\n"," ORDER BY num DESC\"\"\",con=conn)"]},{"cell_type":"markdown","id":"947dd9e6","metadata":{},"source":["## 3.6 CRL check"]},{"cell_type":"code","execution_count":42,"id":"11c0013c","metadata":{"scrolled":true},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
crl_statusnum
0GOOD64143
1FAILED43824
2REVOKED8
\n","
"],"text/plain":[" crl_status num\n","0 GOOD 64143\n","1 FAILED 43824\n","2 REVOKED 8"]},"execution_count":42,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT crl_status, COUNT(crl_status) AS num\n"," FROM certificate\n"," WHERE NOT (crl_status = 'null')\n"," GROUP BY crl_status\n"," ORDER BY num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":43,"id":"e9c8a552","metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
crl_reasonnum
0CRL ERROR: Not Found CRL Extension43233
1CRL ERROR: No connection adapters were found f...25
2CRL ERROR: No connection adapters were found f...19
3CRL ERROR: No connection adapters were found f...13
4CRL ERROR: Invalid URL '<Name(C=JP,O=SECOM Tru...11
.........
499CRL ERROR: HTTPConnectionPool(host='crl4.digic...1
500CRL ERROR: HTTPConnectionPool(host='crl3.digic...1
501CRL ERROR: HTTPConnectionPool(host='crl4.digic...1
502CRL ERROR: HTTPConnectionPool(host='crl3.digic...1
503CRL ERROR: HTTPConnectionPool(host='crl3.digic...1
\n","

504 rows × 2 columns

\n","
"],"text/plain":[" crl_reason num\n","0 CRL ERROR: Not Found CRL Extension 43233\n","1 CRL ERROR: No connection adapters were found f... 25\n","2 CRL ERROR: No connection adapters were found f... 19\n","3 CRL ERROR: No connection adapters were found f... 13\n","4 CRL ERROR: Invalid URL '\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
ocsp_statusnum
0GOOD107387
1REVOKED28
\n",""],"text/plain":[" ocsp_status num\n","0 GOOD 107387\n","1 REVOKED 28"]},"execution_count":44,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT ocsp_status, COUNT(ocsp_status) AS num\n"," FROM certificate\n"," WHERE NOT (ocsp_status = 'null')\n"," GROUP BY ocsp_status\n"," ORDER BY num DESC\"\"\",con=conn)"]},{"cell_type":"code","execution_count":45,"id":"dc7c480e","metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
ocsp_errornum
0Error: get_ocsp_response: Request timeout for ...268
1Error: timed out31
2Error: get_ocsp_response: Unknown Connection E...15
3Error: build_ocsp_request: Unable to load x509...2
4Error: get_certificate_chain: Connection to be...1
.........
243Error: get_certificate_chain: Connection to ti...1
244Error: get_certificate_chain: www.leqee.com di...1
245Error: get_certificate_chain: Client Certifica...1
246Error: get_certificate_chain: Connection to ww...1
247Error: get_certificate_chain: www.wanxue.cn di...1
\n","

248 rows × 2 columns

\n","
"],"text/plain":[" ocsp_error num\n","0 Error: get_ocsp_response: Request timeout for ... 268\n","1 Error: timed out 31\n","2 Error: get_ocsp_response: Unknown Connection E... 15\n","3 Error: build_ocsp_request: Unable to load x509... 2\n","4 Error: get_certificate_chain: Connection to be... 1\n",".. ... ...\n","243 Error: get_certificate_chain: Connection to ti... 1\n","244 Error: get_certificate_chain: www.leqee.com di... 1\n","245 Error: get_certificate_chain: Client Certifica... 1\n","246 Error: get_certificate_chain: Connection to ww... 1\n","247 Error: get_certificate_chain: www.wanxue.cn di... 1\n","\n","[248 rows x 2 columns]"]},"execution_count":45,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql(\"\"\"SELECT ocsp_error, COUNT(ocsp_error) AS num\n"," FROM certificate\n"," WHERE NOT (ocsp_error = 'null')\n"," GROUP BY ocsp_error\n"," ORDER BY num DESC\"\"\",con=conn)"]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.10"}},"nbformat":4,"nbformat_minor":5}