From fea7af49a9da16574646b8e307115f9c65d23489 Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Wed, 16 Mar 2022 08:07:26 -0400
Subject: add dbstore/sql/cisa

---
 dbstore/sql/cisa/search.sql | 26 ++++++++++++++++++++++++++
 1 file changed, 26 insertions(+)
 create mode 100644 dbstore/sql/cisa/search.sql

(limited to 'dbstore/sql/cisa/search.sql')

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;
-- 
cgit v1.2.3