I’d like to create a report with hostname, ip address & location (tag) information, kindly help with SQL query
thanks much @john_hartman
I missed to ask one more thing,
I’d like to create a report for specific CVE with hostname, ip address & location (tag) details in csv format, kindly help with SQL query
Thanks for your support
So just like the query above, for any example queries the best place to start is the Github repo we have where that above query is in.
That repo has ~100 or so queries that may already have what you’re looking for with some minor edits that you might need to make to specifically show what it is you’re looking for.
Hi @john_hartman ,
I’ve gone through these articles but unabe to create a query for my requirement.
I need to run a query for specific CVE and generate a csv report to show hostnam, ip address, title solution,fix & location tag
SELECT da.ip_address, da.host_name, dv.title, ds.summary, htmlToText(ds.fix), dt.tag_name FROM fact_asset_vulnerability_instance favi JOIN dim_asset da ON favi.asset_id=da.asset_id JOIN dim_vulnerability dv ON favi.vulnerability_id=dv.vulnerability_id JOIN dim_solution ds ON dv.nexpose_id=ds.nexpose_id JOIN dim_tag_asset dta ON da.asset_id=dta.asset_id JOIN dim_tag dt ON dta.tag_id=dt.tag_id WHERE dt.tag_type = 'LOCATION' AND dv.nexpose_id LIKE '%Your CVE here' ORDER BY da.ip_address DESC
Hi @john_hartman , its not giving any results though we’ve multiple assets impacted with CVE-2023-23397. I modified,
dv.nexpose_id LIKE ‘%CVE-2023-23397%’
The only thing that could stop it would be if your tags are not actually created as LOCATION tags and were instead created as CUSTOM tags