reportCisaCatalogByVendor

/*
-- author: jkopacko
  += Descriptive names: vendorName, vendorName1, vendorName2 (and more if required)
  += Variable type: String
  += Value: Cisco, VMware, Apache (and many more)
  += Version 1.0 - 05/17/22
  += Query type: Live Discover - choose only ONE machine 
  += OS Support: Windows
  += Source: CISA Playbook | https://www.cisa.gov/sites/default/files/publications/Federal_Government_Cybersecurity_Incident_and_Vulnerability_Response_Playbooks_508C.pdf
*/

-- Grab JSON File
WITH 
   cisaExploitsJSON AS (
      SELECT result 
      FROM curl 
      WHERE url = 'https://www.cisa.gov/sites/default/files/feeds/known_exploited_vulnerabilities.json'
      ),
-- Extract list of vulnerabilities
   cisaExploitsFormatted AS (
      SELECT key, value 
      FROM cisaExploitsJSON, json_each(cisaExploitsJSON.result, '$.vulnerabilities')
      ),
-- Extract values into columns
   cisaExploitsTable (rowIndex, cveID, vendorProject, product, vulnerability, dateAdded, shortDescription, requiredAction) AS (
      SELECT cisaExploitsFormatted.key rowIndex,
            json_extract(cisaExploitsFormatted.value, '$.cveID') cveID,
            json_extract(cisaExploitsFormatted.value, '$.vendorProject') vendorProject,
            json_extract(cisaExploitsFormatted.value, '$.product') product,
            json_extract(cisaExploitsFormatted.value, '$.vulnerabilityName') vulnerability,
            json_extract(cisaExploitsFormatted.value, '$.dateAdded') AS dateAdded,
            json_extract(cisaExploitsFormatted.value, '$.shortDescription') AS shortDescription,
            json_extract(cisaExploitsFormatted.value, '$.requiredAction') AS requiredAction
      FROM cisaExploitsFormatted
      ORDER BY dateAdded DESC
      )
-- Return all results like your variable names 
SELECT *
FROM cisaExploitsTable
--You can add as many vendorNames as you like with the code <OR vendorProject LIKE '$$vendorName#$$'> at the end of line 41
WHERE (vendorProject LIKE '$$vendorName$$' OR vendorProject LIKE '$$vendorName1$$' OR vendorProject LIKE '$$vendorName2$$')
ORDER BY vendorProject ASC