with vulners as
(SELECT *, ROW_NUMBER() OVER(PARTITION BY upt_asset_id, cve_list, package_name, package_version, os, os_version ORDER BY IF(upt_type = 'host', 0, 1), TRY_CAST(cvss_score AS real) DESC, indicator_hash) AS rn FROM upt_vulnerabilities_state WHERE upt_type IN ('host', 'volume') AND cve_list NOT IN (SELECT cve_id FROM upt_vulnerability_exceptions) AND description != ''),
vulnerabilities AS
( SELECT * FROM vulners WHERE rn = 1 ),
filtered AS
( SELECT cve_list, os, IF(os_version IS NULL OR os_version = '', 'any', os_version) AS os_version, package_name, package_version, package_type, description, upt_asset_id,upt_hostname, upt_first_seen, upt_last_seen, TRY_CAST(split(published, 'T')[1] AS DATE) AS published, TRY_CAST(cvss_score AS real) AS cvss_score, TRY_CAST(uptycs_score AS real) AS uptycs_score, IF(indicator_operator IN ('lt', 'rng_e') AND indicator_version IS NOT NULL AND indicator_version != '', 'lt', NULL) AS indicator_operator,
CASE
WHEN indicator_operator = 'lt' AND indicator_version IS NOT NULL AND indicator_version != '' THEN indicator_version
WHEN indicator_operator = 'rng_e' AND indicator_version IS NOT NULL AND indicator_version != '' THEN SPLIT_PART(indicator_version, ',', 2)
ELSE '' END AS indicator_version FROM vulnerabilities ),
summary AS
( SELECT cve_list, os, os_version, package_name, package_version, MAX(indicator_operator) AS indicator_operator,
ARRAY_SORT(ARRAY_AGG(indicator_version),(x, y) ->
CASE
WHEN x IS NULL THEN 1
WHEN y IS NULL THEN -1
WHEN x = y THEN 0
WHEN vercmp(y, x) = 1 THEN 1
ELSE -1 END)[1] AS indicator_version, MAX(package_type) AS package_type, MAX(description) AS description, COUNT(DISTINCT upt_asset_id) AS assets, upt_hostname, DATE(from_unixtime(MIN(upt_first_seen))) AS first_seen, DATE(from_unixtime(MAX(upt_last_seen))) AS last_seen, MIN(published) AS published_at, MAX(cvss_score) AS max_cvss_score, MAX(uptycs_score) AS max_uptycs_score FROM filtered GROUP BY 1, 2, 3, 4, 5,upt_hostname ),
temp AS
( SELECT *, IF(indicator_operator IN ('lt', 'rng_e'), true, false) AS fix_available,
CASE
WHEN max_cvss_score < 4 THEN 'low'
WHEN max_cvss_score >= 4 AND max_cvss_score < 7 THEN 'medium'
WHEN max_cvss_score >= 7 AND max_cvss_score < 9 THEN 'high'
WHEN max_cvss_score >= 9 THEN 'critical'
ELSE 'unknown' END severity FROM summary ),
vln AS
( SELECT max_cvss_score AS cvssScore, cve_list AS cveId, IF(e.description IS NOT NULL, TRUE, FALSE) AS exploitAvailable, os, os_version AS osVersion, package_name AS packageName, package_version AS packageVersion, IF((indicator_operator = 'lt' OR indicator_operator='rng_e') AND indicator_version IS NOT NULL, true, false) AS fixAvailable, v.description AS description, indicator_operator, indicator_version, upt_hostname,
CASE
WHEN indicator_operator = 'lt' AND indicator_version IS NOT NULL THEN indicator_version
WHEN indicator_operator = 'rng_e' AND indicator_version IS NOT NULL THEN split_part(indicator_version, ',', 2)
ELSE ''
END AS fixVersion, IF(e.sources like '%Zero%', TRUE, FALSE) as zeroday_exploitable, published_at AS publishedAt, max_uptycs_score AS uptycsScore, last_seen AS lastSeen, first_seen AS firstSeen, assets AS affectedAssets, severity, cast(cast(ep.epss_score as decimal(10,4)) AS varchar) AS epss_score, cast(cast(ep.epss_percentile as decimal(10,4)) AS varchar) As epss_percentile FROM temp v LEFT JOIN upt_threat_exploits e ON v.cve_list = e.cve_id AND e.sources != 'NVD' LEFT JOIN upt_epss_score ep ON v.cve_list = ep.cve_id)
select * from vln where fixAvailable=true