Hello All,
I am not sure if this Query already exists out there but was hoping it does. The below headings I am able to create a report template for but it does not give us authentication status per host. So I decided to create a custom Query to add authentication status but it does not print out per host vulnerability. Either creating adding to an existing query that exists with authentication status or helping me work through my one created would be great. Thank you in advance.
Asset ID
Asset IP Address
Asset Names
Asset Location - Site
Asset Owner
Custom Tag
Asset OS Name
Service Port
Service Name
Vulnerability ID
Vulnerability Title
Vulnerability Description
Vulnerability Proof
Vulnerability Age
Vulnerability CVSSv3 Score changed to CVSS Score
Vulnerability CVSS Score
Vulnerability Solution
Exploit Minimum Skill
Vulnerable Since - date_added
Vulnerability Published Date
Asset Risk Score - doesn’t exist
Vulnerability Risk Score
Query
WITH owner_tags AS ( SELECT asset_id, array_to_string(array_agg(distinct tag_name),’|’) AS owner_tags
FROM dim_tag JOIN dim_tag_asset USING (tag_id) WHERE tag_type = ‘OWNER’ GROUP BY asset_id )
SELECT DISTINCT ds.name AS site_name, daui.asset_id, da.ip_address, das.port, davs.vulnerability_id, dve.skill_level, dv.riskscore, dv.date_published, dv.description, dv.title, dv.date_added, dsl.fix, dv.severity_score, dv.cvss_score, da.host_name, da.mac_address, dos.description AS operating_system, dht.description, dos.asset_type, da.last_assessed_for_vulnerabilities, aggregated_credential_status_description, das.port, ot.owner_tags FROM dim_asset da JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_host_type dht USING (host_type_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
JOIN fact_asset_scan_service fass using (asset_id)
JOIN dim_credential_status dcs using (credential_status_id)
JOIN dim_credential_status USING(credential_status_id)
JOIN fact_asset fa USING (asset_id)
JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)
JOIN dim_asset_service das USING (asset_id)
JOIN dim_mobile_asset_attribute dmat USING (asset_id)
JOIN dim_asset_unique_id daui USING (asset_id)
JOIN dim_asset_vulnerability_solution davs USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_solution dsl USING (solution_id)
JOIN dim_vulnerability_exploit dve USING (vulnerability_id)
LEFT JOIN owner_tags AS ot ON (ot.asset_id = fa.asset_id)
ORDER BY site_name, da.ip_address