Can I combine both the Queries? get the severity level as critical,high and low on based on the CVSS Score
WITH
solution_data as (
SELECT vulnerability_id,
array_to_string(array_agg(distinct ds.summary), E’\n\n’) AS summary,
array_to_string(array_agg(distinct htmlToText(ds.fix)), E’\n\n’) AS fix,
array_to_string(array_agg(distinct ds.applies_to), E’\n\n’) AS applies_to,
array_to_string(array_agg(distinct ds.estimate), E’\n\n’) AS estimate,
array_to_string(array_agg(distinct ds.solution_type), E’\n\n’) AS solution_types,
array_to_string(array_agg(distinct ds.url), E’\n\n’) AS url,
array_to_string(array_agg(distinct htmlToText(ds.additional_data)), E’\n\n’) AS additional_data
FROM dim_vulnerability_solution
JOIN dim_solution_highest_supercedence dshs USING (solution_id)
JOIN dim_solution ds ON ds.solution_id = dshs.superceding_solution_id
GROUP BY vulnerability_id
),
custom_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘CUSTOM’
GROUP BY asset_id
),
location_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS location_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘LOCATION’
GROUP BY asset_id
),
owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘OWNER’
GROUP BY asset_id
),
asset_facts AS (
SELECT asset_id, riskscore, exploits, malware_kits
FROM fact_asset
),
vulnerability_metadata AS (
SELECT *
FROM dim_vulnerability dv
),
vuln_cves_ids AS (
SELECT vulnerability_id, array_to_string(array_agg(reference), ‘,’) AS cves
FROM dim_vulnerability_reference
WHERE source = ‘CVE’
GROUP BY vulnerability_id
),
vuln_reference_ids AS (
SELECT vulnerability_id, array_to_string(array_agg(reference), ‘,’) AS ids
FROM dim_vulnerability_reference
WHERE source <> ‘CVE’ AND source <> ‘URL’
GROUP BY vulnerability_id
),
vuln_tags AS (
SELECT vulnerability_id, array_to_string(array_agg(category_name ORDER BY category_name ASC), ‘,’) AS tags
FROM dim_vulnerability_category
GROUP BY vulnerability_id
),
vuln_minimum_skill AS (
SELECT vulnerability_id, skill_level AS min_skill
FROM (
SELECT vulnerability_id,
row_number() OVER (
PARTITION BY vulnerability_id
ORDER BY CASE
WHEN skill_level = ‘Expert’ THEN 1
WHEN skill_level = ‘Intermediate’ THEN 2
WHEN skill_level = ‘Novice’ THEN 3
ELSE 4
END DESC
) AS row, skill_level
FROM dim_vulnerability_exploit dve
) v
WHERE row = 1
)
SELECT DISTINCT
ds.name AS “Site Name”,
da.host_name As “Host Name”,
da.ip_address AS “Asset IP Address”,
vm.title AS “Vulnerability Title”,
vm.severity AS “Severity Level”,
vm.severity_score AS “Severity Score”,
dos.description AS “Asset OS Description”,
max(daos.certainty) AS “Certainty”,
dsvc.name AS “Service Name”,
favi.port AS “Service Port”,
dp.name AS “Service Protocol”,
fava.first_discovered AS “First Discovered”,
fava.most_recently_discovered AS “Most Recently Discovered”,
ROUND(fava.age_in_days,0) AS “Aging”,
cves.cves AS “Vulnerability CVE IDs”,
proofAsText(vm.description) AS “Vulnerability Description”,
proofAsText(favi.proof) AS “Vulnerability Proof”,
sd.summary AS “Solution Summary”,
sd.fix AS “Solution Fix”,
ct.custom_tags AS “Custom Tags”,
sd.solution_types AS “Solution Types”
– Vulnerable Since
FROM fact_asset_vulnerability_instance favi
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN asset_facts af USING (asset_id)
JOIN dim_service dsvc USING (service_id)
JOIN dim_protocol dp USING (protocol_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
JOIN vulnerability_metadata vm USING (vulnerability_id)
JOIN dim_vulnerability_status dvs USING (status_id)
JOIN fact_asset_vulnerability_age fava USING (asset_id, vulnerability_id)
LEFT OUTER JOIN vuln_cves_ids cves USING (vulnerability_id)
LEFT OUTER JOIN vuln_reference_ids refids USING (vulnerability_id)
LEFT OUTER JOIN vuln_tags vtags USING (vulnerability_id)
LEFT OUTER JOIN vuln_minimum_skill USING (vulnerability_id)
LEFT OUTER JOIN custom_tags ct USING (asset_id)
LEFT OUTER JOIN location_tags lt USING (asset_id)
LEFT OUTER JOIN owner_tags ot USING (asset_id)
LEFT OUTER JOIN dim_asset_operating_system daos USING (asset_id)
LEFT OUTER JOIN solution_data sd USING(vulnerability_id)
GROUP BY
ds.name,
da.host_name,
da.ip_address,
vm.title,
vm.severity,
vm.severity_score,
dos.description,
dsvc.name,
favi.port,
dp.name,
fava.first_discovered,
fava.most_recently_discovered,
fava.age_in_days,
cves.cves,
vm.description,
favi.proof,
sd.summary,
sd.fix,
ct.custom_tags,a
sd.solution_types
====================================================================================SELECT
2
3
da.ip_address AS “IP”,
4
5
split_part(da.host_name, ‘,’, 1) AS “Name 1”,
6
7
split_part(da.host_name, ‘,’, 2) AS “Name 2”,
8
9
split_part(da.host_name, ‘,’, 3) AS “Name 3”,
10
11
dos. NAME AS “OS”,
12
13
dv.title AS “Title”,
14
15
CASE
16
17
WHEN dv.cvss_score = 10 THEN
18
19
‘Critical’
20
21
WHEN dv.cvss_score BETWEEN 7
22
23
AND 9.9 THEN
24
25
‘High’
26
27
WHEN dv.cvss_score BETWEEN 6.9
28
29
AND 4 THEN
30
31
‘Medium’
32
33
WHEN dv.cvss_score BETWEEN 3.9
34
35
AND 1 THEN
36
37
‘Low’
38
39
WHEN dv.cvss_score = 0 THEN
40
41
‘Informational’
42
43
END AS “Severity”,
44
45
dv.description AS “Description”,
46
47
ds.estimate AS “Time to Fix”,
48
49
ds.fix AS “Solution”,
50
51
dvr.reference AS “Reference”,
52
53
fasvi.proof AS “Proof”,
54
55
fava.age_in_days AS “Age In Days”
56
57
FROM
58
59
fact_asset_vulnerability_instance AS fasvi
60
61
JOIN fact_asset_vulnerability_age AS fava ON fasvi.vulnerability_id = fava.vulnerability_id
62
63
JOIN dim_vulnerability AS dv ON fasvi.vulnerability_id = dv.vulnerability_id
64
65
JOIN dim_asset AS da ON fasvi.asset_id = da.asset_id
66
67
JOIN dim_vulnerability_reference AS dvr ON dv.vulnerability_id = dvr.vulnerability_id
68
69
JOIN dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id
70
71
JOIN dim_asset_vulnerability_solution AS davs ON davs.asset_id = da.asset_id
72
73
JOIN dim_solution AS ds ON ds.solution_id = davs.solution_id