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;