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
27
28
29
30
31
|
-- search CISA vulnerabilities
SELECT COALESCE(json_group_array(json_object(
'vuln_id', a.vuln_id,
'cve_id', c.nvd_id,
'vendor', d.name,
'product', e.name,
'name', b.name,
'description', b.description,
'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)
JOIN cisa_vendors d
ON (d.vendor_id = b.vendor_id)
JOIN cisa_products e
ON (e.product_id = b.product_id)
ORDER BY a.min_rank;
|
