1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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;
|
