Knowledge Base

Ask A Question

Questions

1

SQL for vulnerability report against CVE

I need help with the following query, check vulnerabilities against Robot. I am also pulling asset group and Fix. The query runs but results doesn't match my dynamic asset group - the number of asset on vulnerabilities from query doesn't match against the number of asset in the dynamic asset group for Robot. Below is the query: SELECT dsi.name AS site, dag.name AS asset_group, da.ip_address, da.host_name, dv.title, dv.description, favi.port, dos.description AS operating_system, favi.key, da.last_assessed_for_vulnerabilities AS last_scanned, favi.date AS scan_finished, proofAsText(ds.fix) AS solution, proofAsText(favi.proof) AS proof FROM fact_asset_vulnerability_instance favi JOIN dim_vulnerability_solution dvs USING (vulnerability_id) JOIN dim_vulnerability dv USING (vulnerability_id) JOIN dim_asset da USING (asset_id) JOIN dim_operating_system dos USING (operating_system_id) JOIN dim_solution ds USING (solution_id) JOIN dim_site_asset dsa USING (asset_id) JOIN dim_site dsi USING (site_id) JOIN dim_asset_group_asset daga USING (asset_id) JOIN dim_asset_group dag USING (asset_group_id) WHERE dv.title ILIKE '%2017-6168%' OR dv.title ILIKE '%2017-17382%' OR dv.title ILIKE '%2017-17427%' OR dv.title ILIKE '%2017-17428%' OR dv.title ILIKE '%2017-12373%' OR dv.title ILIKE '%2017-%13098' OR dv.title ILIKE '%2017-%1000385' OR dv.title ILIKE '%2017-%13099' OR dv.title ILIKE '%2017-%17841' OR dv.title ILIKE '%2017-%6883' OR dv.title ILIKE '%2017-%5081' OR dv.description ILIKE '%2017-6168%' OR dv.description ILIKE '%2017-17382%' OR dv.description ILIKE '%2017-17427%' OR dv.description ILIKE '%2017-17428%' OR dv.description ILIKE '%2017-12373%' OR dv.description ILIKE '%2017-%13098%' OR dv.description ILIKE '%2017-%1000385%' OR dv.description ILIKE '%2017-%13099%' OR dv.description ILIKE '%2017-%17841%' OR dv.description ILIKE '%2016-%6883%' OR dv.description ILIKE '%2012-%5081%'

Posted by Kheun Chan about a year ago