wi12.unix7.org

#
#  Author, Copyright: Oleg Borodin <onborodin@gmail.com>
#
require 'openssl'
require 'pg'
 
pghost = "xxxxxxxxx"
pguser = "xxxxx"
pgpass = "xxxxxx"
dbname = "xxxxxxx"
 
conn = PG.connect(host: pghost, dbname: dbname, user: pguser, password: pgpass)
 
puts "%26s   %s" % [ "cert name", "not before / not after" ]
 
Dir["/data/xxxxxxxxxxxx/*.crt"].sort.each do |crt_name|
 
    raw = File.read crt_name
    cert = OpenSSL::X509::Certificate.new raw
 
    cn = crt_name.gsub "_", " "
    cn = File.basename cn, ".crt"
 
    conn.exec("select name, invaldate from xxxxxx_view_certs where name = '#{cn}' 
                                  and issuer = xxxx and invaldate is null limit 1;") do |result|
        result.each do |row|
            name = row['name']
            if cert.not_after > Time.now
                puts "%26s : %s : %s" % [ name, cert.not_before, cert.not_after ]
            end
        end
    end
end

                 cert name   not before / not after
                 Averina O : 2019-01-22 11:28:00 UTC : 2029-01-22 11:28:00 UTC
                Borodin ON : 2015-08-05 10:00:00 UTC : 2030-08-05 10:00:00 UTC
              Bulgakova DN : 2016-06-24 07:08:00 UTC : 2026-06-24 07:08:00 UTC
                 Cheglov V : 2019-03-29 10:29:00 UTC : 2020-03-29 10:29:00 UTC
                  Dyhych D : 2017-04-14 10:09:00 UTC : 2027-04-14 10:09:00 UTC
            Goldina Elvira : 2016-07-29 05:53:00 UTC : 2026-07-29 05:53:00 UTC
               Gufayzen MI : 2019-04-02 12:50:00 UTC : 2020-04-02 12:50:00 UTC
                Jurtaev SA : 2015-08-05 08:13:00 UTC : 2025-08-05 08:13:00 UTC
             Kashnikova OA : 2015-10-08 12:33:00 UTC : 2025-10-08 12:33:00 UTC
                 Korolev D : 2019-02-14 11:28:00 UTC : 2029-02-14 11:28:00 UTC
             Kozlovskaya N : 2017-05-22 14:48:00 UTC : 2027-05-22 14:48:00 UTC
                 Kukarin P : 2019-10-14 00:00:00 UTC : 2020-10-13 23:59:59 UTC
       Kuznetcov Aleksandr : 2015-11-30 15:59:00 UTC : 2025-11-30 15:59:00 UTC
        Kuznetcov А SMART1 : 2018-12-01 13:12:00 UTC : 2020-12-01 13:12:00 UTC
                   Losev V : 2015-12-14 09:37:00 UTC : 2025-12-14 09:37:00 UTC
          MOB Bulgakova DV : 2018-04-14 05:53:00 UTC : 2028-04-14 05:53:00 UTC
             MOB Makarov D : 2018-04-14 05:53:00 UTC : 2028-04-14 05:53:00 UTC
            MOB Nikonova N : 2018-04-14 05:53:00 UTC : 2028-04-14 05:53:00 UTC
                 Makarov D : 2017-06-23 08:43:00 UTC : 2027-06-23 08:43:00 UTC
              Makarov D WS : 2017-06-23 08:43:00 UTC : 2027-06-23 08:43:00 UTC
                Narojnyi A : 2016-05-31 09:40:00 UTC : 2026-05-31 09:40:00 UTC
               Nikonova NP : 2016-03-29 08:26:00 UTC : 2026-03-29 08:26:00 UTC
                  Popova A : 2019-04-02 12:49:00 UTC : 2020-04-02 12:49:00 UTC
                 Popova EE : 2019-05-16 07:54:00 UTC : 2020-05-16 07:54:00 UTC
          Romanov D SMART1 : 2018-12-01 13:12:00 UTC : 2020-12-01 13:12:00 UTC
            Sokolov NYU 01 : 2015-11-05 09:22:00 UTC : 2025-11-05 09:22:00 UTC
            Sokolov NYU 02 : 2015-11-05 09:19:00 UTC : 2025-11-05 09:19:00 UTC
                   Temp 01 : 2015-10-24 19:09:00 UTC : 2025-10-24 19:09:00 UTC
                   Temp 02 : 2015-10-24 19:10:00 UTC : 2025-10-24 19:10:00 UTC
                Velutin RS : 2015-08-05 08:01:00 UTC : 2025-08-05 08:01:00 UTC
          Velutin R SMART1 : 2018-12-01 13:12:00 UTC : 2020-12-01 13:12:00 UTC
              Yakovleva NV : 2016-03-11 14:00:00 UTC : 2026-03-11 14:00:00 UTC
                 Zaripov A : 2017-06-19 13:42:00 UTC : 2027-06-19 13:42:00 UTC
  Zaripov Aleksandr SEM-01 : 2015-11-25 08:34:00 UTC : 2025-11-25 08:34:00 UTC
  Zaripov Aleksandr SEM-02 : 2015-11-25 08:36:00 UTC : 2025-11-25 08:36:00 UTC