Please help and Let me know if this is possible. In addition to the request , I trying to get the result out of the below two queries , the Queries seems to be valid but doesn’t output any Data.
Any help is appreciated below is the query:
1)
WITH remediations AS (
SELECT DISTINCT fr.solution_id AS ultimate_soln_id, assets as assets_affected, solution_type, vulnerabilities, url, summary, fix, assets, dshs.solution_id AS solution_id
FROM fact_remediation(20000,'riskscore DESC') fr
JOIN dim_solution ds USING (solution_id)
JOIN dim_solution_highest_supercedence dshs ON (fr.solution_id = dshs.superceding_solution_id AND ds.solution_id = dshs.superceding_solution_id)
),
owner_map as (
SELECT
dta.asset_id,
array_to_string(array_agg(dt.tag_name), ', ') AS owner_name
FROM dim_tag_asset dta
JOIN dim_tag dt ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'OWNER'
GROUP BY dta.asset_id
ORDER BY dta.asset_id ASC
),
app_map as (
SELECT
dta.asset_id,
dt.tag_name as app_name
FROM dim_tag_asset dta
JOIN dim_tag dt ON dt.tag_id=dta.tag_id
WHERE dt.tag_name like 'app_%'
),
assets AS (
SELECT DISTINCT asset_id, host_name, ip_address, name as os
FROM dim_asset
JOIN dim_operating_system USING (operating_system_id)
GROUP BY asset_id, host_name, ip_address, name
)
SELECT DISTINCT
app_name as "System (Application) Name",
host_name as "Hostname",
summary as "Remediation",
vulnerabilities AS "Vulnerabilities Affected (Count)",
owner_name as "Owner"
FROM remediations r
JOIN dim_asset_vulnerability_solution dvs USING (solution_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN assets USING (asset_id)
JOIN owner_map USING (asset_id)
JOIN app_map USING(asset_id)
WHERE cvss_score >= 7
GROUP BY app_name, host_name, summary, vulnerabilities, owner_name
WITH dim_asset_custom AS
(
SELECT asset_id,
Min(scan_id) AS scan_id
FROM dim_asset_scan das
WHERE (
CURRENT_DATE - scan_finished) <= interval '90 days'
GROUP BY asset_id
ORDER BY asset_id), baseline_table AS
(
SELECT fasv.asset_id,
dta.tag_id,
replace(dt.tag_name, 'app_', '') AS tagname,
fasv.vulnerability_id,
baselinecomparison (fasv.scan_id, current_scan) AS baseline,
s.baseline_scan,
s.current_scan
FROM fact_asset_scan_vulnerability_finding fasv
JOIN
(
SELECT asset_id,
da.scan_id AS baseline_scan,
lastscan (asset_id) AS current_scan
FROM dim_asset_custom da) s
ON s.asset_id = fasv.asset_id
AND (
fasv.scan_id = s.baseline_scan
OR fasv.scan_id = s.current_scan)
JOIN dim_tag_asset dta
ON fasv.asset_id = dta.asset_id
JOIN dim_tag dt
ON dta.tag_id = dt.tag_id
JOIN dim_vulnerability dv
ON fasv.vulnerability_id = dv.vulnerability_id
WHERE dt.tag_name LIKE 'app%'
AND dv.cvss_score >= 7
GROUP BY fasv.asset_id,
dta.tag_id,
dt.tag_name,
fasv.vulnerability_id,
s.baseline_scan,
s.current_scan ), assets_vulns AS
(
SELECT av.asset_id,
av.tag_id,
av.tagname,
av.vulnerability_id,
av.baseline,
av.baseline_scan,
av.current_scan,
fava.first_discovered AS date
FROM baseline_table AS av
JOIN fact_asset_vulnerability_age fava
ON av.asset_id = fava.asset_id
AND av.vulnerability_id = fava.vulnerability_id
GROUP BY av.asset_id,
av.tag_id,
av.tagname,
av.vulnerability_id,
av.baseline,
av.baseline_scan,
av.current_scan,
fava.first_discovered ), assets_vulns_old AS
(
SELECT av.asset_id,
av.tag_id,
av.tagname,
av.vulnerability_id,
av.baseline,
av.baseline_scan,
av.current_scan
FROM baseline_table AS av
GROUP BY av.asset_id,
av.tag_id,
av.tagname,
av.vulnerability_id,
av.baseline,
av.baseline_scan,
av.current_scan ), remediated_vulns AS
(
SELECT av.asset_id,
count (av.vulnerability_id) AS remediated_vulns
FROM assets_vulns_old AS av
WHERE av.baseline LIKE 'Old'
GROUP BY av.asset_id ), existing_vulns AS
(
SELECT av.asset_id,
count(av.vulnerability_id) AS existing_vulns
FROM assets_vulns av
WHERE now() - av.date >= interval '90 DAYS'
AND av.baseline NOT LIKE 'Old'
GROUP BY av.asset_id ), new_vulns AS
(
SELECT av.asset_id,
count(av.vulnerability_id) AS new_vulns
FROM assets_vulns av
WHERE now() - av.date < interval '90 DAYS'
AND av.baseline NOT LIKE 'Old'
GROUP BY av.asset_id ), baseline_scan_date AS
(
SELECT av.asset_id,
finished
FROM assets_vulns av
LEFT JOIN dim_scan ds
ON ds.scan_id = av.baseline_scan
GROUP BY av.asset_id,
finished ), current_scan_date AS
(
SELECT pass_fail,
finished,
asset_id
FROM (
SELECT av.asset_id,
finished,
CASE
WHEN fa.aggregated_credential_status_id > 2
AND fa.aggregated_credential_status_id != '-1' THEN 'Pass'
ELSE 'Fail'
END AS pass_fail
FROM assets_vulns av
LEFT JOIN dim_scan ds
ON ds.scan_id = av.current_scan
JOIN fact_asset fa
ON fa.last_scan_id=ds.scan_id
JOIN dim_aggregated_credential_status dacs
ON dacs.aggregated_credential_status_id=fa.aggregated_credential_status_id
GROUP BY av.asset_id,
finished,
fa.aggregated_credential_status_id,
pass_fail ) AS temp
GROUP BY asset_id,
finished,
pass_fail), owner_map AS
(
SELECT dta.asset_id,
array_to_string(array_agg(dt.tag_name), ', ') AS owner_name
FROM dim_tag_asset dta
JOIN dim_tag dt
ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'OWNER'
GROUP BY dta.asset_id
ORDER BY dta.asset_id ASC ), app_map AS
(
SELECT dta.asset_id,
dt.tag_name AS app_name
FROM dim_tag_asset dta
JOIN dim_tag dt
ON dt.tag_id=dta.tag_id
WHERE dt.tag_name LIKE 'app_%' ), pre AS
(
SELECT am.app_name AS "System",
COALESCE (da.host_name, 'N/A') AS "Hostname",
(COALESCE (ev.existing_vulns, 0) + COALESCE (nv.new_vulns, 0)) AS "Current Vulnerabilities",
COALESCE (ev.existing_vulns, 0) AS "Existing Vulnerabilities Count",
COALESCE (nv.new_vulns, 0) AS "New Vulnerabilities Count",
COALESCE (rv.remediated_vulns, 0) AS "Remediated Vulnerabilities Count",
COALESCE (csd.pass_fail, 'Fail') AS "Pass/Fail",
om.owner_name AS "Owner"
FROM existing_vulns AS ev
FULL JOIN remediated_vulns AS rv
ON ev.asset_id = rv.asset_id
FULL JOIN new_vulns AS nv
ON ev.asset_id = nv.asset_id
JOIN dim_asset da
ON da.asset_id = ev.asset_id
JOIN app_map am
ON am.asset_id=da.asset_id
JOIN owner_map om
ON om.asset_id=da.asset_id
JOIN dim_operating_system dos
ON da.operating_system_id=dos.operating_system_id
JOIN current_scan_date csd
ON da.asset_id=csd.asset_id
GROUP BY am.app_name,
da.host_name,
ev.existing_vulns,
rv.remediated_vulns,
nv.new_vulns,
om.owner_name,
csd.pass_fail )
SELECT *
FROM pre