Unable to generate the report through api call using custom queries it shows syntax issue, if we use same query on the web gui it work with out any erroes
can you please help in getting the sql query for JSON format or any way we can cut shot the query with the deiredresults
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,
sd.solution_types