diff options
Diffstat (limited to 'dbstore/sql/cisa/search.sql')
-rw-r--r-- | dbstore/sql/cisa/search.sql | 26 |
1 files changed, 26 insertions, 0 deletions
diff --git a/dbstore/sql/cisa/search.sql b/dbstore/sql/cisa/search.sql new file mode 100644 index 0000000..daaf5cf --- /dev/null +++ b/dbstore/sql/cisa/search.sql @@ -0,0 +1,26 @@ +-- search CISA vulnerabilities +SELECT COALESCE(json_group_array(json_object( + 'vuln_id', a.vuln_id, + 'cve_id', c.nvd_id, + 'vendor', b.vendor, + 'product', b.product, + 'name', b.name, + 'added_at', b.added_at, + 'action', b.action, + 'due_at', b.due_at, + 'rank', a.min_rank + )), '[]') + + FROM ( + SELECT vuln_id, + MIN(rank) AS min_rank + FROM cisa_vulns_fts + WHERE cisa_vulns_fts MATCH :q + GROUP BY vuln_id + ) a + JOIN cisa_vulns b + ON (b.vuln_id = a.vuln_id) + JOIN cisa_vuln_cve_ids c + ON (c.vuln_id = b.vuln_id) + + ORDER BY a.min_rank; |