query.sql

WITH inactive_nodes 
     AS (SELECT certname 
         FROM   certnames 
         WHERE  ( deactivated IS NOT NULL 
                   OR expired IS NOT NULL )) 
SELECT reports.certname                     AS certname, 
       Encode(reports.hash :: bytea, 'hex') AS hash, 
       reports.end_time                     AS end_time, 
       reports.noop                         AS noop, 
       report_statuses.status               AS status, 
       reports.noop_pending                 AS noop_pending, 
       reports.corrective_change            AS corrective_change 
FROM   reports 
       left join environments 
              ON environments.id = reports.environment_id 
       left join producers 
              ON producers.id = reports.producer_id 
       left join report_statuses 
              ON reports.status_id = report_statuses.id 
WHERE  ( ( Encode(reports.hash :: bytea, 'hex') ) IN (SELECT 
                Encode(reports.hash :: bytea, 'hex') AS latest_report_hash 
                                                      FROM   certnames 
                left join catalogs 
                       ON catalogs.certname 
                          = 
                certnames.certname 
                left join factsets fs 
                       ON certnames.certname 
                          = fs.certname 
                left join reports 
                       ON 
                certnames.latest_report_id = reports.id 
                  left join environments 
         catalog_environment 
                                                                    ON 
                  catalog_environment.id = catalogs.environment_id 
                left join report_statuses 
                       ON reports.status_id 
                          = 
         report_statuses.id 
                left join environments 
                          facts_environment 
                       ON 
         facts_environment.id = fs.environment_id 
                  left join environments 
         reports_environment 
                                                                    ON 
                  reports_environment.id = reports.environment_id 
                                                      WHERE 
                  certnames.certname = ANY ( $1 )) )